Hi,
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
Next
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"
cn.Open
' 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
Next
' 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
Next
Next
End If
Next
' Close the connection and databases
cn.Close
Set cn = Nothing
db.Close
db2.Close