Convert Linked Table to Local Table in Access 97 (1 Viewer)

[Ephestion]

Convert Linked Table to Local Table in Access 97
I have tried;

1. Copy and paste method but it seems i have to append to an existing table with the exact same fields.
2. Using VBA in 97 doesn't allow to make a local copy
3. All other user interfaces have been explored.

So how do we convert linked tables to local tables in a different database not necessarily the original linked database.

 

[isladogs]

Although Access97 was an excellent version, it is 26 years old!

My standard code to do this is:

Public Sub Link2Local(ByVal sTable As String)

'================================
'Purpose          : Converts linked table to local table
' Author           : Colin Riddington (isladogs)
' DateTime      : 06/10/2022
'================================

On Error GoTo Err_Handler

    DoCmd.CopyObject , "_" & sTable, acTable, sTable
    DoCmd.SelectObject acTable, "_" & sTable, True
    DoCmd.RunCommand acCmdConvertLinkedTableToLocal

Exit_Handler:
    Exit Sub
 
Err_Handler:
    If Err = 7874 Or Err = 2544 Or Err = 2046 Then Resume Next
    MsgBox "Error " & Err & " in Link2Local procedure : " & Err.Description
    Resume Exit_Handler

End Sub

However, I've just checked and acCmdConvertLinkedTableToLocal isn't available in A97
According to ChatGPT, that code was first available in Access 2000

So all I can suggest is that you do one of the following:
a) Run a make table query with all fields from your linked table

SELECT LinkedTable.* INTO LocalTable
FROM LinkedTable;

b) Delete the linked table and IMPORT a new copy

c) Decide that its finally time to update to a newer version which does support converting linked to local

 

[Ephestion]

DoCmd.RunCommand acCmdConvertLinkedTableToLocal - Confirming it's not an Access 97 built in command
VB6 works with Access 97 but I am not sure if it works with 2000+

 

[isladogs]

From memory, I believe that VB6 does work in A2000+ but for now I've given you 2 solutions that will work in A97

 

[Ephestion]

Rather than use the Query method which I am unsure on how to do I am physically copying the fields into new tables and then pasting the data manually. Thank you for your input I am doing what you said manually instead of through a query.

*Note using Access 2019/2016 will not read Access 97 databases. So I can't import it into a new database. Do you know which version will work with Access 97 and act as a bridge for Access 2019/16? Maybe I can update it to 2000 in some version that supports 97?

 

[isladogs]

Make table queries are a standard type of query that you can select.

A97 files can be opened by all versions up to 2007. All current versions can still open any database created in A2000 or later

 

[ebs17]

Rather than use the Query method which I am unsure on how to do I am physically copying the fields into new tables and then pasting the data manually.

A manual action is safer than a standard SQL statement, where the fields are safely transferred with correct data types and contents, in one piece?
What the make-table query does not do is transfer other content such as indexes and validation rules. But that would be the same question with the preferred manual mode.

A more complete approach is to transfer tables via the TransferDatabase method. With tables in relationships, one must be prepared for additional difficulties.

Simple manual method:
- Delete the linked tables in the frontend
- Invoke the import from Access-DB and select all tables

In this way, one also takes over the relationships at the same time.