Streamlining the process?

[alcorp]

Hey, I've created an access project and I am trying to streamline the process.

Basically in my project, I import info from a text file, run queries and then I get a simple results table. Now how do I set it up where it is easy to repeat the process so I can do this process with saved settings or options so I won't have to keep on manually setting up the queries and tables each time and so others can do the same?

I know excel has something similar but I can not figure out how you can do it in access unless you're not able. Thanks.

[alansidman]

You could build a form and place command buttons on the form. Each of the command buttons would have VBA code behind them to automate the manual steps you wish to execute. Attached is an example of one that I created many years ago but will give you an idea of how it works. Be sure to open the form in design view and look at the codes behind the buttons.

[alcorp]

Thanks for the file although I am still a little unclear about it. My tables are similar to yours but I want it to where you import the data(tables from excel and text files, if done correctly by user) to automatically query the results. I looked at your code and I'm still lost on how exactly it works. I am pretty sure this is exactly what I need but I never used it so I am a little lost.

[alansidman]

Perhaps if you uploaded sample excel spreadsheets you wish to import to Access and then provide the details of what you want your query to do and what you want to happen with the results, I could help with some specific code that will accomplish what you want. If you are going to want to move down the Access trail, you will need to learn some basic VBA to ease the journey.

[alcorp]

OnlineStreamline.xlsx

here is a sample.

For the first query I display MAPBOOK, PAGE, PARCEL, and EDU.

PARCEL has a criteria of Not like "8**" and EDU had a criteria of Not like"0" and Not Like "Null"

2nd query I use the table from query one. I then set the first column to APN: [MAPBOOK] & [PAGE] & [PARCEL]

which is sort by ascending.

Column two is set to TAXFEE: Sum(23*[EDU])
and total is set to Expression and to two decimal places.

[alansidman]

Based on the above, the following code should work for you. You will need to build a form and place a command button the form. Then put this code behind the command button. Change the name of the command button or the sub name to be the same. This also assumes you are using Windows 7 and Access and Excel version 2007 or later. If you have more than one sheet in your workbook, this will import each sheet as a table. If you are using only one sheet but have two or more empty ones, I suggest you delete the sheets in your workbook or it may cause issues with the code.

Please Login or Register  to view this content.

[bigroo1958]

Hi Alcorp,

Here is my approach to importing text files into access, its pretty simple and I have being using this for a number of years now daily!!