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

 

[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,