VBA - Export Tables

[WANNABE]

Please Help Me... How can I use DoCmd.RunCommand acCmdExport
to export a Predetermined list of tables from one MDB file to a the another MDB
file without prompting the user for a file name or location and to allow
automatic replacement of existing tables???

I have created a button and started the VBA so that the data can be archived on
an as needed basis, but I can't figure out exactly how to pass the parameters to
the command that I am trying to use.
Thank you...

 

[Wayne-I-M]

Hi

You can use import or export - it's almost the same.

have a look at an answer I gave a while ago

http://groups.google.com/group/micr...roup:microsoft.public.access#786781a92a2adf02

And just use (if you want) then use the end section

Note - This 1st part ( DoCmd.DeleteObject acTable ) is just my preference as
I tend to delete tables and then just reimport/export, other people may say
it not worth it. Of course you could just use an update query to add "stuff"
to tables but I have found that the less you put in code the less there is to
go wrong but this is up to you




'If tables exisit - delete them'
Dim db As Database, tdf As TableDef
Set db = CurrentDb()
For Each tdf In db.TableDefs
If tdf.Name = "TableNameA" Then
DoCmd.DeleteObject acTable, "TableNameA"
End If
If tdf.Name = "TableNameB" Then
DoCmd.DeleteObject acTable, "TableNameB"
End If
If tdf.Name = "TableNameC Then
DoCmd.DeleteObject acTable, "TableNameC"
End If

'Reimport tables from some drive'
Next
DoCmd.TransferDatabase acImport, "Microsoft Access", "Path to DB.mdb",
acTable, "TableA", "TableA", structureonly:=False
DoCmd.TransferDatabase acImport, "Microsoft Access", "Path to DB.mdb",
acTable, "TableA", "TableB", structureonly:=False
DoCmd.TransferDatabase acImport, "Microsoft Access", "Path to DB.mdb",
acTable, "TableA", "TableC", structureonly:=False

In fact I don't tend to use stuff like this very often but I "do" use a
table export many times ( to upgrade date on field staff laptops) but with
this I will check to see if the DB exists on the external machine 1st (field
staff "mess about" with their machines) - something like what is shown in the
answer.

Good luck

 

[Wayne-I-M]

ooops - sorry

I mixed up the A's B's and C's

Should be like this

DoCmd.TransferDatabase acImport, "Microsoft Access", "Path to DB.mdb",
acTable, "TableB", "TableB", structureonly:=False
DoCmd.TransferDatabase acImport, "Microsoft Access", "Path to DB.mdb",
acTable, "TableC", "TableC", structureonly:=False

Basically the table names should be the same - sorry

 

[WANNABE]

Thanks Wayne that looks great, can you help me take it to the net level?
How can I use the out put of a query which runs against MsysObjects and lists
the tables that I want to export to the archive DB. I thought some kind of For
loop, but have no idea what the commands would be. Thank you for any help you
can provide.
=====================================================
ooops - sorry

I mixed up the A's B's and C's

Should be like this

DoCmd.TransferDatabase acImport, "Microsoft Access", "Path to DB.mdb",
acTable, "TableB", "TableB", structureonly:=False
DoCmd.TransferDatabase acImport, "Microsoft Access", "Path to DB.mdb",
acTable, "TableC", "TableC", structureonly:=False

Basically the table names should be the same - sorry