Import tables from multiple different sources (1 Viewer)

jmsm

New member
Local time
Today, 13:50
Joined
Oct 1, 2020
Messages
12
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!
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:50
Joined
Oct 29, 2018
Messages
21,357
Hi. Are you trying to replace the old table or add new records to it?
 

jmsm

New member
Local time
Today, 13:50
Joined
Oct 1, 2020
Messages
12
Hi. Are you trying to replace the old table or add new records to it?
Hi.
The destination table is empty and the idea is to add new records, perform some delete queries and then append to a master table.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:50
Joined
Oct 29, 2018
Messages
21,357
Hi.
The destination table is empty and the idea is to add new records, perform some delete queries and then append to a master table.
Will it always be empty? If so, just delete it first before running TransferDatabase.
 

jmsm

New member
Local time
Today, 13:50
Joined
Oct 1, 2020
Messages
12
Will it always be empty? If so, just delete it first before running TransferDatabase.
How I can never thought this.. The simple ideas are always the best!

Thank you!
 

Users who are viewing this thread

Top Bottom