Nested VBA append query doesn't append

[ChipR]

I'm not sure about the rest of your INSERT statement, but at first glance you have to change
INSERT INTO Db.TableDefs(i) .Name IN
to
INSERT INTO " & Db.TableDefs(i) .Name & " IN

[Stewart Ross]

Hi. You can't use the direct references to tabledef properties and VB variables in SQL statements - they mean nothing to the SQL interpreter. If the insert into approach is to work at all - and I'm not convinced it will - you will need to refer to the values of these items, not their names. I show part of the approach you will need to take below.

DoCmd.RunSQL "INSERT INTO " & Db.TableDefs(i).Name & " IN " & todb & " SELECT * FROM " & fromdb & ...

-Stewart

[jmoudy77]

Updated but still not working

Private Sub cmdimport_Click()

    Dim i As Integer
    Dim fromdb As String
    Dim fromdb2 As String
    Dim todb As String
    
    
    On Error Resume Next
    
    todb = "'" & Forms![AMS Import Utility].txtto & "'"
    fromdb = Forms![AMS Import Utility].txtfrom
    fromdb2 = "'" & Forms![AMS Import Utility].txtfrom & "'"
    
    
    Set Db = CurrentDb()
    For i = 0 To Db.TableDefs.Count - 1
        DoCmd.RunSQL "INSERT INTO Db.TableDefs(i).Name IN todb SELECT Db.TableDefs(i).Name.* FROM fromdb  WHERE (Mid(Db.TableDefs(i).Name, 1, 4) <> 'MSys')"
        Debug.Print Db.TableDefs(i).Name
    Next
    
End Sub

[jmoudy77]

I've been using queries in VBA for a while and usually have a pretty good handle on them, but this one is kicking my butt. Using Access 2007.

The form is designed to export(append) records from tables in the current database to identical tables in another database.

I know the loop is working because the table names are printed to the immediate window, but when I check the database being appended to the information isn't there. I am also not recieving any error messages; I'm stumped.

Pretty straight forward:

where txtto and txtfrom are textboxes

Private Sub cmdimport_Click()

    Dim i As Integer
    Dim fromdb As String
    Dim todb As String
    
    On Error Resume Next
    
    todb = Me.txtto
    fromdb = Me.txtfrom
    
    Set Db = OpenDatabase(fromdb)
    For i = 0 To Db.TableDefs.Count - 1
        If Mid(Db.TableDefs(i).Name, 1, 4) <> "MSys" Or "USys" Then
            DoCmd.RunSQL "INSERT INTO Db.TableDefs(i).Name IN fromdb SELECT AVSTATS.* FROM Db.TableDefs(i).Name);"
            Debug.Print Db.TableDefs(i).Name
        End If
    Next
    
End Sub

Thanks in advance