[RESOLVED] SQL Copy Table Records between Access Databases

[dw85745]

Any suggestions to get this to work greatly appreciated:

Geting Error #3024, Can't find the Db2 Database and gives me the default Visual Studio directory instead of the directory where the *.mdb is located
However this >> strDBPathGoodWS = GetPath(PATH_DBBackUp) is returning the correct path to the *.mdb

Public Sub RecordCopy_All(SourceName As String, DestName As String)
'Copy all record from Source Table to Destination Table
'Assumes Destination Table already exists

'NOTES:   First Db declared public as DaoDb, is open and is accessible

'Here making sure second Database is Defined and Open
Dim Db2 As Database
Dim strDBPathGoodWS As String

strDBPathGoodWS = GetPath(PATH_DBBackUp)
Set Db2 = OpenDatabase(strDBPathGoodWS)

'This is the SQL
   
   Dim strSQL As String
   
   strSQL = " INSERT INTO " & DestName & "" _
     & " SELECT * " _
     & "FROM " & SourceName & ";"


'SQL String from above passed for execution looks like this.>>>         INSERT INTO DaoDb.Restore2348_0_H SELECT * FROM Db2.Restore2348_0;
     
 
     
     DaoDb.Execute strSQL
     
  

End Sub

[dw85745]

Finally got the generic to work (After 24 hrs of *&^% effort)!!!!

The format is:

INSERT INTO TableTo SELECT * FROM TableFrom IN 'dbPathFrom';