Transfer data between two databases

[Melvana]

here's my problem :

I need to replace all the records of all the tables
in my database called 'transport.mdb' by all the records
of all the tables of my other database called '2000-04-13.mdb'. Sound simple? Not for me.

I'm doing this in VB6 with Access databases. All I've been able to do is to delete all the records of the tables in
'transport.mdb' and now I don't know how to 'copy' the
content of '2000-04-13.mdb' into 'transport.mdb'.

What would you suggest me to do?

An example would be very appreciated... :)

[Bigley]

Well before deleting them all I would create a recordset of all the records, then open a connection to the other database and insert them into the appropriate table. Never had to do it though so I dont have an example to hand.

[BruceG]

If the structures of both mdb's are the same, the problem is not really a database issue, but a file copying issue. You could use:

FileCopy "C:\MyDir\2000-04-13.mdb", "C:\MyDir\transport.mdb"


To copy the contents of an individual table from one mdb to another, assuming the structure of both tables is identical, you could use:

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


[Edited by BruceG on 10-06-2000 at 07:47 AM]

[Melvana]

Thanks BruceG for your help :)

Even if the structure of both table are identical,
the FileCopy function doesn't works for me because
the file is already open but the example you wrote after
that worked just fine.

Thank you again :)