How do I make a copy of a linked table

[Guest]

When using a FILECOPY command to make a copy of the .dbf file, I receive a
RunTime Error '70': permission denied. This is a linked table.

 

[strive4peace]

Hi Herb

to make a copy of a linked table:

1. make a query based on the table and select all the fields

2. then change the query into a Make-Table Query

from the menu --> Query, Make Table... and specify tablename to make

3. then, Run (!) the query!


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*

 

[Guest]

I created the query as you instructed, then used the following code
docmd.openquery "queryname"; it worked fine. Thank you!

 

[strive4peace]

you're welcome, Herb happy to help

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*

 

[Guest]

Crystal,
Is it possible to pass parameters to a query from VB code. I have many
tables which need copies made and I'd like to use the same query as above
changing the main table name and the new table name by using parameters to
the query!

Thanks in advance.
Herb

 

[RoyVidar]

Crystal,
Is it possible to pass parameters to a query from VB code. I have
many tables which need copies made and I'd like to use the same
query as above changing the main table name and the new table name
by using parameters to the query!

Thanks in advance.
Herb

The basic syntax, is

SELECT *
INTO NewTable
FROM OldTable

So, if you just fetch a list over the tables you wish to work with,
you should be able to do this through dynamic SQL, if interested.
Here are some ideas (type = 6, linked to Access, type = 4 SQL server, I
think)

Dim rs As adodb.Recordset

Set rs = CurrentProject.Connection.Execute( _
"SELECT name FROM msysobjects " & _
"WHERE type = 6 and left(name,3) <> 'mys'", , adCmdText)

Do While Not rs.EOF
CurrentProject.Connection.Execute _
"SELECT * INTO [" & rs.Fields(0).value & "new] " & _
"FROM [" & rs.Fields(0).value & "]"
rs.MoveNext
Loop

 

[RoyVidar]

"WHERE type = 6 and left(name,3) <> 'mys'", , adCmdText)

Should have been

"WHERE type = 6 and left(name,3) <> 'msy'", , adCmdText)

but, the last check isn't necessary, and it would suffice with

"WHERE type = 6", , adCmdText)