How to Copy a Linked Table to another table

[anoble1]

I am attempting to copy a linked Global Address List table to a local table with a click of a button.

I am just using some simple code to transfer like I have done in the past with regular tables but when it copies it create/converts the local table there into another linked table just like the "Global Address List" which is what I do not want.

How can I copy that GAL into a Local Table with a button?

DoCmd.CopyObject , "tblNames", acTable, "Global Address List"

[twinnyfo]

anoble1,

The simplest way to do this (which is not via VBA, which is how I know you want to do it), is simply right-click on your GAL table and select "Convert to local table." I haven't found a good way to do that with VBA. There is this:

Call DoCmd.SelectObject(acTable, "Global Address List", True)
Call RunCommand(acCmdConvertLinkedTableToLocal)

But I can't get it to actually execute with any of my linked tables. You can give it a try.

Why do you need this table locally? Usually you would want a GAL linked, so that you could receive updates.

==================
Update:
My research points to my failure as the fact that I have some relationships built on my table. If yo have no relationships to your linked table it may just work!

[anoble1]

I need to query that table and when I query that GAL it takes like over a minute. If I had a button that could be ran every few days to update that Local table from an end user than that would be nice.

[twinnyfo]

Was that code functional?

[ADezii]

1. This is simply in the event that twinnyfo's approach does not work.
2. I create a Sub-Routine for you that requires only the Name of a Linked Table and it will do the rest.
3. This Routine will:
   1. Examine the Linked Table to see if it is indeed Linked Table. This is done by checking the Connect Property of a Linked Table.
   2. Assuming it is a Linked Table, extract the Database Path from the Connect String.
   3. Retrieve the Source Table name from the Linked Table.
   4. DELETE the Linked Table. You are not DELETING the Table itself, but simply the Link to it.
   5. Import the Linked Table into the Current Database as a 'Local' Table.
4. I'm afraid that I may be making matters more complicated than they actually are, so I'll get to he heart of the matter.
5. Sub-Routine Definition:
   

   Public Sub ConvertLinkedToLocal(strLinkedTbl As String)
   Dim strConnect As String
   Dim strPath As String
   Dim strSourceTable As String
    
   strConnect = CurrentDb.TableDefs(strLinkedTbl).Connect      'Connect String
    
   If InStr(strConnect, "=") = 0 Then
     MsgBox strLinkedTbl & " is not a Linked Table!", vbCritical, "Linked Table Error"
       Exit Sub
   Else
     strPath = Mid$(strConnect, InStr(strConnect, "=") + 1)        'Actual DB Path
     strSourceTable = CurrentDb.TableDefs(strLinkedTbl).SourceTableName
    
     DoCmd.DeleteObject acTable, strLinkedTbl
     DoCmd.TransferDatabase acImport, "Microsoft Access", strPath, acTable, _
                            strSourceTable, strLinkedTbl, False
   End If
   End Sub

6. Sample Call to Sub-Routine (resulting in the Linked Table tblNames now becoming a Local Table named tblNames.
   

   Call ConvertLinkedToLocal("tblNames")

P.S. - I tested the Code using a Linked Table that it is involved in Multiple Relationships in the Back End and it worked fine. I am referring to the Order Details Table of the Northwind Sample Database.

[NeoPa]

Hi Andy.

If I understand you correctly you'd like an accurate copy of the data within the GAL, as at a certain point in time (so not dynamic as such), to be available in a local table which is refreshed separately from when it's used.

You've already discovered, as I just did to my surprise, that the Paste option to append to existing table is NOT available with DoCmd.CopyObject(). That rather sucks frankly. I was going to find it for you and point out its availability but that didn't happen!

So, the alternative I would suggest, for the populating of this table so that it matches your GAL at the time it's run, would be to use a few fairly simple SQL commands in sqequence.

Before you do any of that create yourself a local copy of the table using Copy/Paste manually from the GAL and set it as a local table. The data doesn't matter for now. For the purposes of clarity I will refer to that table as [tblLocalGAL]. The other (Proper / linked.) I'll call [GAL] for now but you know what it is on your system.

Now, the routine will basically clear the data from [tblLocalGAL] first and then immediately re-populate it from [GAL]. Thus the full set of data is an exact duplicate at that point in time.

NB. I've included some procedures below which I use for ensuring multiple sets of SQL are run together as a single transaction. You're free to use it but you may already have something similar.

Clearing [tblLocalGAL] is very basic SQL :

DELETE FROM [tblLocalGAL]

After that you create a SQL string to perform the APPEND query and Bob's your uncle. As [tblLocalGAL] has been duplicated by your earlier Copy/Paste from [GAL] it has the same fields available so the SQL will be of the following format (You may have to leave out some Fields from the APPEND if they're created automatically like AutoNumbers but I doubt that in this case.) :

