Solved: Newcomer Excel/Acces problem. Please share your thougths.

[hunsnowboard]

Hi Everyone! I am very new to Ms Access forum as I just started learning Access. I have many postings on this site, and found many solutions here for my Excel macros which helped me a lot. :clap: I am very happy that this site exists, and plan to take the VBA training and certificate advertised by this site, this summer.

Now I have a more complex problem, actually first I would like to know your oppinions and thoughts on my problem.

I have a quite big excel file with a macro. Big excel file means more than 160.000 records (3 sheets) and this number is growing. So the macro looks for some data and manipulates them on each sheet and then comes with the results on the fourth sheet. My problem is the following: in the future many people will use this file and will search the macro (maybe in the same time) from different places. This probably will slow down very much the use of excel. And than I thought what if I would put all these data in an Access file. The problem here is that not every user will have Access on their local computer, furthermore very few people can use Access and they are reluctant to learn it. The excel file now is located on a shared drive (the situation would be the same with the access file). So here is what I thought (please share your toughts with me if it is possible):

1. User opens an empty excel file on his local computer and makes starts the macro
2. Excel opens the access or access file on the shared drive and starts the access macro which will search for the given code
3. Access transports the output of the search in the opened empty excel file.

Is this possible? If it works..I also have some additional questions like do I have to install Ms Access for this solution? On which machine?
Thank you in advance for your help!

[CreganTur]

If you're going to have multiple concurrent users, then you want to stay far, far away from Excel. The reason for this is that Excel shared workbooks are notorious for issues and errors that occur as the result of multiple people trying to do multiple things to the workbook at the same time.

Access is definately the better option.

Your users do not need to learn Access in order to use it- that's where you, as the developer, come in. It's the developer's job to create forms that are easy for Users to use. You should simplify your process as much as you can for your users.

What you should consider is creating 2 databases- one as the backend database that holds the tables and records that your application will need. Put this in a place where all of your users can access it, like a shared drive. Then have a front-end database that holds all of the forms, queries, and objects needed to make the UI run. You can save this front-end DB as a an MDE file- this is a special version of Access that can be run on computers that do not have Microsoft Access installed.

I know this is a lot of information to try to assimilate, but the most important thing for you to do is carefully plan out your table structures. You'll learn a lot by working on this project.

[hunsnowboard]

Hi CreganTur! Thank you so much for your advice. You are definetelly right about this, and once again I learned something very important. I'll do some research on what you have written, and probably will ask some more questions. If it is not a problem, I'll not close this thread for now, as I might ask other things regarding to this issue, hoping that you or someone will help in my or the project's further development! Thank you again!

[CreganTur]

If it is not a problem, I'll not close this thread for now, as I might ask other things regarding to this issue, hoping that you or someone will help in my or the project's further development!

You're very welcome; glad to provide some help.

