I am having some with my program. I want to copy table content and data from one database to identical tables in different databases. (overriding the old tables)

So far, in the different databases, I am able to delete the original table, create a new table with the same name, and create the correct, appropiate fields, but I am having major issues with copying the data over.

I am having the error "Invalid argument" at the starred line below in my code.

Can someone please help?!

' Open database with the standard tables
    Set db = OpenDatabase(Text1.text)

    ' Opening the selected database(s) in List3
    For Counter = 0 To List3.ListCount - 1
        Text3.text = List3.List(Counter)
        Set db2 = OpenDatabase(Text3.text)

        ' Iterating through the tables in List1
        For i = 0 To db.TableDefs.Count - 1
            Set tdf = db.TableDefs(i)

            ' Iterating through the tables in the database(s) in List2
            For j = 0 To db2.TableDefs.Count - 1
                Set tdf2 = db2.TableDefs(j)

                ' Filter all system tables
                If ((tdf2.Attributes And &H2) = 0 And (tdf2.Attributes And &H80000000) = 0) Then
                    ' Delete the tables from the database(s) in List2 that match the table names in List1
                    If (tdf2.Name = tdf.Name) Then
                        ' SQL query to delete the tables
                        stringSQL = "DROP TABLE " & tdf2.Name
                        db2.Execute stringSQL
                    End If
                End If
    Next Counter

    ' Open connections to source and destination databases
    Set cn = New ADODB.Connection
    cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Text1.text & ";Persist Security Info=False"

    ' Iterates through the tables in List1
    For i = 0 To db.TableDefs.Count - 1
        If ((db.TableDefs(i).Attributes And &H2) = 0 And (db.TableDefs(i).Attributes And &H80000000) = 0) Then
            Set tdf = db.TableDefs(i)
            Set rs = db.OpenRecordset(tdf.Name)
            Num_Of_Fields = rs.Fields.Count                                 ' Calculates number of fields in the recordset

            For j = 0 To List3.ListCount - 1
                Text3.text = List3.List(j)
                Set db2 = OpenDatabase(Text3.text)

                ' SQL query to create tables in DBs in List2 with names of tables in List1
                stringSQL2 = "CREATE TABLE " & tdf.Name
                db2.Execute stringSQL2

                ' Adds the correct number of fields
                For x = 0 To Num_Of_Fields - 1
                    ' SQL query to add the fields from the tables in List1 to the tables in the DBs in List2
                    stringSQL3 = "ALTER TABLE " & tdf.Name & " ADD COLUMN " & rs.Fields(x).Name & " TEXT(10); "
                    db2.Execute stringSQL3

                ' Iterate through the tables in DB2
                For y = 0 To db2.TableDefs.Count - 1
                    ' Check if any of the tables in DB2 has the same as any of the
                    ' tables in DB1
                    If (db2.TableDefs(y).Name = db.TableDefs(i).Name) Then

                        ' Set the TableDef to the table in DB2 that has the same name
                        ' as the table in DB1
                        Set tdf2 = db2.TableDefs(y)

                        ' SQL query to copy all data from the tables in List1 to the DBs in List2
                        **** stringSQL4 = "INSERT INTO " & tdf2.Name & " SELECT * FROM " & tdf.Name & "';" ****
                        **** cn.Execute stringSQL4 ****
                    End If
        End If

    ' Close the connection and databases
    Set cn = Nothing


Also when I replace line 69 above with:

db2.Execute stringSQL4

the data will is still not copied over.

Thank you.

This article has been dead for over six months. Start a new discussion instead.