By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,818 Members | 1,381 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,818 IT Pros & Developers. It's quick & easy.

How to Copy a Linked Table to another table

P: 225
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?
Expand|Select|Wrap|Line Numbers
  1. DoCmd.CopyObject , "tblNames", acTable, "Global Address List"
6 Days Ago #1
Share this Question
Share on Google+
5 Replies

Expert Mod 2.5K+
P: 3,278

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:

Expand|Select|Wrap|Line Numbers
  1. Call DoCmd.SelectObject(acTable, "Global Address List", True)
  2. 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.

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!
5 Days Ago #2

P: 225
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.
5 Days Ago #3

Expert Mod 2.5K+
P: 3,278
Was that code functional?
5 Days Ago #4

Expert 5K+
P: 8,628
  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:
    Expand|Select|Wrap|Line Numbers
    1. Public Sub ConvertLinkedToLocal(strLinkedTbl As String)
    2. Dim strConnect As String
    3. Dim strPath As String
    4. Dim strSourceTable As String
    6. strConnect = CurrentDb.TableDefs(strLinkedTbl).Connect      'Connect String
    8. If InStr(strConnect, "=") = 0 Then
    9.   MsgBox strLinkedTbl & " is not a Linked Table!", vbCritical, "Linked Table Error"
    10.     Exit Sub
    11. Else
    12.   strPath = Mid$(strConnect, InStr(strConnect, "=") + 1)        'Actual DB Path
    13.   strSourceTable = CurrentDb.TableDefs(strLinkedTbl).SourceTableName
    15.   DoCmd.DeleteObject acTable, strLinkedTbl
    16.   DoCmd.TransferDatabase acImport, "Microsoft Access", strPath, acTable, _
    17.                          strSourceTable, strLinkedTbl, False
    18. End If
    19. End Sub
  6. Sample Call to Sub-Routine (resulting in the Linked Table tblNames now becoming a Local Table named tblNames.
    Expand|Select|Wrap|Line Numbers
    1. 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.
5 Days Ago #5

Expert Mod 15k+
P: 31,470
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 :
Expand|Select|Wrap|Line Numbers
  1. 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.) :
Expand|Select|Wrap|Line Numbers
  1. INSERT INTO [tblLocalGAL]
  2.           ( [Field1]
  3.           , [Field2]
  4.           , ...
  5.           , [FieldN])
  6. SELECT      [Field1]
  7.           , [Field2]
  8.           , ...
  9.           , [FieldN]
  10. 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.

Expand|Select|Wrap|Line Numbers
  1. Private Const conMaxLocks As Long = 9500
Expand|Select|Wrap|Line Numbers
  1. 'RunSQLsAsTran() executes the contents of strSQLs as successive SQL commands
  2. '  separated by semi-colons.  The whole set is treated as a single transaction,
  3. '  so if any one of them fails then all are rolled back.
  4. '  If strMsg is not "" then it prompts the operator first who can proceed or halt.
  5. '  If [frmBusy] exists in your project it will be displayed while the SQLs run.
  6. '  Returns True if it completes successfully.
  7. ' 24/09/2015    Added handling of running out of resources.
  8. ' 25/05/2019    Added ignoring of empty strings.
  9. Public Function RunSQLsAsTran(ByVal strMsg As String, _
  10.                               ByRef strSQLs As String, _
  11.                               Optional dbVar As DAO.Database, _
  12.                               Optional wsVar As DAO.Workspace) As Boolean
  13.     Dim strMode As String, strWhere As String
  14.     Dim varSQL As Variant
  16.     If strMsg > "" Then
  17.         strMsg = Replace(strMsg, "%L", vbNewLine)
  18.         If MsgBox(Prompt:=strMsg, _
  19.                   Buttons:=vbOKCancel Or vbQuestion, _
  20.                   TITLE:="RunSQLsAsTran") = vbCancel Then Exit Function
  21.     End If
  22.     'Handle form frmBusy not being available when we open it.
  23.     On Error Resume Next
  24.     Call DoCmd.OpenForm(FormName:="frmBusy")
  25.     DoEvents
  26.     On Error GoTo 0
  27.     If dbVar Is Nothing Then Set dbVar = CurrentDb()
  28.     If wsVar Is Nothing Then Set wsVar = WorkspaceFromDB(dbVar)
  29.     'Allow more resources as this is a transaction.
  30.     Call DBEngine.SetOption(Option:=dbMaxLocksPerFile, Value:=10 * conMaxLocks)
  31.     Call wsVar.BeginTrans
  32.     On Error GoTo ErrorHandler
  33.     'Process all the separate SQL strings passed.
  34.     For Each varSQL In Split(strSQLs, ";")
  35.         If Trim(varSQL) > "" Then _
  36.             Call dbVar.Execute(Query:=varSQL, Options:=dbFailOnError)
  37.     Next varSQL
  38.     'If all OK so far then commit the transaction and requery this form.
  39.     Call wsVar.CommitTrans(dbForceOSFlush)
  40.     'Reset resources to default for normal operation.
  41.     Call DBEngine.SetOption(Option:=dbMaxLocksPerFile, Value:=conMaxLocks)
  42.     RunSQLsAsTran = True
  43.     If FormLoaded(strForm:="frmBusy") Then Call CloseMe(objMe:=Forms("frmBusy"))
  44.     Exit Function
  46. ErrorHandler:
  47.     'Handle message first before Err is corrupted.
  48.     strMsg = Replace("This update failed.%L%L" _
  49.                    & "Error %N%L%D%L%L" _
  50.                    & "Please report this problem to Support." _
  51.                    , "%N", Err.Number)
  52.     strMsg = Replace(strMsg, "%D", Err.Description)
  53.     strMsg = Replace(strMsg, "%L", vbNewLine)
  54.     Call wsVar.Rollback
  55.     If FormLoaded(strForm:="frmBusy") Then Call CloseMe(objMe:=Forms("frmBusy"))
  56.     'Reset resources to default for normal operation.
  57.     Call DBEngine.SetOption(Option:=dbMaxLocksPerFile, Value:=conMaxLocks)
  58.     Call MsgBox(Prompt:=strMsg, _
  59.                 Buttons:=vbOKOnly Or vbExclamation, _
  60.                 TITLE:="RunSQLsAsTran")
  61. End Function
Expand|Select|Wrap|Line Numbers
  1. 'FormLoaded determines whether or not a form object is loaded.
  2. Public Function FormLoaded(strForm As String) As Boolean
  3.     FormLoaded = False
  4.     On Error Resume Next
  5.     FormLoaded = (Forms(strForm).Name = strForm)
  6. End Function
3 Days Ago #6

Post your reply

Sign in to post your reply or Sign up for a free account.