Automatically populate an Access database from a script

Question

I have a script that downloads data from a database into a series of CSV files. After they're downloaded, they must be loaded into an Access database for reporting (I use DoCmd.TransferText, and have a saved text import specification). Every time I run the job that generates the data and downloads into CSV, I usually need to load into a fresh copy of the unpopulated version of the Access database. Is there a way to automate this in a batch script?

In short, I need to be able to:

copy the unpopulated Access file to a new file with the timestamp in the name
load certain CSV files that match a pattern (such as "data_for_reporting_2_20111024_135142.csv") in the directory into the Access file.

Would it be possible to import from the other database directly into Access ... without using CSV files as an intermediate step? If so, this should be simpler. — HansUp
– HansUp, Commented Oct 24, 2011 at 18:25
@HansUp: It is technically possible, but since the CSV reports are already being generated and consumed by other users/tools, I'd rather make use of what I already have, especially since there can be a LOT of data and I don't want to spend time waiting to extract it twice. It would also require either: installing the Oracle drivers on the computers of users who use these reports (lots of administrative overhead), or copying the data from the extracting database to another more static database, which might work but seems like too many steps. I'd like to keep the process as simple as possible. — FrustratedWithFormsDesigner
– FrustratedWithFormsDesigner, Commented Oct 24, 2011 at 18:32

HansUp · Accepted Answer · 2011-10-24 19:45:20Z

2

I think you can use VBScript to do what you need.

copy the unpopulated Access file to a new file with the timestamp in the name

FileSystemObject.CopyFile "c:\somefolder\template.mdb", "c:\dest\new.mdb"

See CopyFile Method.

load certain CSV files that match a pattern (such as "data_for_reporting_2_20111024_135142.csv") in the directory into the Access file.

You can examine the Files Collection of your CSV folder, determine which of those file names match your target pattern, then run DoCmd.TransferText with each matching file name.

You would run DoCmd.TransferText from an Access application instance:

Option Explicit
Dim appAccess
Dim strMdb
Const cstrFolder = "c:\dest\"

strMdb = "new.mdb"

Set appAccess = CreateObject("Access.Application")
appAccess.OpenCurrentDatabase cstrFolder & strMdb, False

So, do the Transfertext from that instance variable:

appAccess.DoCmd.TransferText [your options]

Edit: This would be faster for me to create and test in VBA. So I think I would use that instead of VBScript.

Create a function, SnarfCSV, in a standard module in your template MDB. Then create a macro, mcrSnarfCSV, with the SnarfCSV function as its runcode action. Then after you copy the template MDB to the new MDB, open the new one with the /x command line switch to run the macro.

"Path to MSACCESS.EXE" "Path to your db file" /x mcrSnarfCSV

edited Oct 24, 2011 at 19:45

answered Oct 24, 2011 at 18:49

HansUp

97.3k11 gold badges80 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

FrustratedWithFormsDesigner Over a year ago

Ok, so how do I invoke this all from a script? Ultimately, I'd like to add this as the last line in my script as one last line that executes prepare_access_reports.bat and then just like that I have new Access reports.

HansUp Over a year ago

Oh, crap! When you first said "I have a script", I thought you meant VBScript. Sorry I'm too rusty on .bat

FrustratedWithFormsDesigner Over a year ago

Well, currently everything is running from an SQLPlus script, but it looks like I can invoke VBScript using the host command, ala host "wscript.exe test.vbs", so this might still work... I'll have to learn a bit of VBScript and then give it a shot. Is VBScript usually deployed by default? If I could install new components on the user's machines, I'd have gone with the Oracle client in the first place.

HansUp Over a year ago

Honestly I'm not sure whether it's always available. I didn't need to install anything extra to run VBScript with Windows 7 and 2003. I don't have other Windows versions currently.

HK1 Over a year ago

vbscript should be installed and functioning on basically any windows machine. It's a function of "Windows Script Host". It is possible to block vbscript files from running and some security software may do this. Usually it's done by changing the .vbs file association. In short, you shouldn't normally have to worry about your .vbs files not working. And calling them from the command line (batch file) is also possible.

|

Collectives™ on Stack Overflow

Automatically populate an Access database from a script

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related