How to copy linked table to a different database as a local table?

[Robin]

Hi

I'm fairly new to VBA and am getting nowhere. Performing the task manually
provides options for linked, local, structure only etc., but within VBA
(using DoCmd.CopyObject) all I seem able to do is create another linked
table.

Is there some other way to do this?

Thanks

Robin

 

[David Lloyd]

Robin:

One alternative is to use a Make Table query and use the IN clause to
specify a different database. For example:

SELECT MyLinkedTable.* INTO MyNewTable IN 'C:\MyDestinationDB.mdb'
FROM MyLinkedTable;


--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


Hi

I'm fairly new to VBA and am getting nowhere. Performing the task manually
provides options for linked, local, structure only etc., but within VBA
(using DoCmd.CopyObject) all I seem able to do is create another linked
table.

Is there some other way to do this?

Thanks

Robin

 

[PC Datasheet]

A linked table is not actually a table but merely a pointer to a table in
the backend. So you actually need to copy the table in the backend. The
simple way is to open the database you want to copy to and go to File - Get
External Data to import the table. You can programatically do it from that
same database using the TransferDatabase function. Check it out in the Help
file.

 

[Robin]

Thank you David - solved the problem perfectly

I've seen elsewhere that this is not appropriate if index integrity is an
issue, but since I didn't have any indexes, perfect! Thanks again.

Robin

 

[Robin]

Thanks for the quick response. I was trying to do this programmatically.

Elsewhere it was suggested I use make table queries - I tried this and it
works perfectly.

I tried using TransferDatabase but couldn't get it to do what I wanted (my
lack of knowledge me thinks), so I need to spend some more time playing with
that function.

Regards,

Robin