VBA SQL INSERT concatenated field and variable table name

[moorecurls]

Hi CJ_London,

Thanks so much. This works, with one change. The fields didn't concatenate, but I can do that once I get everything all in the local table IT_Orig_data. So I've appended three fields, which is perfect! See code below that works:

Function AppendTableData()
'Variable Declaration
Dim StrSQL As String
Dim TableName As String
Dim FieldName As String
Dim db As Database
Dim tdf As TableDef
Dim SourceTableName As TableDef


Set db = CurrentDb


For Each tdf In db.TableDefs
If Left(tdf.Name, 4) = "ZAPR" Then


Debug.Print tdf.Name & IIf(tdf.SourceTableName <> "", " Source_Table: " _
& tdf.SourceTableName, "")
TableName = tdf.Name
FieldName = tdf.SourceTableName

'Run SQL Query (Insert)

StrSQL = "INSERT INTO IT_Orig_Data (AllFields,F2,FileName) " & _
" SELECT [F1], [F2], '" & FieldName & "' FROM " & TableName
Debug.Print StrSQL

DoCmd.SetWarnings False
DoCmd.RunSQL (StrSQL)
DoCmd.SetWarnings True
Set task = Nothing
Set db = Nothing
Set Field = Nothing
End If
Next


End Function

[moorecurls]

Hi Davegri, I'll answer the questions, but CJ_London gave me everything I needed.
1. F1 and F2 are fields that are in the "ZAPR*_csv" files. They are short text fields.
2. The "ZAPR*_csv" files have any number of lines. It just happens that the first on has 24k lines of data.
3. Originally the IT_Orig_Data which is a local table has two fields "AllFields" and "FileName". I was hoping to concatenate the F1 and F2 fields in the "ZAPR*_csv" files into the "AllFields" long text field in IT_Orig_Data. There are only two columns in "ZAPR*_csv" files. --- see the notes above, I've changed the IT_Orig_Data table to have three fields -- which works.
4. Really, this is proprietary info...sorry, but all I will tell you is that the fields are both text fields.
5. I'm testing 4 csv files, but I hear from the business that there could be hundreds.
6. Same as above, the csv files are the "ZAPR*_csv" files.
Sorry for any confusion. However, this is a closed ticket now. CJ_London helped resolved the issue!

[Welshgasman]

That is NOT appending F1 and F2

Hi CJ_London,

Thanks so much. This works, with one change. The fields didn't concatenate, but I can do that once I get everything all in the local table IT_Orig_data. So I've appended three fields, which is perfect! See code below that works:

Function AppendTableData()
'Variable Declaration
Dim StrSQL As String
Dim TableName As String
Dim FieldName As String
Dim db As Database
Dim tdf As TableDef
Dim SourceTableName As TableDef


Set db = CurrentDb


For Each tdf In db.TableDefs
If Left(tdf.Name, 4) = "ZAPR" Then
Debug.Print tdf.Name & IIf(tdf.SourceTableName <> "", " Source_Table: " _
& tdf.SourceTableName, "")
TableName = tdf.Name
FieldName = tdf.SourceTableName

'Run SQL Query (Insert)

StrSQL = "INSERT INTO IT_Orig_Data (AllFields,F2,FileName) " & _
" SELECT [F1], [F2], '" & FieldName & "' FROM " & TableName
Debug.Print StrSQL

DoCmd.SetWarnings False
DoCmd.RunSQL (StrSQL)
DoCmd.SetWarnings True
Set task = Nothing
Set db = Nothing
Set Field = Nothing
End If
Next


End Function

[Welshgasman]

If that was directed at me, because I do not have the data and need something to test???
Also it shows the code works?

Hi thanks for your help, but I don't understand why examples of F1 and F2 are needed. This is proprietary information. They are both field names in the linked tables that all start with ZAPR and both fields are text. There are many ZAPR* that is why I created a variable TableName. CJ_London, I like the idea of needing a Select within the strSQL. I'll give that a try. Although I'm not sure exactly how to do it.

I've changed the vba to below:

