Hello.
I'm facing an issue that I don't know how to overcome. I have 5 different access with a table (called "store") that have the same structure than the destination table ("allstores") in my current database. I want to import the entries of these 5 tables (and perform some queries between importations) to other access table. My idea was (I used this method for other thing and it worked):
I have a table ("Table_names") with 5 entries that each entry represent the path of each .accdb file:
Table_name : C:\Users\XX\Downloads\DATABASE1.accdb
Table_name : C:\Users\XX\Downloads\DATABASE2.accdb
...
The vba code is supplied by these entries that have the path of each accdb file.
I have a vba code that is this:
Option Compare Database
Function Importar_seq()
DoCmd.SetWarnings False
'Purpose: How to open a recordset and loop through the records.
'Note: Requires a table named MyTable, with a field named MyField.
Dim rs As DAO.Recordset
Dim strSql As String
Dim oTableName As String
strSql = "SELECT Table_name FROM Table_Names;"
Set rs = DBEngine(0)(0).OpenRecordset(strSql)
oTableName = "allstores"
Do While Not rs.EOF
Debug.Print rs!Table_name
DoCmd.TransferDatabase acImport, "Microsoft Access", rs!Table_name, acTable, "store", oTableName, 0, False
CurrentDb.Execute "delete_old_records"
CurrentDb.Execute "Append_to_master_table"
CurrentDb.Execute "delete_aux_table"
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
End Function
The problem is that access create a "allstores1" table, assuming that "allstores" table doesn't exist. I read that is the behaviour that Docmd.TransferDatabase acImport works but I can't figure an alternative.
Any help?
Thank you!
I'm facing an issue that I don't know how to overcome. I have 5 different access with a table (called "store") that have the same structure than the destination table ("allstores") in my current database. I want to import the entries of these 5 tables (and perform some queries between importations) to other access table. My idea was (I used this method for other thing and it worked):
I have a table ("Table_names") with 5 entries that each entry represent the path of each .accdb file:
Table_name : C:\Users\XX\Downloads\DATABASE1.accdb
Table_name : C:\Users\XX\Downloads\DATABASE2.accdb
...
The vba code is supplied by these entries that have the path of each accdb file.
I have a vba code that is this:
Option Compare Database
Function Importar_seq()
DoCmd.SetWarnings False
'Purpose: How to open a recordset and loop through the records.
'Note: Requires a table named MyTable, with a field named MyField.
Dim rs As DAO.Recordset
Dim strSql As String
Dim oTableName As String
strSql = "SELECT Table_name FROM Table_Names;"
Set rs = DBEngine(0)(0).OpenRecordset(strSql)
oTableName = "allstores"
Do While Not rs.EOF
Debug.Print rs!Table_name
DoCmd.TransferDatabase acImport, "Microsoft Access", rs!Table_name, acTable, "store", oTableName, 0, False
CurrentDb.Execute "delete_old_records"
CurrentDb.Execute "Append_to_master_table"
CurrentDb.Execute "delete_aux_table"
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
End Function
The problem is that access create a "allstores1" table, assuming that "allstores" table doesn't exist. I read that is the behaviour that Docmd.TransferDatabase acImport works but I can't figure an alternative.
Any help?
Thank you!