copying a db table

[chongo 2002]

I want to copy all records from one table to another then erase all records in the first table. I got the code below from another post.

Dim db1 As Database
Dim db2 As Database
Dim strSQL As String
Set db1 = OpenDatabase("C:\MyDir\2000-04-13.mdb")
Set db2 = OpenDatabase("C:\MyDir\transport.mdb")
db2.Execute "DELETE * FROM MyTable", dbFailOnError
strSQL = "INSERT INTO MyTable IN 'C:\MyDir\transport.mdb' " _
       & "SELECT * FROM MyTable"
db1.Execute strSQL, dbFailOnError

My code:

Dim dbtemp As Database
Dim dbtemp1 As Database
Set dbtemp = OpenDatabase("" & App.Path & "\report.mdb")
Set dbtemp1 = OpenDatabase("" & App.Path & "\report.mdb")
SQL = "INSERT INTO [records] IN '" & App.Path & "\report.mdb'"" SELECT * FROM [records]"
dbtemp.Execute SQL, dbFailOnError
dbtemp1.Execute "DELETE * FROM [reports]", dbFailOnError

I get an error on my SQL = statement. Error 128. Anyone know how to fix it?

[paulw]

It would help to know what error 128 is and what DB Engine you are using (SQL Server?).

SQL = "INSERT INTO [records] IN '" & App.Path & "\report.mdb'"" SELECT * FROM [records]"

There are two problems - one, you are trying to Insert to [records] by selecting from [records] - I assume the SELECT should be from [reports]?, and, two, there seems to be some extra quotes in that SQL string. It should be:
SQL = "INSERT INTO [records] IN '" & App.Path & "\report.mdb' SELECT * FROM [records]"

The IN is a bit redundant, since you are already referencing that database (twice). You only need the one reference.

If its not clear, post again.

Cheers,

P.

[chongo 2002]

Yeah there were extra quotes, but i Think the code is out of order because it delets all the records but never adds them to the othe table, I get an syntax error if i do it this way:

Set dbtemp1 = OpenDatabase("" & App.Path & "\report.mdb")
dbtemp1.Execute "SELECT * FROM [reports] INSERT INTO [records]", dbFailOnError
'SQL = "INSERT INTO [records] IN '" & App.Path & "\report.mdb' SELECT * FROM [records]"
'dbtemp.Execute SQL, dbFailOnError
dbtemp1.Execute "DELETE * FROM [reports]"
dbtemp1.Close
End Sub

[chongo 2002]

I got the syntax to pass without errors, but it doesn't work. It deletes all the entries but it never adds them to the other table. I realize that the tables are in the same db but that should not have an effect on the actions. An suggestions?

[paulw]

Blimey, this is a cold one...

Your original INSERT is obviously failing (dbFailOnError). You need to trap that error and take appropriate action. Because you have no error trapping, the next statement runs whatever, so you are deleting all the records without the insert occurring.

You have checked Field Name/Type compatability, haven't you?

I suggest you put the INSERT SQL statement into a standalone query and run it to see what errors are generated. There is nothing wrong with the syntax, it must be a data structure problem.

Make the INSERT query work alone first...

Cheers,

P.

[chongo 2002]

I will try that and get back to ya, i don't get on the computer much.

[Chris]

chongo2002, I think this will do. may need not to use the IN clause.

Dim dbtemp As Database
Set dbtemp = DAO.OpenDatabase(App.Path & "\database1.mdb", False, False)
SQL = "INSERT INTO [" & App.Path & "\database2.mdb].[records] SELECT * FROM records;"
dbtemp.Execute SQL
dbtemp.Execute "DELETE * FROM reports;"
dbtemp.Close
Set dbtemp = Nothing

[Edited by Chris on 12-20-2000 at 02:24 AM]

[chongo 2002]

I got it working, Thanks to all!