How can I script copying an Access table from one mdb to another?

Question

Hi all,
I've inherited a setup that utilizes Access 97 databases. I need to copy a table from one main mdb to multiple others to prep them for use. The source mdb and source table are mostly static, the destination mdbs vary widely.

So, I have:
source.mdb contains table A
destination.mdb contains tables 1, 2, and 3

I need to end up with:
source.mdb is unchanged (contains table A)
destination.mdb contains tables 1, 2, 3, and A

This is a simple enough task within the Access GUI, but because this task needs to happen hundreds of times in a day, I'd like to automate it. The ultimate goal is to have a script or batchfile that I can give each mdb user (about 75 employees) to manipulate each database on their own before use. I'm much more at home in a UNIX/Oracle environment, so scripting this up has thrown me for a loop.

In Oracle, I would use a query like this:

copy from user/password@sourceDB to user/password@destinationDB
create new_tableA using select * from tableA;

A similar question here on stack overflow tells how to copy records from one table to another, but the destination table pre-exists, and particular fields are defined:

strSQL = "INSERT INTO ServiceRecordInvoices " & _
"( sriID, sriServiceRecordID, sriInvoiceDate, sriInvoiceNumber, " & _
"sriDescription, sriInvoiceAmount ) " & _
" IN '" & strDatabasePathandNameTo & "' " & _
"SELECT srpID, srpServiceRecordID, srpInvoiceDate, srpInvoiceNumber, " & _
"srpParts, srpPartsAmount " & _
"FROM ServiceRecordParts IN '" & strDatabasePathandNameFrom & "';"

My first try looks like this and unsurprisingly, doesn't work. Can someone steer me right?

copyTableSql = "CREATE [new_tableA] " & _
" IN '" & destinationDBpath & "' " & _
"SELECT * FROM tableA IN '" & sourceDBpath & "';"

Thanks for any advice.

Answer 1

If you're willing to use VBA within Access to automate this task, it would be easy with a "SELECT ... INTO ..." statement --- what Access calls a Make Table Query.

SELECT s.* INTO tblA IN 'C:\SomeFolder\destination.mdb' FROM tblA AS s;

If you must drive it with scripting outside of Access, try vbscript similar to this:

Option Explicit
Dim dbe
Dim db

Set dbe = CreateObject("DAO.DBEngine.36")
Set db = dbe.OpenDatabase("C:\SomeFolder\source.mdb")

db.Execute "SELECT s.* INTO tblA IN 'C:\SomeFolder\destination.mdb' FROM tblA AS s;"
Set db = Nothing
Set dbe = Nothing

I tested that script with my own database and table names, and it works for me with Access 2007. You may need to reference a different DAO version for Access 97. I don't know which version you need, but would probably start with "DAO.DBEngine.35" for the CreateObject line.

Actually, this task would be simpler if you create a link in destination.mdb which points to tblA in source.mdb. That way you wouldn't have to copy data from one database to the other.

Answer 2

Since it's wise to separate data and logic in Access anyway, provide them with 3 databases - one with connections to the other two. Then you could probably just filecopy everything. Everyone gets a common copy of the mdb with code and a common copy of A, plus their own 1-2-3.

Access is pretty sweet about this sort of thing. You shouldn't lose any efficiency.

Also, any future updates to either of the data mdbs (or the code mdb for that matter) is just another filecopy.

Answer 3

You’re being a little bit vague in terms of wanting to use access coding in visual basic inside of MS access, or if you actually want use a windows script? In your case it seems that windows scripting would be more appropriate. With windows scripting you can launch any windows application be it excel, word, or even MS access and then have that application do anything you want. Your script can then shut down the application. So in windows land, you have what’s called automation, and you write windows scripts to accomplish these goals. However when you launch a MS access often the application will have forms and code that runs at startup. Often you don’t want these things to occur as you have since when you use windows scripting it really as if the user has launched the application.

However it turns out on every windows box, since windows 2000, you can open and read and manipulate access database files without even having to have access installed on the computer. The other advantage of this approach is that you can thus copy data out of these database files, but not actually have to launch the access application itself. This saves memory, startup time, and also as mentioned eliminates the need to actually have MS access startup and be launched in a batch file (keep in mind there is old style batch files, and then there is what we call windows scripting…I suggest you use a windows script here). Thus scripting approach lends it quite well to your needs to copy data out of one file to another.

Windows scripting also works very well if you want to use the windows task scheduler to run some nightly batch processing program. In fact, if you do need some nightly reports from ms-access, then windows scripting would be your choice as opposed to using VBA inside of ms-access. Windows scripting would enable you to launch ms-access, print some reports, and then shut down the application. You can also do this for Excel, or in fact just about any windows application.

So automated solutions in windows are supported, and this is due to most windows applications expose simply expose all their properties and methods as objects and properties that the GUI parts are based on. So, your scripting code can usually accomplish the SAME goals as if a worker was sitting at their PC and selecting options with the mouse that simply uses those methods of the application).

Keep in mind this is not a mouse recording system, but a true scripting language that all windows applications adhere to.

However in your case, as mentioned you don’t need to launch a MS access, and you can use the built in libraries that ships with windows to open and manipulate these database files. The script posted here by HansUp that is in fact a standard plain Jane windows script. Simply paste his example code into a text file, rename the text file with a .vbs file extension, and you good to go. And, that script will even work and run on a windows box even without MS access installed.

Answer 4

If you are running this from within Access, this is a single line of code or a macro using the DoCmd.TransferDatabase command.

Set the options depending on whether you're running it from the target database (importing) or from the source (exporting).