Function AppendTableData()
'Variable Declaration
Dim strSQL As String
Dim TableName As String
Dim fieldname As String
Dim F1 As String
Dim F2 As String
Dim db As Database
Dim tdf As TableDef
Dim SourceTableName As TableDef


Set db = CurrentDb


For Each tdf In db.TableDefs
If Left(tdf.Name, 4) = "ZAPR" Then
Debug.Print tdf.Name & IIf(tdf.SourceTableName <> "", " Source_Table: " _
& tdf.SourceTableName, "")
TableName = tdf.Name
fieldname = tdf.SourceTableName
'Run SQL Query (Insert)
'strSQL = "INSERT INTO IT_Orig_Data [(AllFields,[FileName)] " & _
"SELECT [F1]&[F2] as [AllFields], FieldName as [FileName] FROM TableName"
'strSQL = "INSERT INTO IT_Orig_Data ([AllFields],[FileName]) VALUES ('([F1] & [F2])', '" & FieldName & " ');"
'strSQL = "INSERT INTO IT_Orig_Data (AllFields,FileName) VALUES (" & TableName &'"F1" &" & TableName & " "F2" ','" & FieldName & "');"
strSQL = "INSERT INTO IT_Orig_Data ([AllFields],[FileName]) SELECT VALUES ('" & F1 & F2 & "','" & fieldname & "');"
Debug.Print strSQL


'strSQL = "INSERT INTO IT_Orig_Data ([AllFields],[FileName]) VALUES ('" & TableName & " [F1 & F2], " & FieldName & " ');"
'strSQL = "INSERT INTO IT_Orig_Data ([AllFields],[FileName]) VALUES ('([F1]&[F2]) " & FieldName & TableName & "' );"


'strSQL = "INSERT INTO IT_Orig_Data ([AllFields],[FileName]) VALUES ('([F1]&[F2]) as [AllFields],FieldName as [FileName] " & TableName & "' );"
'strSQL = "INSERT INTO table-name (field-list) VALUES (data, data, " & variable & ", more-date) ;"


DoCmd.SetWarnings False
DoCmd.RunSQL (strSQL)
DoCmd.SetWarnings True
Set task = Nothing
Set db = Nothing
Set Field = Nothing
End If
Next


End Function

Thanks again for your help.

[CJ_London]

you don't make like easy for yourself

I provided

strSQL = "INSERT INTO IT_Orig_Data (AllFields,FileName) " & _
" SELECT [F1] & [F2], '" & FieldName & "' FROM " & TableName

you used

StrSQL = "INSERT INTO IT_Orig_Data (AllFields,F2,FileName) " & _
" SELECT [F1], [F2], '" & FieldName & "' FROM " & TableName

with the comment 'the fields didn't concatenate'


your whole function could be simplified to

Function AppendTableData()
'Variable Declaration
Dim StrSQL As String
Dim db As DAO.Database
Dim tdf As DAO.TableDef


    Set db = CurrentDb
    
    For Each tdf In db.TableDefs
        
        If Left(tdf.Name, 4) = "ZAPR" Then
            
            'Run SQL Query (Insert)
            
            StrSQL = "INSERT INTO IT_Orig_Data (AllFields, FileName) " & _
            " SELECT [F1] & [F2], '" & tdf.SourceTableName & "' FROM " & tdf.Name
            'Debug.Print StrSQL
            
            db.Execute StrSQL
            'msgbox db.recordsaffected & " records imported from " & tdf.name & " into IT_Orig_Data", vbOKonly
            
    
        End If
        
    Next


    Set db = Nothing


End Function

note the proper declaration of variable types, use of indenting and use of code tags. The indenting shows that your 'Set db = Nothing' was in the wrong place (corrected above)

I don't understand why examples of F1 and F2 are needed.

it's taken 19 posts to get this far. Examples and other info was requested because your original post was very unclear - maybe not to you but to us who have no knowledge of your application. If examples had been provided (they don't have to be real, just realistic), together with the other information requested then this could been resolved in 2 or 3 posts. Example data, views of relationships and tables speak a lot more to us than a description of those things.


We give our time for free and we don't like wasting our time.