Dynamically Update Append Query 1

[corycrum]

Hi,

I have a database with approximately 60 tables that are linked to 60 distinct Excel files. Each of these tables has an identical format and layout. The only difference is the data in the tables. One table shows sales, another shows cost of sales, another shows interest expense, etc.

I want to append all of these tables to a "master" table with an append query. Rather than build out 60 append queries, can I build one append query that dynamically updates and loops through all 60 tables and dumps the data into a master table?

What is the most efficient way to do this?

Thanks!

 

[dhookom]

You can create a query of all the Excel linked tables with SQL like:

SELECT *
FROM MsysObjects;

I'll leave it up to you to refine this to the appropriate tables linked from Excel.
Then write some DAO code to open the query as a recordset and then loop through it record by record to create an append query SQL statement and execute it:

Dim db as DAO.Database
Dim rs as DAO.Recordset
Dim strSQL As String
Dim strTable as String
SET db = Currentdb
Set rs = db.OpenRecordset("qselExcelTableNames")
With rs
  Do Until .EOF
    strTable = .Fields(0)
    strSQL = "INSERT INTO tblNewOne (fielda, fieldb,...) " & _
      "SELECT Fielda, fieldb,... FROM [" & strTable & "]"
    db.Execute strSQL, dbFailOnError
    .MoveNext
  Loop
  .Close
End With
Set rs = Nothing
Set db = Nothing

Duane
Hook'D on Access
MS Access MVP

 

[corycrum]

Thanks! I think this will get me there. However, there is one complicating factor I didn't mention above which I'm having a little trouble with.

Each of the 60 Excel tables have slightly different field names. They are in a crosstab format by year. For example, "T_Sales" has the following fields...

EntityId, RecentSales, 2008Sales, 2007Sales, 2006Sales...

"T_Cost_of_Sales" has the following fields...

EntityId, RecentCOS, 2008COS, 2007COS, 2006COS...

So, the field names in the SELECT statement above need to be variables rather than static text like "FieldA".

I have added the needed field name values for each table in the "table names" recordset. I'm not much of a programer, but i have taken a stab at writing out the code. Can you provide me some guidance? Here is my code...

Dim strTable As String
Dim strAccountId As String
Dim strFieldNameRecent As String
Dim strFieldName2008 As String
Dim strFieldName2007 As String
Dim strFieldName2006 As String

..........

Set rs = db.OpenRecordset("Q_Sys_Linked_Excel_Tables")
With rs
Do Until .EOF
strTable = .Fields("TableName")
strAccountId = .Fields("AccountId")
strFieldNameRecent = .Fields("Recent")
strFieldName2008 = .Fields("2008")
strFieldName2007 = .Fields("2007")
strFieldName2006 = .Fields("2006")

..........

"SELECT " & strAccountId &", " & strFieldNameRecent & ", " & FieldName2008 & ", " & strFieldName2007&", "& strFieldName2006 & " FROM [" & strTable & "]

 

[dhookom]

I don't like using SELECT * but if your columns are all consistent, you might try:

    strSQL = "INSERT INTO tblNewOne  " & _
      "SELECT * FROM [" & strTable & "]"

Duane
Hook'D on Access
MS Access MVP

 

[corycrum]

Thanks! That worked!