Results 1 to 7 of 7
  1. #1
    jo15765's Avatar
    jo15765 is offline Expert
    Windows 10 Access 2016
    Join Date
    Nov 2010
    Location
    6 Feet Under
    Posts
    670

    VBA To Insert Data Into Table Based Off Table Name


    Hi - my database has 5 tables in it. I need to ignore the table called tblUserSpecs, but conditionally say
    Code:
    if tableName = tblImport Then
       insert into tblProd
    else if tableName = tblImport1 Then
      insert into tblMaster
    else if tableName = tblImport2 Then
       insert into tblSalesData
    else if tableName = tblImport3 Then
       insert into tblEmpNumbers
    end if
    obviously that is not valid syntax just showing what I need to make happen.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    How is variable tableName set? Put table name within quote marks in expression.

    INSERT what from where?

    You have the correct idea with this pseudocode, why not try some real code?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    jo15765's Avatar
    jo15765 is offline Expert
    Windows 10 Access 2016
    Join Date
    Nov 2010
    Location
    6 Feet Under
    Posts
    670
    This is what I cam up with...it works, but may not be the cleanest solution
    Code:
    Function Cleanup()
    
    
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Set db = CurrentDb
    For Each tdf In db.TableDefs
        If Not (tdf.Name Like "MSys*" Or tdf.Name Like "~*") Then
            If tdf.Name = "tblOne" Then
                DoCmd.RunSQL ("INSERT INTO MasterOne SELECT tblOne.* FROM tblOne;")
                DoCmd.RunSQL ("DELETE tblOne.* FROM tblOne;")
            End
            If tdf.Name = "tblTwo" Then
                DoCmd.RunSQL ("INSERT INTO MasterTwo SELECT tblTwo.* FROM tblTwo;")
                DoCmd.RunSQL ("DELETE tblTwo.* FROM tblTwo;")
            End
            If tdf.Name = "tblThree" Then
                DoCmd.RunSQL ("INSERT INTO MasterThree SELECT tblThree.* FROM tblThree;")
                DoCmd.RunSQL ("DELETE tblThree.* FROM tblThree;")
            End
            If tdf.Name = "tblFour" Then
                DoCmd.RunSQL ("INSERT INTO MasterFour SELECT tblFour.* FROM tblFour;")
                DoCmd.RunSQL ("DELETE tblFour.* FROM tblFour;")
            End
        End If
    Next
    Set tdf = Nothing
    Set db = Nothing
    
    
    
    
    End Function

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Might be 'cleaner' to concatenate variables into SQL statement so there are not multiple sets of RunSQL commands. It is not necessary to specify fields in DELETE action. I prefer CurrentDb.Execute. This could be a Sub.
    Code:
    Sub Cleanup()
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Set db = CurrentDb
    For Each tdf In db.TableDefs
        If Not (tdf.Name Like "MSys*" Or tdf.Name Like "~*") Then
             db.Execute "INSERT INTO Master" & Mid(tdf.Name,4) & " SELECT * FROM " & tdf.Name
             db.Execute "DELETE FROM " & tdf.Name
        End If
    Next
    Set tdf = Nothing
    Set db = Nothing
    End Sub
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    jo15765's Avatar
    jo15765 is offline Expert
    Windows 10 Access 2016
    Join Date
    Nov 2010
    Location
    6 Feet Under
    Posts
    670
    Genious! I knew there was "cleaner" way of writing it. I am good at producing the syntax that the VBE in Excel gives you...haha. Not so swift at condensing it and removing the 'un-neccessary' parts.

  6. #6
    jo15765's Avatar
    jo15765 is offline Expert
    Windows 10 Access 2016
    Join Date
    Nov 2010
    Location
    6 Feet Under
    Posts
    670
    Okay - i am revisiting this as I have now implemented it into my code (using the code from post #3) and when i debug my app I get an error

    Compile error - next without for

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Need End If not just End.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Replies: 1
    Last Post: 11-19-2018, 06:53 PM
  2. Replies: 3
    Last Post: 08-23-2015, 12:11 PM
  3. Replies: 1
    Last Post: 06-12-2015, 02:02 PM
  4. Replies: 5
    Last Post: 12-01-2014, 11:31 AM
  5. Replies: 1
    Last Post: 12-28-2010, 11:24 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