It is essential that the data tables retain their primary key fields.
With no primary key fields, data sources used in queries & forms based on several tables become read only
I never use ODBC to link tables so can't advise in that respect
However, to convert and retain PK fields, do ONE of the following:
1. Select all tables you want to convert, right click & run ‘Convert to local table’ - that's it! Couldn't be easier
Make sure no local tables are selected or the option will be disabled
2. Loop through all linked tables using this code:
Code:
Public Sub Link2Local(ByVal sTable As String)
On Error GoTo Err_Handler
Dim sTmpTable As String
sTmpTable = "mytmptable"
'Make tmp table
DoCmd.RunSQL "select * into mytmptable from [" & sTable & "]" 'Wrapped in [] just in case of spaces / special characters in name
'Delete original table
DoCmd.DeleteObject acTable, sTable
'Rename tmp to oldtable
DoCmd.Rename sTable, acTable, sTmpTable
Exit_Handler:
Exit Sub
Err_Handler:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume Exit_Handler
End Sub