I have linked tables that all start with "ABC" as an example that I need to load to a local table called "IT_Orig_Data". There are two fields in the linked tables ([ABC*].[F1] & [ABC*].[F2]). I need these fields to concatenate into the local table as "AllFields". The local tabl [IT_Orig_Data] already have the fields "AllFields" as Long Text and FileName as Short Text. The code below is what I have so far:
Function AppendTableData()
'Variable Declaration
Dim strSQL As String
Dim TableName As String
Dim FieldName As String
Dim db As Database
Dim tdf As TableDef
Set db = CurrentDb
For Each tdf In db.TableDefs
If Left(tdf.Name, 4) = "ABC" Then
Debug.Print tdf.Name & IIf(tdf.SourceTableName <> "", " Source_Table: " _
& tdf.SourceTableName, "")
TableName = tdf.Name
FieldName = Source_Table
'Run SQL Query (Insert)
strSQL = "INSERT INTO IT_Orig_Data ([AllFields],[FileName]) VALUES ('([F1]&[F2]), FieldName " & TableName & "' );"
DoCmd.SetWarnings False
DoCmd.RunSQL (strSQL)
DoCmd.SetWarnings True
Set task = Nothing
Set db = Nothing
Set Field = Nothing
End If
Next
End Function
I'm getting an error when it reaches the DoCmd.RunSQL(strSQL) - Run-time error '3346': Number of query values and destination fields are not the same
I need to concatenate the fields [F1]&[F2] and append into IT_Orig_Data as [AllFields], then I need the table name that is stored in the variable tdf.Name to append into IT_Orig_Data as [FileName]. What am I doing wrong? Any assistance would be wonderful! Thanks in advance.