Results 1 to 7 of 7
  1. #1
    dcdimon's Avatar
    dcdimon is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2013
    Location
    Bradenton, FL
    Posts
    71

    Question Append to multiple tables using a Loop and Array in VBA


    I'm not even sure if this is possible, but I have a number of queries I'd like to append to tables (1 table per query) in a split database. I want to make tables so the data and calculations I've made can be accessed by other users. Rather than create 10 append queries I'd like to be able to us a Do Until loop to run through all my queries and append them to their tables. I was thinking I can create a table with the query names and the corresponding table names and then use the loop to read through the table to get the query and append table name.

    However, I'm not having any luck getting this done. I've got the following code, but keep getting the error - "syntax error in query expression 'P1CntrLnQ.* Insert'."

    This is showing me that it's seeing the query name (P1CntrLnQ), but I'm not sure what else this is telling me - other than it doesn't like how the command is put together.

    Code:
    Dim rs As DAO.Recordset
    Dim db As DAO.Database
    Dim SQLstr As String
    
    Set rs = CurrentDb.OpenRecordset("AppendTablesT", dbOpenSnapshot)
    
    With rs
    .MoveFirst
    Do Until .EOF
    
    DoCmd.RunSQL "Select " & rs!queryname & " " _
                            & "Insert Into " & rs!appendname & " " _
                            & "FROM & rs!queryname"
    
            'db.Execute strSQL, dbFailOnError - original SQL execution
    
    rs.MoveNext
    Loop
    End With
    I changed to the docmd.runSQL from the remarked out db.Execute... simply because I didn't see a difference in how the script was running.

    Am I anywhere close or am I totally off my nut thinking I can use a loop command for this? Any help, even if it's 'give up, it can't be done' would be appreciated.

    DD

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    This should hellp:

    BaldyWeb-Immediate window

    Offhand, your string concatenation is off in the last line.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    The syntax is wrong - you have the SELECT keyword in the wrong place.
    The SQL should look more like

    "INSERT INTO Customers SELECT * FROM NewCustomers;"

    In your case, try this
    Code:
       Dim rs As DAO.Recordset
       Dim db As DAO.Database
       Dim SQLstr As String
    
       Set db = CurrentDb
    
       Set rs = db.OpenRecordset("AppendTablesT", dbOpenSnapshot)
    
       With rs
          .MoveFirst
          Do Until .EOF
    
             SQLstr = "INSERT INTO " & rs!appendname & " SELECT * FROM " & rs!queryname & ";"
             '     Debug.Print SQLstr   ' uncomment to see if the SQL string is correctly formed
    
             db.Execute SQLstr, dbFailOnError
    
             'DoCmd.RunSQL "Select " & rs!queryname & " " _
              & "Insert Into " & rs!appendname & " " _
              & "FROM & rs!queryname"
    
             rs.MoveNext
          Loop
       End With
    
       rs.Close
       Set rs = Nothing
       Set db = Nothing
    
       Msgbox "Done"  ' I use this line to know when the code finishes executing
    The difference between the commands "RunSQL" and "Execute", from what I understand, is that "RunSQL" processes the SQL through Access and "Execute" bypasses Access and the SQL is handled by JET (The database engine). "Currentdb.Execute" is generally faster.....

  4. #4
    dcdimon's Avatar
    dcdimon is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2013
    Location
    Bradenton, FL
    Posts
    71

    Thumbs up

    ssnafu,

    Woohoo!!! That worked great!

    I've reworked it to not only append data, but create tables as well. I was able to use your script as a basis to create a function that appends/ creates tables with just a few of the fields when all would be overkill.

    I'm now working on getting a dynamic WHERE statement attached - a static one is already working - to only update info that's not already in the table.

    Thank you for giving me that script - I was close, but because I had been staring at it for the better part of a day I couldn't see where I was off.

    And also thank you for the difference between Execute and RunSQL. The Execute function seems much faster than the RunSQL and it has almost no performance hit on Access.

    Thanks again.

    DD

  5. #5
    Xipooo's Avatar
    Xipooo is offline Sr. Database Developer
    Windows 8 Access 2013
    Join Date
    Jan 2014
    Location
    Arizona
    Posts
    332
    Glad it worked for you!

    Be sure to mark the thread as solved and give the person who answered your question a bump in their reputation!

  6. #6
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Thank you for giving me that script
    You're welcome. glad you got it working.
    Sometimes I look at the VBA so long, I can't see the obvious errors, let alone any errors.
    Then I have to do something else for a while - then the error(s) usually jump out at me.
    The code was also meant to be a learning tool (plus I was to lazy to type out instructions). Seems that you have already benefited from it.


    The Execute function seems much faster than the RunSQL and it has almost no performance hit on Access.
    If I am appending records, I always use the "Execute" method for that reason... (from what I have read.... I have not done performance testing)


    I've reworked it to not only append data, but create tables as well.
    You might want to rethink this. If it is necessary to use a temp table to hold data for a report (or whatever), then it should be created at design time and not constantly recreated. When you need to use the "temp" table, execute a delete query to clear out old data, then append the new data. I use a "temp" table due to it being the only way to generate the record source of a report.

    Constantly creating tables using make table queries in the production BE (you do have the db split??) causes bloat. Plus there is a greater chance of corrupting the dB. So I never use make table queries.

  7. #7
    dcdimon's Avatar
    dcdimon is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2013
    Location
    Bradenton, FL
    Posts
    71
    Quote Originally Posted by ssanfu View Post
    You might want to rethink this. If it is necessary to use a temp table to hold data for a report (or whatever), then it should be created at design time and not constantly recreated. When you need to use the "temp" table, execute a delete query to clear out old data, then append the new data. I use a "temp" table due to it being the only way to generate the record source of a report.
    Yes, I do have a split db - the tables are being used to populate production metrics in another app, so they're full of a lot of calculations that I can do easier in Access and then I send the data up to a table on our SQL server. I used the create tables to get the tables started - I didn't feel like making them manually - and then those tables were pushed up to our server. After that I switched the script over to append.

    DD

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Combine Multiple Access Files into One and Append Tables
    By KLTurner in forum Import/Export Data
    Replies: 10
    Last Post: 01-02-2014, 11:38 AM
  2. Replies: 6
    Last Post: 05-31-2013, 07:46 PM
  3. Loop or Array not recognized
    By dvgef2 in forum Forms
    Replies: 6
    Last Post: 05-31-2013, 08:37 AM
  4. Replies: 30
    Last Post: 08-30-2012, 05:14 PM
  5. Append to multiple tables
    By GraemeG in forum Queries
    Replies: 1
    Last Post: 04-05-2012, 11:42 AM

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