Solved

MS Access - Copying linked tables

Posted on 2009-03-30
9
268 Views
Last Modified: 2013-11-28
Morning,
I have a linked table which I want to make a phsical copy of. I have a piece of code which takes a copy of the table, but instead of an actual physical copy, the database purely creates another link to the table and renames it. What I need is a for a backup of the table, not another link. Can anyone tell me ho wI would modify my piece of code, or suggest a new way of doing this?

Thanks
DoCmd.TransferDatabase acExport, "Microsoft Access", CurrentDb().Name, acTable, strTable, strTable & "_backup", False

Open in new window

0
Comment
Question by:andyb7901
  • 4
  • 3
  • 2
9 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 24016769
Use a make-table query.
0
 
LVL 84
ID: 24016782
SELECT INTO is often used to create backup tables. The only issue with this method is that the table into which you move your data cannot already exist, so you must check for that first:

Dim i As Integer
Dim dbs As DAO.Database

Set dbs = CurrentDB

For i = 0 to dbs.TableDefs.Count + 1
  If dbs.TableDefs(i).Name = "YourTempTableName" Then
    DoCmd.DeleteObject acTable, "YourTempTableName"
    Exit For
  End If
Next i

DoEvents
dbs.Execute "SELECT * INTO YourTempTableName FROM YourTableName"

Note that if you want to build this in another database, use the IN keyworkd:

dbs.Execute "SELECT * INTO YourTempTableName IN 'C:\SomeFolder\SomeDatabase.mdb' FROM YourTableName"
0
 

Author Comment

by:andyb7901
ID: 24016879
I think Peters suggestion may be the best one. What I need to do then is run a query in my other linked database. I can not link the make table queries into my database so I have to be able to run them some other way. I thought I would run them as below, but need to find a way to find the database path from one of my linked tables and then use this as my database file path?
I hope this makes some sense?

Dim db As DAO.Database

Dim dbname As String

 

dbname = "Linked_Table_Name".SourceTableName

Set db = OpenDatabase dbname

db.Execute ("Linked_Query1")

 

Set db = Nothing

Open in new window

0
 
LVL 77

Expert Comment

by:peter57r
ID: 24016986
It lokks to me that Scott has given you the sql you need on the last line of his post.
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 84
ID: 24017079
If ALL you're doing is making a copy of a linked table, then SELECT INTO is the simplest and most foolproof way to do this. There really is no need to work with backend queries and such; you can use the Connect property of a TableDef to get to your backend database:

Dim tdf As DAO.TableDef
Dim dbs As DAO.DAtabase
Dim sPath As String

Set dbs = Currentdb

For each tdf in dbs.TableDefs
  If Len(tdf.Connect) > 0 Then
    '/the Connect string starts with ;DATABASE= so we must remove that
    sPath = Left(tdf.Connect, Len(tdf.Connect) - 10)
  End If
Next tdf

sPath would then contain the fully qualified path to your backend database. Using this in the manner I described earlier, you can build a table in your Backend Database:

dbs.Execute "SELECT * INTO YourTempTableName IN '" & sPath & "' FROM YourLinkedTableName"


0
 
LVL 84
ID: 24017083
Sorry, forgot this:

After locating your connect string, you can then Exit the For loop:

For each tdf in dbs.TableDefs
  If Len(tdf.Connect) > 0 Then
    '/the Connect string starts with ;DATABASE= so we must remove that
    sPath = Left(tdf.Connect, Len(tdf.Connect) - 10)
    Exit For
  End If
Next tdf
0
 

Author Comment

by:andyb7901
ID: 24017136
I dont really want to hard code the path into the database name. My DB is is shared between multiple locations so I dont want anything hard coded. I have a remapping function which remaps all of my linked tables to another database as specified by the user. The function uses the Source Table Name to determine what the DB name is. I was hoping I could addopt a similar type of thing?
0
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 24017333
Right ... the loop I posted last gets the path to the database and uses that when running the SELECT INTO statement. If you have that value stored somewhere, you could use that also. However, directly reading the Connect string will guarantee that it is always current, regardless of your stored values.

I've combined my suggestions into a Function named MakeTableCopy. You'd run it like this:

MakeTableCopy "tblCustomers", "tblCustomers_Backup", False

This will create a table named "tblCustomers_Backup" in your backend, based on the table named "tblCustomers" (which can be in the FE as a local table or a linked table).

I made a few changes to my earlier code also - for example, I used Left to parse the path, but should have used Right.


Function MakeTableCopy(SourceTable As String, DestinationTable As String, Optional CreateLink As Boolean = False)
 

Dim i As Integer

Dim tdf As DAO.TableDef

Dim dbs As DAO.Database

Dim sPath As String
 

Set dbs = CurrentDb
 

For i = 0 To dbs.TableDefs.Count - 1

  If dbs.TableDefs(i).Name = DestinationTable Then

    DoCmd.DeleteObject acTable, DestinationTable

    Exit For

  End If

Next i
 

For Each tdf In dbs.TableDefs

  If Len(tdf.Connect) > 0 Then

    '/the Connect string starts with ;DATABASE= so we must remove that

    sPath = right(tdf.Connect, Len(tdf.Connect) - 10)

    Exit For

  End If

Next tdf

DoEvents
 

dbs.Execute "SELECT * INTO " & DestinationTable & " IN '" & sPath & "' FROM " & SourceTable

DoEvents
 

If CreateLink Then

  DoCmd.TransferDatabase acLink, "Microsoft Access", sPath, acTable, DestinationTable, DestinationTable

End If
 

End Function

Open in new window

0
 

Author Closing Comment

by:andyb7901
ID: 31564213
Lovely job, works like a treat!
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Numbers are exporting as text 5 27
MS Access 2010 Form Building 3 23
formattig excel from access 3 18
Syntax using Declare 3 16
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now