Page 2 of 2 FirstFirst 12
Results 16 to 20 of 20
  1. #16
    moorecurls is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2018
    Posts
    24
    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

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

  3. #18
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    2,774
    That is NOT appending F1 and F2

    Quote Originally Posted by moorecurls View Post
    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
    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

  4. #19
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    2,774
    If that was directed at me, because I do not have the data and need something to test???
    Also it shows the code works?

    Quote Originally Posted by moorecurls View Post
    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.
    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

  5. #20
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    10,325
    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

    Code:
    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.

Page 2 of 2 FirstFirst 12
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