Moving back end to a different location

[RichO]

There is probably an easy answer to this but I can't find an exact answer searching.

I have an Access 2000 database with the back end stored in a separate location. If I want to move the back end data file to a different location on the computer, how do I change the link (file path)?

The only way I can figure out is to delete all of the old links and relink them from their new location.

Isn't there somewhere that you can just change the path name to the entire back end in one easy step?


Thanks for any help.

 

[llkhoutx]

You've got it right

delete all of the old links and relink them from their new location.

Another method is to reset to path in each linked table and refresh the link.

I've never timed the two methods to see which is faster. I presently delete links and relink about 250 tables at a time; but only in new installations. It's prettly quick; less that 60 seconds.

 

[RichO]

Is there any way to mass delete the links? The standard Ctrl-A or Ctrl-click doesn't work for multi-selecting. There are over 100 tables in this DB.

Thanks for the reply.

 

[llkhoutx]

I encountered a major problem deleting links, in that my application allows users to select which back-end to link to. Quite often, the users clicks the relink button multiple times resulting in tables linked multiple times with a suffix to each table name.

To get around this, I delete all linked tables from the front-end before relinking all back-end tables. Multiple back-ends may require relinking different sets of tables at different times, so my delete link method won't work for all.

To "gang" delete all links, put the following code where appropriate:

dim db as dao.database
dim tbl as dao.tabledef
set db=currentdb
for each tbl in db.tabledefs
if tbl.connect <> "" then tbl.connect=""
next
db.close
set db=nothing

 

[FLabrecque]

Have you tried the TOOLS\Database Utilities\Linked Table Manager?

 

[allan57]

I use the following method to change my linked table destination (Access 97):

Dim dbs As Database

Dim tdf As TableDef

On Error Resume Next

Set dbs = CurrentDb

dbs.TableDefs.Refresh

For Each tdf In dbs.TableDefs

With tdf

If Len(.Connect) > 0 Then

'Current Location
If .Connect = ";DATABASE=N:\Access_Tables\Sensitising_Dry_Chem_Store\Sensitising_Dry_Chem_Store_be.mdb" Then

'New Location
.Connect = ";DATABASE=N:\Access_Development\ACCESS_Program_Development_Testing_AREA\Dry_Chem_Store\Sensitising_Dry_Chem_Store_be.mdb"

.RefreshLink

End If

End If

End With

Next

Set tdf = Nothing

dbs.Close
Set dbs = Nothing

 

[llkhoutx]

You originally asked:

Isn't there somewhere that you can just change the path name to the entire back end in one easy step?

The answer to that question is no. Your last post is one way to do it.

 

[Aurak]

Hi,

The tabledef’s connect property is ready only once it is added to the tabledefs collection. The posted code doesn’t work. I use the following code to re-link broken table references. I use relative path and I store the linked mdb in a folder called tables. Please note that you may not use mdb filenames with spaces. I don’t know why, it is not documented. If you have spaces in the linked file, you get an ISAM error which is not very helpful. My main database file with the forms, queries, etc called Purchasing DB.mdb. I created a switchboard form witch is loaded every time the file is opened.

Dim nCnt As Integer
Dim sConnection As String
Dim oTable As New DAO.TableDef
Dim oNewTable As New DAO.TableDef

sConnection = ";DATABASE=" & Replace(CurrentDb.Name, "Purchasing DB.mdb", "Tables\aq.mdb")
For Each oTable In CurrentDb.TableDefs
With oTable
If .Connect <> sConnection And .Connect <> "" Then
Set oNewTable = CurrentDb.CreateTableDef(.Name, dbAttachSavePWD, .Name, sConnection)
CurrentDb.TableDefs.Delete oTable.Name
CurrentDb.TableDefs.Append oNewTable
End If
End With
Next

As you can see it is necessary to delete the old reference and create a new one with different connect property. I use Access XP but I think it is not version dependent. I hope the code is helpful. If you have questions, please let me know.

Best regards,

Jeno

 

[ski24-7]

This gets the job done on Access 2003. Keeps current file names but changes path to same as main database, handy if you link to more than one file. File must exist and contain the table or .RefreshLink fails with .Connect value reverting back to old.


Dim iFileSep As Integer
Dim sConnection As String
Dim sOldConnectPath As String
Dim sConnectFileName As String
Dim oTable As New DAO.TableDef

sConnection = ";DATABASE=" & Left(CurrentDb.Name, InStrRev(CurrentDb.Name, "\"))
For Each oTable In CurrentDb.TableDefs

With oTable
If .Connect <> "" And Left(.Connect, 5) <> "dBase" Then
iFileSep = InStrRev(.Connect, "\")
sOldConnectPath = Left(.Connect, iFileSep)
sConnectFileName = Mid(.Connect, iFileSep + 1)
If sOldConnectPath <> sConnection Then
.Connect = sConnection & sConnectFileName
On Error Resume Next
.RefreshLink
If err.Number = 3024 Then
MsgBox "File not found for Table " & .Name & vbCrLf & _
sConnection & sConnectFileName
ElseIf err.Number = 3011 Then
MsgBox "Table " & .Name & " not found in this file " & vbCrLf & _
sConnection & sConnectFileName
Else
MsgBox "Error number = " & err.Number
End If
End If
End If
End With
Next
CurrentDb.TableDefs.Refresh

End Sub