copying a linked table and contents

I wish to copy the structure and the contents of a linked table (stored externally to access) into a new access table

The VB below sadly doesn't do the trick:

DoCmd.CopyObject , "NewTableName", acTable, "SourceTableName".

It only makes a mirror of the linked table. Anyone got any ideas?

Have you tried going to file>Get External Data>Import

This might work instead of using VBA. Good Luck

Thanks for your reply. I know that works, however that would involve user manual interaction, I wanted to automate as much as possible.

How about after your code adding an SQL statement : SELECT * FROM [tablename] AS [NewName]

In the end I programmed it in VB, using a loop

docmd.runsql to create table b4 the loop
dcount (to get exit point for the loop)
docmd.runsql insert data in the loop

its all automated now, but would have been nice to have been able to call a simple VB function to have done the job for me, if one exists

From Access Help:

DoCmd.TransferDatabase acImport, "Microsoft Access", _
"C:\DBS\NWSales.mdb", acReport, "NW Sales for April", _
"Corporate Sales for April"

Thanks, thats the simply way I was after.