Using INSERT INTO statement in Access 2003 VBA

[vbavir]

the code I am working on imports a txt file into a temporary table - the table names and fields change with each txt file that is imported - so I do not know what the field names will be or tables being used to import the data

here is what I currently have for code

Dim db As Database
Dim rs As Recordset
Dim rsDATA As Recordset, rsID As Recordset
Dim tb As TableDef

Dim intRS As Integer, FldNo As Integer
Dim x As Long, lngID As Long
Dim strTName As String, strfield As String, strTMP As String, strQuery As String
Dim fld As Field


Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT DISTINCT tblTableDetail.[tTables],tblTableDetail.[Import_Priority] FROM tblTableDetail WHERE (tblTableDetail.[EDI_Transaction]= '" & strTrans & "') ORDER BY Import_Priority;")

'loop through Table Detail to get each table need for 'insert statement
Do Until intRS = rs.RecordCount
strTName = rs!tTables 'destination table
strTMP = "TMP" & strTName
Set rsDATA = db.OpenRecordset(strTMP) 'Temporary table holding the data
Set tb = db.TableDefs(strTName)
Set rsID = db.OpenRecordset(strTName)

' Load the recordset's data into new table
' an INSERT statement for each.

'Here is where the code stops
'I know the statement is not correct
'but not sure if this statement is possible
'I just want to insert all from one table into another


strQuery = ("INSERT INTO strTName SELECT * FROM strTMP;")

con.Execute strQuery ' add the record


'the error message is - 424-Object required

 

[Golom]

What are these for? You don't seem to be using them (or need them).

Set rsDATA = db.OpenRecordset(strTMP)   'Temporary table holding the data
Set tb = db.TableDefs(strTName)
Set rsID = db.OpenRecordset(strTName)

strQuery = "INSERT INTO [" & strTName & "] SELECT * FROM [" & strTMP & "];"

[red]db[/red].Execute strQuery ' add the record

What is con? Should it be db.Execute ... ?

This assumes that strTName and strTMP have exactly the same structure and that both exist before you run the query.

 

[vbavir]

the purpose for this code is to insert the data from the temporary table into the final destination table which has primary keys and foreign keys which are used to establish a relationship

strTName is the final destination table the strTMP is the temporary table which holds the imported data

currently I was able to get the code to work by using the docmd.openquery - command to execute a query I build in access