Setting up automated Microsoft Access database update using VBA?

[DRRoyLester]

Hello I am very new to Microsoft Access and VBA coding, I have seen many threads that partly answer my question but the answers are too complex for me to adapt for my purpose. Please forgive my ignorance as i don't know if VBA is the best solution or if what i am asking is even possible. I will try to outline the situation, my high level intent followed by how i propose to achieve this.

Scenario: I have <20 number of systems/equipment all producing csv. files regularly. The .csv file has a max of 50 columns and 5000 rows but both column and row number varies depending on the file. The column headers are always written in the same way but the number of columns vary between equipment but the first three columns are always the same. The csv. files are stored on a shared server.

High level intent: I would like to automatically send the data within these .csv files to a single access database as either separate tables (named as the file is named) or into a monthly table depending on when the transfer happens with an added column stating the file name for grouping purposes. I also need to make sure the format of each column is correct e.g. date/time, text and number, as i wish to graph this data after importing.

Proposal, as mentioned i don't know if this is the best way to achieve my high level intent: Would it be possible to move (using a digital transfer agent) the csv. file into a template that contains a VBA code to send the data to a specified Access database with the correct formatting?

Thank you in advance for your help.

Roy

 

[GPGeorge]

Welcome to AWF.

"... the number of columns vary between equipment but the first three columns are always the same."

Does each piece of equipment produce consistent formats in its csv files? Or can the same piece of equipment produce inconsistent formats for each csv that it creates?

If the former is true, you can create import specs for each piece of equipment and handle them consistently. If the latter is true, you can still accomplish the end goal, but with more intensive manipulation in VBA to get to the end goal.

 

[DRRoyLester]

Welcome to AWF.

"... the number of columns vary between equipment but the first three columns are always the same."

Does each piece of equipment produce consistent formats in its csv files? Or can the same piece of equipment produce inconsistent formats for each csv that it creates?

If the former is true, you can create import specs for each piece of equipment and handle them consistently. If the latter is true, you can still accomplish the end goal, but with more intensive manipulation in VBA to get to the end goal.

Very good point, the first three columns are the only ones that will not be number format.

Could you explain how I go about import specs, and how to make this automated into Access

 

[theDBguy]

Hi. Welcome to AWF!

I agree, if you can create import specs for the common columns, you should be able to consolidate all the data into the same table.

 

[DRRoyLester]

Hi. Welcome to AWF!

I agree, if you can create import specs for the common columns, you should be able to consolidate all the data into the same table.

The format question is a side part of this the automatic transfer is the larger piece I can’t get my head round

 

[theDBguy]

The format question is a side part of this the automatic transfer is the larger piece I can’t get my head round

Just to make sure we're on the same page, nothing in Access can really be "automatic," because it's an event driven system. So, when you say automatic, I interpret that as the user clicking a button to start the process.

If that's the case, then importing the csv files automatically shouldn't be a problem.

 

[The_Doc_Man]

The first three columns will not be number format, but you want to do graphing, which is USUALLY associated with numeric data. There seems to be a built-in level of inconsistency. If you have inconsistent numbers of fields coming in, a big question will be how to extract data from the 4th and later CSV columns. "Automation" USUALLY implies consistency or regularity. When there is irregularity of data, automation becomes more complex in that you have to first decide that file X is in a particular format. Is there a way to know or predict ahead of time AND WITH REGULARITY which format a particular machine will exhibit?

By no means is this impossible, but you did express the idea that some things you have seen on this forum are too complex for you. That is going to limit or at least affect the solutions we can offer.

 

[DRRoyLester]

Just to make sure we're on the same page, nothing in Access can really be "automatic," because it's an event driven system. So, when you say automatic, I interpret that as the user clicking a button to start the process.

If that's the case, then importing the csv files automatically shouldn't be a problem.

This maybe the key answer as I would like something truly automated (no clicking) but just to check, if as said, I have put the VBA in a template for sending to access and I initiate the VBA from excel would this not be automated, or does the initiation of the VBA class as a click? Sorry if that isn’t very clear

 

[theDBguy]

This maybe the key answer as I would like something truly automated (no clicking) but just to check, if as said, I have put the VBA in a template for sending to access and I initiate the VBA from excel would this not be automated, or does the initiation of the VBA class as a click? Sorry if that isn’t very clear

To me, an automatic system would be like if the csv files were stored in SharePoint for example. You can then use Power Automate to grab the data and put in Dataverse, for instance.

If you use Excel, then it still requires a user initiating the import. If you require no user interaction at all, then you'll need a constantly running system 24/7. For example, if you leave a database open, you can use a timer to initiate an action at a regular interval.

 

