Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    moorecurls is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2018
    Posts
    24

    VBA SQL INSERT concatenated field and variable table name

    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.

  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Debug.print strsql and check it is correct.
    Looks like you also have an errant single quote?
    No continuation character and " in incorrect placement.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  3. #3
    moorecurls is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2018
    Posts
    24
    Thanks so much Welshgasman,

    I've printed the below:


    INSERT INTO IT_Orig_Data ([AllFields],[FileName]) VALUES ('ZAPR003_P9006_csv 'F1' & 'F2', ZAPR003_P9006.csv ');

    The table is correct ZAPR003_P9006_csv but I need to concatenate the fields F1 and F2 which are in the table ZAPR003_P9006_csv. I need to know the syntax or where the errant single quote is...I'm missing it! Thanks for your help.

  4. #4
    moke123's Avatar
    moke123 is online now Me.Dirty=True
    Windows 11 Office 365
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    Assuming those fields are text I would think it should look more like

    Code:
    strSQL = "INSERT INTO IT_Orig_Data (AllFields,FileName) VALUES (""" & F1 & " " & F2 & """,""" & FieldName & " " & TableName & ")"""
    If this helped, please click the star * at the bottom left and add to my reputation- Thanks

  5. #5
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    1. Turn off warnings until you have resolve the issue
    2. use debug.print sqlstr to see what it looks like before you run it

  6. #6
    moorecurls is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2018
    Posts
    24
    I have an SQL that runs but it is not working correctly:
    strSQL = "INSERT INTO IT_Orig_Data ([AllFields],[FileName]) VALUES ('F1 & F2', '" & FieldName & " ');"
    Debug.Print strSQL :
    INSERT INTO IT_Orig_Data ([AllFields],[FileName]) VALUES ('F1 & F2', 'ZAPR003_P9006.csv ');
    Which is how it is importing into the table:
    AllFields FileName
    F1 & F2 ZAPR003_P9006.csv

    This isn't what I want....I need all of the data that is in each over 25k rows that is in the fields F1 and F2 concatenated and appended into the table IT_Orig_Data with the FileName of ZAPR003_P9006.csv on each row.

    So I'm back to how do I concatenate a field in SQL vba?

  7. #7
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    You are putting single quotes around the concatenation.
    Do not even try to run the sql until you get it correct.
    Show examples of f1 and f2.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  8. #8
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    if you have 25,000 rows, I would expect your sql to have select and from parts

    can you show some example data and the result required to insert into your orig_data table sinc eyour code makes no sense to me.

    Source _table is undefined and is not assigned a value - but then you try to assign it to a variable called Field_name???

    and please use code tags to preserve indentation to make your code more readable

  9. #9
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Assuming they are strings.

    Code:
    Sub ConcatSQL()
    Dim F1 As String, F2 As String, strSQL As String, Filename As String, fieldname As String
    F1 = "Test"
    F2 = "Test2"
    Filename = "ZAPR003_P9006.csv"
    fieldname = "Text1"
    strSQL = "INSERT INTO IT_Orig_Data ([AllFields],[FileName]) VALUES ('" & F1 & F2 & "','" & fieldname & Filename & "')"
    
    Debug.Print strSQL
    
    End Sub
    INSERT INTO IT_Orig_Data ([AllFields],[FileName]) VALUES ('TestTest2','Text1ZAPR003_P9006.csv')
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  10. #10
    moorecurls is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2018
    Posts
    24
    Thanks moke123, Yes, both F1 and F2 fields are Short text. The field I'm appending to is long text. I'm hoping this isn't an issue. I tried the syntax but it wasn't working so I modified to the below:
    strSQL = "INSERT INTO IT_Orig_Data (AllFields,FileName) VALUES ('" & F1 & " " & F2 & "','" & FieldName & "');"
    It is debug. Printing as:
    INSERT INTO IT_Orig_Data (AllFields,FileName) VALUES (' ','ZAPR003_P9006.csv');
    Which gives me the two fields I need, however, it is appending to the table with the spaces:
    AllFields FileName
    ZAPR003_P9006.csv
    It seems that the fields F1&F2 are not being recognized as fields in the table ZAPR003_P9006_csv. I need to some how added in the TableName variable then the field names. in a qry, I would right it like this: INSERT INTO IT_Orig_Data ( AllFields, FileName )
    SELECT [F1]&[F2] AS AllFields, FieldName AS FileName
    FROM TableName;

    So I need to figure out the syntax in vba for the fields F1 and F2 be recognized as fields in the variable TableName. Any ideas, would help. Thanks

  11. #11
    moorecurls is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2018
    Posts
    24
    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.

  12. #12
    moorecurls is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2018
    Posts
    24
    Ok I give up. I'm going a different direction now. I'll just import each field F1, F2, and the variable FieldName. I thought it would be a simple fix. I changed both the IT_Orig_Data F1, and F2 to Short text as well. However, it is still not working. I'm now being told it needs to be a collection. I've tried that below but still need help.

    Function AppendTableData()
    'Variable Declaration
    Dim StrSQL As String
    Dim TableName As String
    Dim FieldName As String
    Dim Field1 As New Collection
    Dim Field2 As New Collection
    Dim F1 As New Collection
    Dim F2 As New Collection
    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
    Field1 = TableName & F1
    Field2 = TableName & F2

    'Run SQL Query (Insert)
    StrSQL = "INSERT INTO IT_Orig_Data (F1,F2,FileName) VALUES ('" & TableName!F1 & "', '" & TableName!F2 & "','" & FieldName & "');"
    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

    Any direction would be wonderful! Thanks so much.

  13. #13
    moke123's Avatar
    moke123 is online now Me.Dirty=True
    Windows 11 Office 365
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    I'm not sure I'm understanding your requirements.

    I'm guessing your linked table is an xl file or csv.

    What exactly is your long text field? are you concatenating all the records in the linked table to 1 record in the new table?
    If this helped, please click the star * at the bottom left and add to my reputation- Thanks

  14. #14
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    don't know who's telling you you need a collection, certainly not here.

    your brackets are all wrong, and I'm not at all clear why you are referencing the tabledef, I'm guessing tables that start with ZAPR are linked tables. Suggest try

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

  15. #15
    davegri's Avatar
    davegri is offline Excess Access
    Windows 11 Access 2019
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    No, no. Don't know what your error message about a collection is all about, but it certainly isn't stating you need a collection for this.
    Please explain in simple terms what you are trying to do; especially where the data for 24000 new records come from.
    1.Where do F1 and F2 come from? From the csv file?
    2.Does the csv file hold 24000 rows?
    3.Are there only 2 columns in IT_Orig_Data?, If not, how will you populate the other columns in the 24000 new records?
    4.What does the csv file table look like?
    5.How many csv tables are there?
    6.How many table names start with ZAPR?

    It's all very confusing without this basic explanation. Please answer all 6 questions, skipping none.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 16
    Last Post: 09-19-2021, 07:38 AM
  2. Replies: 11
    Last Post: 03-10-2019, 02:32 AM
  3. Replies: 7
    Last Post: 02-25-2019, 10:09 PM
  4. Replies: 9
    Last Post: 02-14-2014, 12:53 PM
  5. Formatting a concatenated field
    By kris335 in forum Access
    Replies: 14
    Last Post: 09-21-2011, 09:56 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums