Is there a simple way to copy tables with ADO? 1

[N2Life]

I have a routine that copies all the tables of a database into a pre-existing backup database named with the day of the month. With DAO, the following routine copies all the tables in a database to the backup database (specified by WhereFile):

For Each tbl In CurrentDb.TableDefs
If tbl.Attributes = 0 Then
DoCmd.CopyObject WhereFile, tbl.Name, acTable, tbl.Name
End If
Next

(I am writing programs in Access 2000, using Windows XP.)

Is there a way to do the same thing with ADO, with similar simplicity? I consulted faq705-3859 which apparently accomplishes the task, but I'm surprised that what DAO can do with such brevity, ADO must do with an entire chapter of code. Thank you.

 

[cmmrfrds]

If you are going to continue to use Access, then why change to ADO? Are you using another type of application i.e. Web based?

An SQL Statement such as "Select into" or "Insert into" would also work but you would need to follow up and define the indexes with the Alter Statement or ADOX.

 

[N2Life]

Since ADO is the new standard, I suspect that one day Microsoft will stop supporting DAO. Just trying to stay up with the latest technology.

 

[N2Life]

By the way, the indexes don't matter. The routine just copies all the tables into a backup database named for the day of the month. The purpose is simply to protect the data. A safe copy of the entire program is maintained somewhere else.

 

[cmmrfrds]

You can use an SQL Statement to join across different databases by setting up a ADO connection. Example.

Function ExternalTables2()

Dim cn As New Connection, cn2 As New Connection
Dim rs As New Recordset, rs2 As New Recordset
Dim connString As String, connString2 As String
Dim sql1 As String, sql2 As String

connString = "provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\AEmptyDir\employee.mdb;" & _
"Persist Security Info=False"
cn.ConnectionString = connString
cn.Open connString
connString2 = "provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\AEmptyDir\employee2.mdb;" & _
"Persist Security Info=False"

cn.ConnectionString = connString2
cn.Open connString2

sql1 = "select * from cn...employees as A inner join cn2...employees as B" & _
"ON A.employeeID = B.employeeID"

rs.Open sql1, CurrentProject.Connection, adOpenStatic, adLockOptimistic

End Function

 

[cmmrfrds]

The simplest is with an SQL Statement referencing the full path of the other mdb.


INSERT INTO C:\AEmptyDir\employee.mdb.employees
SELECT * From employees

 

[N2Life]

Thank you cmmrfrds for putting me on the right track. Everything works now.

I've never submitted a tip before. As an old hand at this, do you think the code might be useful to others? I have routines that 1) create a text file to hold the current date, 2) create the necessary backup databases to hold the tables, and 3) read the text file to see if the date stored there is current date or not; if it isn't, makes backups automatically (using ADO!), then updates the text file to current date. The first 2 routines are run only once. This approach provides extra insurance, especially if you are using a file server and put the backups (as well as a full copy of the original database) on your C:drive.

Again, thanks.
N2Life

 

[cmmrfrds]

Sure, a tip on automating backups is valuable. Even if somebody already has a routine they may be interested in new ideas and ways of doing it. Thank you.