[DRRoyLester]

The first three columns will not be number format, but you want to do graphing, which is USUALLY associated with numeric data. There seems to be a built-in level of inconsistency. If you have inconsistent numbers of fields coming in, a big question will be how to extract data from the 4th and later CSV columns. "Automation" USUALLY implies consistency or regularity. When there is irregularity of data, automation becomes more complex in that you have to first decide that file X is in a particular format. Is there a way to know or predict ahead of time AND WITH REGULARITY which format a particular machine will exhibit?

By no means is this impossible, but you did express the idea that some things you have seen on this forum are too complex for you. That is going to limit or at least affect the solutions we can offer.

That’s fair, to be clear, the first column is always a date and time, the second is a ways time (00:00:00) the third is text, the remaining columns are always number. Graphing is always done on a time axis so using the second column as X and the other data passed column 3. Also to be h clear the graphing and report does not need to be automated as I hope to use Power Bi to do the analysis.

i have already seen how easy it is to import a csv. file into Access i just want to achieve this automatically by using a VBA code in an excel template

 

[DRRoyLester]

To me, an automatic system would be like if the csv files were stored in SharePoint for example. You can then use Power Automate to grab the data and put in Dataverse, for instance.

If you use Excel, then it still requires a user initiating the import. If you require no user interaction at all, then you'll need a constantly running system 24/7. For example, if you leave a database open, you can use a timer to initiate an action at a regular interval.

This sounds promising, what’s a dataverse? Or a timber?

 

[The_Doc_Man]

In the simplest, bare-bones method that stays within Access, you would have a database that you left open for a while. A LONG while.

In it, you would have a form that either stays "up" because nobody will use it, or your minimize it and hide it so nobody sees it. Then the form has a "timer" that triggers every few minutes. (More often than, say, every 5 minutes, and you "swamp" the machine.)

Using the functions in Access or in the File System Object that comes from the Windows Scripting library, you scan for new candidates for import. For each candidate, you would do the CSV importation and data distribution, then RENAME or MOVE that file so that it would not be handled a second time.

Then before you exit the Form_Timer routine, look again for candidates. When you finally run out of candidates for import, be sure that you have an appropriate timer set up for the Form_Timer routine to repeat that cycle.

This doesn't address the importation of the CSV because that is a separate discussion still ongoing in this thread, but the above is a simple-minded overview of automation. Note that this approach can run into issues of shutting down the (essentially infinite) loop because you need ANOTHER form that can tell your timer to stop unless you leave the form visible on a dedicated machine.

 

[plog]

When/how are new files obtained?

Does some other system dump the .csv files into a directory every Tuesday at 7:53 pm? So that they are always there at 7:54 ready to be processed?

Are you randomly emailed these .csv files by people who need prompting so they might all be there between Tuesday morning and Thursday night?

Explain how/when you know you have new files to be processed

 

[Pat Hartman]

1. Figure out how many different formats you have?
2. Why are the formats different?
3. Must they remain different?
4. Do you want only some of the columns from each file?
5. Are the columns you need the same from each file?
6. Are the columns in the same place in every file?

The answers to these questions should help you decide
1. How many import specs you need.
2. How many tables you need.

To automate the process, you can as others have said "press a button". Or, you can have a procedure that runs when the db opens. This procedure can check your log files to determine when the last import was performed and whether or not it needs to be done now. The import procedure then loops through the files and imports each one. If you have multiple import specs, the file name must provide the information you need to determine which import spec to use to link/append the data from this file.

Someone also suggested a timer event. I would avoid the timer event unless that is what you really need. Will you have multiple concurrent users? If so, you almost certainly don't want to use a timer event although you can manage it by using the log technique I alluded to earlier.

I log all imports to make it easy to back them out if there is an issue. I create the log record with the file name, date and time of the import and a record count. I insert the log record with the name and datetime filled in but the count at 0. Capture the LogID. Then I link to the import file and run an append query that appends all the rows and the selected columns and includes the LogID. Then I run an update query that counts the append rows and matches it to the count of the linked table. If the counts are identical, I update the count in the log record. If they are different, I delete all the imported rows and delete the log record. If the file names are always the same, then you can't easily tell if you are importing the same file multiple times but mine mostly have different names so another check is to compare the name to the log table and reject the selection if you have already imported the file.

You usually want to link to files rather than to import them directly so you can do a little validation on the way in and if you want to use the logging technique which includes the LogID in each appended record.

 

[bastanu]

You can use the built in Windows scheduler to add a timed task to open your Access app (or Excel file) at certain times\intervals and in the opening events (or you can use an AutoExec macro for the Access app) you run your succession of imports.

Cheers,