HELP! I'm trying to figure this out. This is what I'm trying to do. I have a group of tables with the same specs. I want to programmatically append the information in these tables into a master table, aptly named, master table.
Here's the Code. I get a syntax error and i don't know why. :(
Your help would be greatly appreciated.
Public Function AppendFile()
'The AppendFile function appends individual company tables to the Master Table, tblMaster.
Dim qryAppend As QueryDef
Dim rstAppend As Recordset
Dim db As Database
Set db = CurrentDb()
Set rstAppend = db.OpenRecordset("tblFilePath")
rstAppend.MoveFirst
Do Until rstAppend.EOF()
DoCmd.DeleteObject acQuery, "AppendImports"
Set qryAppend = CurrentDb.CreateQueryDef("AppendImports")
qryAppend.SQL = "INSERT INTO tblMASTER ( Company_Number, Location_Code, Customer_Number, Customer_Name," & _
"Address, ZipCode, Geocode, State, County, City, Gross_Sales, Gross_RX_Sales, Gross_OTC_Sales, Gross_Lease_Sales," & _
"Exempt_Sales, Exemption_Code, Taxable_Sales, Taxable_RX_Sales, Taxable_OTC_Sales, Taxable_Lease_Sales, " & _
"Taxable_Purchases, Sales_Tax, RX_Tax, OTC_Tax, Sellers_Use, Consumers_Use, Rental_Tax, Tax_Rate, " & _
"Period_Begin_Date, Period_End_Date)"
"SELECT " & rstAppend.Fields(3).Value & ".Company_Number, " & rstAppend.Fields(3).Value & ".Location_Code," & rstAppend.Fields(3).Value & _
".Customer_Number," & rstAppend.Fields(3).Value & ".Customer_Name," & rstAppend.Fields(3).Value & ".Address," _
& rstAppend.Fields(3).Value & ".ZipCode," & rstAppend.Fields(3).Value & ".GeoCode," & rstAppend.Fields(3).Value _
& ".State," & rstAppend.Fields(3).Value & ".County," & rstAppend.Fields(3).Value & ".City," & rstAppend.Fields(3).Value _
& ".Gross_Sales," & rstAppend.Fields(3).Value & ".Gross_RX_Sales," & rstAppend.Fields(3).Value & ".Gross_OTC_Sales," _
& rstAppend.Fields(3).Value & ".Gross_Lease_Sales," & rstAppend.Fields(3).Value & ".Exempt_Sales," _
& rstAppend.Fields(3).Value & ".Exemption_Code," & rstAppend.Fields(3).Value & ".Taxable_Sales," & rstAppend.Fields(3).Value _
& ".Taxable_RX_Sales," & rstAppend.Fields(3).Value & ".Taxable_OTC_Sales," & rstAppend.Fields(3).Value & ".Taxable_Lease_Sales," _
& rstAppend.Fields(3).Value & ".Taxable_Purchases," & rstAppend.Fields(3).Value & ".Sales_Tax," & rstAppend.Fields(3).Value & ".RX_Tax," _
& rstAppend.Fields(3).Value & ".OTC_Tax," & rstAppend.Fields(3).Value & ".Sellers_Use," & rstAppend.Fields(3).Value & ".Consumers_Use," _
& rstAppend.Fields(3).Value & ".Rental_Tax," & rstAppend.Fields(3).Value & ".Tax_Rate," & rstAppend.Fields(3).Value & ".Period_Begin_Date," _
& rstAppend.Fields(3).Value & ".Period_End_Date FROM " & rstAppend.Fields(3).Value & ";"
DoCmd.OpenQuery "AppendImports", acViewNormal
rstAppend.MoveNext
qryAppend.Close
Loop
End Function
Syntax Error
