VB6 or VBA - Copy table contents from one database to identical database

[tgifgemini]

Ladies and gentlemen,
first I want to wish each and every one of you a wonderful and prosperous new year.

This is not really a problem. I am looking for an efficient and quick way to copy table content from one database to identical tables in another identical database.

I have two Access databases: "PTSPROD" which is production database and "PTSTEST" which is a test database both databases have 10 identical tables.

Objective: I need to erase the contents of 5 of the tables in "PTSTEST" then copy contents of 5 similar tables from "PTSPROD" into "PTSTEST".

I could mannually delete the table contents, then copy and paste, but that will be crude.

Question: How can I programatically accomplish this task faster?

Thanks.
tgif

[SCBWV]

Why not just copy the file PTSTEST over PTSPROD?

[hkdani]

I assume that you have some idea of how to work with Access databases in VB6. If not, let me know.

But I would use the End of File property (EOF) in a loop to delete the records in the database.

With MyDatabase
      .movefirst
      While not .EOF
        .delete
        .movenext  
      Wend
End with

And would probably use the same method to load them from database 'A' to database 'B'.
You could declare some variables to use to keep the values of the fields. This would allow for error checking. Or you could just transfer them directly

DatabaseB.MoveFirst
While DatabaseB.EOF <> True
DatabaseA.AddNew

DatabaseA.Field("Name") = DataBaseB.Field("Name")
......
......
...... etc,

DatbaseA.Update
DataBaseB.MoveNext
Wend

Hank

[tgifgemini]

Hi "hkdani" and BWV,
thanks for your input. However, is there such a syntax like

database.movefirst

or did you mean

recordset.movefirst

?

Also, PTSPROD and PTSTEST databases have about 15 identical tables each and I want to delete and copy only five of the 15 tables.

Using your format and looping through to delete the database content may delete the tables which I do want to delete.

For example:
PTSPROD.mdb has these tables that I want to copy from:
"TblPersonnel", "TblDepartment", "TblProgrammersHours" and "TblRequestsAllPersonnel".

Also, PTSTEST.mdb has these tables that I want to delete, then repopulate with PTSPROD tables: "TblPersonnel", "TblDepartment", "TblProgrammersHours" and "TblRequestsAllPersonnel".

Now, how would this jive with your format?
Thanks,
tgif.

[hkdani]

thanks for your input. However, is there such a syntax like
(Toggle Plain Text)

database.movefirst

Weeeeellllllllllllllllll. That's a deep subject.

Option Explicit
Dim database as ADODB.Recordset

Private Sub Form_Load()
Set database = new ADODB.Recordset

database.MoveFirst
End Sub

I haven't tried the code. These are just quick notes to illustrate what you need to do. But if you're able to recognize what is proper syntax, I think you should have no problem figuring out how to properly declare and instantiate your own recordset variable objects.

But that's a good point. "database" isn't a very good name. And it may not even be allowed as a variable, if it's one of those words that are not allowed to be used.

Usually, you want to declare a Recordset Object Variable with something that begins with rst:

Dim rstCustomers as ADODB.recordset

Using your format and looping through to delete the database content may delete the tables which I do want to delete.

You said you wanted to delete contents.

There is a difference between a table and a recordset. You can have a table without a recordset. But you can't have a recordset wtihout a table.

In other words, your table will still be there with its fields. You just won't have any records.

A table is a database object You can delete a table object, but that would take different code. The code above is just for deleting one row of the table at a time.

Hank

[tgifgemini]

Thanks hkdani.
I've chosen an alternative way to acheive my objective and it worked perfectly:
here is what I did:

Private Sub CmdTableCopyUtility_Click()
Dim dbs As ADODB.Connection
Set dbs = New ADODB.Connection

'This program copies tables from Prodcution database PTSPROD to tables in Test database PTSTEST2:
'================================================================================================

dbs.Open "Provider=microsoft.jet.oledb.4.0;data source=\\515opsisdallf00008\shared\PYRLAPPS\PTStest2\PTS.mdb"
dbs.Execute "Delete * From TblRequestsAllPersonnel", , adExecuteNoRecords
dbs.Execute "Delete * From TblPersonnel", , adExecuteNoRecords
dbs.Execute "Delete * From TblDepartment", , adExecuteNoRecords
dbs.Execute "Delete * From TblProgrammerPeriodHours", , adExecuteNoRecords

dbs.Execute "Insert Into TblRequestsAllPersonnel Select * From TblRequestsAllPersonnel In '\\515opsisdallf00008\shared\PYRLAPPS\PTSPROD\PTS.mdb'", , adExecuteNoRecords
dbs.Execute "Insert Into TblPersonnel Select * From TblPersonnel In '\\515opsisdallf00008\shared\PYRLAPPS\PTSPROD\PTS.mdb'", , adExecuteNoRecords
dbs.Execute "Insert Into TblDepartment Select * From TblDepartment In '\\515opsisdallf00008\shared\PYRLAPPS\PTSPROD\PTS.mdb'", , adExecuteNoRecords
dbs.Execute "Insert Into  TblProgrammerPeriodHours Select * From TblProgrammerPeriodHours In '\\515opsisdallf00008\shared\PYRLAPPS\PTSPROD\PTS.mdb'", , adExecuteNoRecords

dbs.Close

End Sub

Thank you.
tgif

[hkdani]

Dim dbs As ADODB.Connection
Set dbs = New ADODB.Connection

It would make it read easier if you did the following:

Dim conPersonnel as Connection

Seems that dbs gives a misleading concept. As declared above, the person reading the code would have an easier time understanding that the process was using a connection property as well as Identifying in a general way what Table was being accessed.

With such a well written answer to your own problem, you should have solved it before you wrote the question:)

You don't really have to mention ADODB unless you are writing code in an application that uses Data Access Objects (DAO) as well as ActiveX Data objects at the same time.

Hank