Since you're new to Access, you may be interested in a couple of books that I have found helpful. They are:
Access 2003 Step By Step (http://www.amazon.com/Microsoft%C2%AE-Office-Access-2003-Microsoft/dp/0735615179/ref=pd_bbs_sr_1?ie=UTF8&s=books&qid=1233762957&sr=8-1), which is a great book that covers working with the front end of access, and Access 2003 Programming By Example (http://www.amazon.com/Access-2003-Programming-Example-VBA/dp/1556222238/ref=sr_1_3?ie=UTF8&s=books&qid=1233763008&sr=1-3), which takes you from basic VBA techniques, through advanced coding for recordsets, classes, and many other important topics.

HTH:thumb

[hunsnowboard]

Hi CreganTur! Thank you for the book recommendations. I checked some information about your advice. Please let me know if I understood right.

MS Access has two types. One which is the normal, and the other one which is the MS Access Developer Extension. The Developer Extension has the royalty free runtime version, which can be used by many users. So, if I want to make this macro in Access... first of all I have to convince my boss to buy the MS Access Developer Extension, because only for the Developer Extension is the runtime version for free. We have the normal MS Access now, but this seems to be not enough...as far as I understood! Please let me know if I am right! Thank you very much in advance! (I'll also check out the books you mentioned) Thank you!

[CreganTur]

I don't think you have to buy anything special. Take a look at this (http://www.databasedev.co.uk/microsoft-access-mde.html). It's a good article that shows how to convert a MDB to a MDE.

If there is a cost involved, then explain to your boss that the small cost will allow users who do not have Access on their systems to be able to run the Access DB you create, and that will be a lot cheaper (potentially) than getting new licenses for your users who don't have Access.

I don't do much work with MDE files, so I may be a little of with my information.

[hunsnowboard]

Hi CreganTur! Thank you for the information. Now, here is something that I do not understand.

Link: http://office.microsoft.com/en-us/access/HA011208861033.aspx


The Access runtime software and the royalty-free Access runtime license formerly shipped as part of a Microsoft Office Developer (MOD) product with earlier versions of Microsoft Office. For Access 2003, the Access runtime software is shipped with the Microsoft Office Access 2003 retail product, and the royalty-free Access runtime license is a component of the Access 2003 Developer Extensions. Even though the runtime software is included in the Microsoft Office Access 2003 retail product, the right to distribute the runtime is not included. You must purchase the Access 2003 Developer Extensions to obtain a royalty-free license to package and distribute the runtime.

Runtime version of Access can be copied to any machines for free only in case if I have MS Access Developer Extension. Please approve if I understood it correctly...

[CreganTur]

Runtime version of Access can be copied to any machines for free only in case if I have MS Access Developer Extension. Please approve if I understood it correctly...
Looks that way to me. Call Microsoft for the specifics.

[hansup]

Hi CreganTur! Thank you for the information. Now, here is something that I do not understand.

Runtime version of Access can be copied to any machines for free only in case if I have MS Access Developer Extension. Please approve if I understood it correctly...
The situation has changed with the Access 2007 Runtime. Microsoft says "You do not need to buy any special SKU in order to redistribute the Access 2007 Runtime. You can freely redistribute it or point users to this download."

The forum rules won't let me post the link to Microsoft's download page, but you can find it by Googling "access 2007 runtime"

[hunsnowboard]

Hi Hansup! The problem is that at the company we have MS Access 2003. Actually we have Office Professional. I called Microsoft in my country and they said that if I have Office Professional than it is no problem to have as many runtime versions as I want. However, I forget to ask them the version. Tomorrow I'll call them and ask about the version. Will update this topic soon, but beside this I have many things! Thank you for your comments!

[hunsnowboard]

Hi Everyone! I just thought that I give you an update on my case. I talked with Microsoft and they told me that if my company has Office Professional than I am free to install as many runtime version as I want. And this is true for the 2003 version as well! I'll update this post soon, for help! Thank you!

[hunsnowboard]

Hi Everyone! So here are my first questions related to my project. In my database I would like to have only one userform. I already created this userform. I also created a sidebar which has 4 buttons. And now about my problems:
1) I have a very basic select query and I would like to run it from the form. The problem is that, when I run it the results are displayed in table format. How can I display the results on my main form?
2) How should I make a query which will look for a value (which is inputed) in three different columns, and if found than display as result the column names in which it was found. If in all of the columns can be found, then it should display every column names.

I hope you understood my questions and can help me!
Thank you in advance!

Edit: Refrased question.

[CreganTur]

1) I have a very basic select query and I would like to run it from the form. The problem is that, when I run it the results are displayed in table format. How can I display the results on my main form?


You can show the records as a sub-form. You'll need to create a form that shows your records in datasheet view. Then code your button so it opens the subform. Check Access Help and search the forum for more info on subforms.

[hunsnowboard]

Hi There! Thank you for your help and support! I will close now this thread and describe my problems with the access project in new threads. Thank you!