Export table to another database

Question

I need to export a table from an Access 2010 database to an Access 1997 database (don't ask!).  while I can go in and perform the task manually, I would prefer to do it in a macro that I can set to run daily (without any manual intervention).  I cannot link the tables because the '97 database will not accept a .accdb file as a link.  Is there a way to set this up in a macro?  I'm not familiar with writing VBA statements, so I'm shying away from that option.  If you can give me explicit directions on that, then I can attempt.  Or, can I write a statement in the '97 database macro to import the table?

Answer 1

Is your database split? If it is a multi-user database it NEEDS to be split. So what I would do is split the table into a separate mdb file and link to it from both the 2010 and 1997 stations.

Not sure if that will work. So you do need to explain why you are dealing with 1997 and maybe we can offer alternatives.

Answer 2

ciao SherylZA,

"dont'ask....?" ok ;-)

the following code allow you to export the tables from currentdb 2010from another one, customizing

strPathDbReceiver  path string.

Unfortunately....I really don't know if coudl be helpful from moving accdb tables into an older 1997 Access database...maybe you got backward compatibiliy issues with AC20xx database version...

Besides, I have not choise to test it witht 1997...

try...and let us know...!

ciao, Sandro.

Option Compare Database

 Option Explicit

 Private Const strPathDbReceiver = "C:\pathOfDestinationDB"

Sub exporTables()

 Dim tbf As DAO.TableDef

 Dim dbs As DAO.Database

 Dim i As Integer

 Set dbs = DBEngine(0)(0)

 DoCmd.SetWarnings False

 For Each tbf In dbs.TableDefs

     If (tbf.Attributes And dbSystemObject) Then

     Else

         DoCmd.TransferDatabase acExport, "Microsoft Access", strPathDbReceiver, acTable, tbf.Name, tbf.Name

     End If

 Next

 DoCmd.SetWarnings True

 Set dbs = Nothing

End Sub

Answer 3

How are you doing this manually now? If you are just using the export wizard you can write a macro for that, but If I remember right 2010 doesn't recognize 97 as a access file format. I can export to a 2003 database but I have no way to test on 97.

I would think saving the table as a csv file and then importing that file into your 97 database would work.