Problem with DoCmd.CopyObject for copying SQL Server linked tables in Access 2003

  • Thread starter Thread starter Paul Le Sueur
  • Start date Start date
P

Paul Le Sueur

Dear All,

I assume this is the correct newsgroup for this posting,
apologies if I have got it wrong!

I have a problem using the VBA DoCmd.CopyObject command in
Access 2003. This problem has only come about since using
Access 2003, it was OK in 2000...

I have three SQL Server databases:
* DB1
* DB2
* DB3

each having the same data tables in them
* Table1
* Table2
* Table3

I have linked these tables (9 in the example, three in each
database * three databases) to an Access 2000 database with
a naming convention like this:
* dbo_Table1DB1
* dbo_Table1DB2
* dbo_Table1DB3
* dbo_Table2DB1
* dbo_Table2DB2
* dbo_Table2DB3
* dbo_Table3DB1
* dbo_Table3DB2
* dbo_Table3DB3

When a user is using the access database, they can choose the
database they want to work with from a combo box, and the
appropriate table links are copied to working names ie:
* WTable1
* WTable2
* WTable3
within access, where the existing forms, queries and reports
can work with them.

The problem is that since I started using the database with
Access 2003, instead of merely copying the table links as
before, the entire SQL Server table is copied into Access as
a real access table. As my actual implementation is dealing
with some fairly big SQL Server tables, it takes a very long
time to copy the tables and my Access database grows to about
100 MB.

My VBA syntax for copying tables is as follows:
'delete the working databases
DoCmd.DeleteObject acTable, "WTable1"
DoCmd.DeleteObject acTable, "WTable2"
DoCmd.DeleteObject acTable, "WTable3"

'copy the chosen databases objects to the working
'databases
DoCmd.CopyObject "", "WTable1", acTable, _
"dbo_Table1" & CboDatabase.Text
DoCmd.CopyObject "", "WTable2", acTable, _
"dbo_Table2" & CboDatabase.Text
DoCmd.CopyObject "", "WTable3", acTable, _
"dbo_Table3" & CboDatabase.Text

Any ideas?

Thanks in anticipation!

Paul.
 
Back
Top