INSERT INTO [tblLocalGAL]
          ( [Field1]
          , [Field2]
          , ...
          , [FieldN])
SELECT      [Field1]
          , [Field2]
          , ...
          , [FieldN]
FROM        [GAL]

Put both SQLs into a single string separated by a semi-colon (;) and pass that to RunSQLsAsTran() and you have a process that will keep your data up-to-date as far as you need depending on when, or how frequently, it's run.

Good luck.

Private Const conMaxLocks As Long = 9500

'RunSQLsAsTran() executes the contents of strSQLs as successive SQL commands
'  separated by semi-colons.  The whole set is treated as a single transaction,
'  so if any one of them fails then all are rolled back.
'  If strMsg is not "" then it prompts the operator first who can proceed or halt.
'  If [frmBusy] exists in your project it will be displayed while the SQLs run.
'  Returns True if it completes successfully.
' 24/09/2015    Added handling of running out of resources.
' 25/05/2019    Added ignoring of empty strings.
Public Function RunSQLsAsTran(ByVal strMsg As String, _
                              ByRef strSQLs As String, _
                              Optional dbVar As DAO.Database, _
                              Optional wsVar As DAO.Workspace) As Boolean
    Dim strMode As String, strWhere As String
    Dim varSQL As Variant
 
    If strMsg > "" Then
        strMsg = Replace(strMsg, "%L", vbNewLine)
        If MsgBox(Prompt:=strMsg, _
                  Buttons:=vbOKCancel Or vbQuestion, _
                  TITLE:="RunSQLsAsTran") = vbCancel Then Exit Function
    End If
    'Handle form frmBusy not being available when we open it.
    On Error Resume Next
    Call DoCmd.OpenForm(FormName:="frmBusy")
    DoEvents
    On Error GoTo 0
    If dbVar Is Nothing Then Set dbVar = CurrentDb()
    If wsVar Is Nothing Then Set wsVar = WorkspaceFromDB(dbVar)
    'Allow more resources as this is a transaction.
    Call DBEngine.SetOption(Option:=dbMaxLocksPerFile, Value:=10 * conMaxLocks)
    Call wsVar.BeginTrans
    On Error GoTo ErrorHandler
    'Process all the separate SQL strings passed.
    For Each varSQL In Split(strSQLs, ";")
        If Trim(varSQL) > "" Then _
            Call dbVar.Execute(Query:=varSQL, Options:=dbFailOnError)
    Next varSQL
    'If all OK so far then commit the transaction and requery this form.
    Call wsVar.CommitTrans(dbForceOSFlush)
    'Reset resources to default for normal operation.
    Call DBEngine.SetOption(Option:=dbMaxLocksPerFile, Value:=conMaxLocks)
    RunSQLsAsTran = True
    If FormLoaded(strForm:="frmBusy") Then Call CloseMe(objMe:=Forms("frmBusy"))
    Exit Function
 
ErrorHandler:
    'Handle message first before Err is corrupted.
    strMsg = Replace("This update failed.%L%L" _
                   & "Error %N%L%D%L%L" _
                   & "Please report this problem to Support." _
                   , "%N", Err.Number)
    strMsg = Replace(strMsg, "%D", Err.Description)
    strMsg = Replace(strMsg, "%L", vbNewLine)
    Call wsVar.Rollback
    If FormLoaded(strForm:="frmBusy") Then Call CloseMe(objMe:=Forms("frmBusy"))
    'Reset resources to default for normal operation.
    Call DBEngine.SetOption(Option:=dbMaxLocksPerFile, Value:=conMaxLocks)
    Call MsgBox(Prompt:=strMsg, _
                Buttons:=vbOKOnly Or vbExclamation, _
                TITLE:="RunSQLsAsTran")
End Function

'FormLoaded determines whether or not a form object is loaded.
Public Function FormLoaded(strForm As String) As Boolean
    FormLoaded = False
    On Error Resume Next
    FormLoaded = (Forms(strForm).Name = strForm)
End Function