VB Code to copy linked table to local

[JD]

I have an Access 2007 database with a linked ODBC table that changes on a
daily basis. I want to automate a function where the linked table is copied
and pasted as a local table. I tried the following command:

DoCmd.CopyObject , stNewName, acTable, stTblName

It worked, and named the new table correctly (based on an earlier command
that assigns the table name with the current date as "stNewName"), but it
stayed as a linked table - which means tomorrow, today's data will be lost.
Is there a parameter of the CopyObject command that will make the new table
local, or is there another command I should be using?

 

[kc-mass]

Why not use a MakeTable query to create your file.

Something like :

SELECT tblLinkedTable.* INTO tblCopyOfLinkedFile
FROM tblLinkedTable;

If you use VBA can can easily embed the date in the target filename so
each day's will be retained and ID'd.

Regards

Kevin

 

[Daniel Pineault]

Look over the following posts as they provide a couple different approaches:

http://www.pcreview.co.uk/forums/thread-2174021.php
http://www.access-programmers.co.uk/forums/showthread.php?t=111900
http://bytes.com/topic/access/answers/188847-automatically-created-local-copy-linked-tables
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.

 

[JD]

Thanks for the help. It seems to me that based on the suggestions I've read,
the easiest solution will be to simply copy the table and paste it to a new
local table.

 

[Stefan Hoffmann]

hi JD,

It worked, and named the new table correctly (based on an earlier command
that assigns the table name with the current date as "stNewName"), but it
stayed as a linked table - which means tomorrow, today's data will be lost.
Is there a parameter of the CopyObject command that will make the new table
local, or is there another command I should be using?

See

http://msdn.microsoft.com/en-us/library/bb214131.aspx

DoCmd.TransferDatabase acImport, ....


It's faster then using SELECT * INTO localTable FROM linkedTable.


mfG
--> stefan <--