andyb7901
asked on
MS Access - Copying linked tables
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
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
Use a make-table query.
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\SomeDatabas e.mdb' FROM YourTableName"
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\SomeDatabas
ASKER
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?
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
It lokks to me that Scott has given you the sql you need on the last line of his post.
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"
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"
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
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
ASKER
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Lovely job, works like a treat!