Exporting Data to an External Access Database.

[Robeen]

Hi,

I need help with code that will allow me to take data [All Fields] from a Table in my Database and Insert them into another identically structured Table in another MS Access accdb Database.
I would also like to filter for records older than a month [MyDateField]


I have tried the following code I found on line. It doesn't throw an error . . . but it doesn't perform the Insert either. I'd appreciate some help on the best way to do this.

  Dim ws As DAO.Workspace   'Current workspace (for transaction).
  Dim db As DAO.Database    'Inside the transaction.
  Dim bInTrans As Boolean   'Flag that transaction is active.
  Dim strSql As String      'Action query statements.
  Dim strMsg As String      'MsgBox message.
  'Step 1: Initialize database object inside a transaction.
  Set ws = DBEngine(0)
  ws.BeginTrans
  bInTrans = True
  Set db = ws(0)
  'Step 2: Execute the append.
  strSql = "INSERT INTO MyTable_Archive ( Field1, Field2, Field3) " & _
    "IN ""\\MyPath\MyArchiveDB.accdb"" " & _
    "SELECT Field1, Field2, Field3 " & _
    "FROM MyTable;" 
   
  db.Execute strSql, dbFailOnError

I'd appreciate any help.

Thanks!!

[Robeen]

Cancel this. Sorry.

I got it to work using a different approach [one I use more often]:

                strInsertSQL = "INSERT INTO MyTable_Archive (Fld1, fld2, fld3)  "
                strInsertSQL = strInsertSQL & " IN ""MyPath\Archive.accdb"" "
                strInsertSQL = strInsertSQL & "SELECT Fld1, fld2, fld3 FROM MyTable;"

                DoCmd.SetWarnings False
                DoCmd.RunSQL strInsertSQL
                DoCmd.SetWarnings True

Again . . . apologies.