I knowing absolutely nothing about VB programming, and my knowledge of creating databases is limited. We have a database for my office that is two parts. A front end that has all of the forms, queries, etc; and a back end that I believe contains all of the actual data. It was done this was to make it run faster when it was getting too big. Whenever a used logins, there is a login script that copies the front end from the server to their workstation, and the back end remains on the server.

This week we installed a brand new server, and we had to change one of the top level directory names. Since then, our database has stopped working. I have gone in and modified the login script so it correctly points to the right location, and the script is working correctly. The problem is that there is still something within the database front end that points to the old server name. I figured if I could find it, I could just replace the old name (alpha) with the new name (walfad1) and it would start working, but I have no idea how to find it.

When I try to open the database, I get a blank Access screen, and the message: Windows cannot find the file C:\CFS\Case File System.mdb." I have verified that the file is on my local drive, and that the file is on the server. I have also tried to manually start the login form (the form that is supposed to run when you start the DB) and I get the attached error regarding the Jet Database not being able to open the file \\alpha\alston\case file system_be.mdb Since "alpha" is the name of the old server, I know this is one instance where I need to replace that information with the new server name; I just have no idea where to do it. I clicked the "debug" button to see if I could do it that way, but it did not show up in the form.

Is there another location I should be looking in, or is there a way to go through the database and replace all instances of this with the new server? Thanks a lot for any help!

I knowing absolutely nothing about VB programming, and my knowledge of creating databases is limited. We have a database for my office that is two parts. A front end that has all of the forms, queries, etc; and a back end that I believe contains all of the actual data. It was done this was to make it run faster when it was getting too big. Whenever a used logins, there is a login script that copies the front end from the server to their workstation, and the back end remains on the server.

This week we installed a brand new server, and we had to change one of the top level directory names. Since then, our database has stopped working. I have gone in and modified the login script so it correctly points to the right location, and the script is working correctly. The problem is that there is still something within the database front end that points to the old server name. I figured if I could find it, I could just replace the old name (alpha) with the new name (walfad1) and it would start working, but I have no idea how to find it.

When I try to open the database, I get a blank Access screen, and the message: Windows cannot find the file C:\CFS\Case File System.mdb." I have verified that the file is on my local drive, and that the file is on the server. I have also tried to manually start the login form (the form that is supposed to run when you start the DB) and I get the attached error regarding the Jet Database not being able to open the file \\alpha\alston\case file system_be.mdb Since "alpha" is the name of the old server, I know this is one instance where I need to replace that information with the new server name; I just have no idea where to do it. I clicked the "debug" button to see if I could do it that way, but it did not show up in the form.

Is there another location I should be looking in, or is there a way to go through the database and replace all instances of this with the new server? Thanks a lot for any help!


I knowing absolutely nothing about VB programming ...

Your in luck, Access uses VBA not VB.

Open the database that is having trouble finding the back end and use the Link table manager to relink to the new location.

Tools > Database Utilities > Link Table Manager


About splitting:


It was done this was to make it run faster when it was getting too big.

It really does not have a lot to do with size. This is the correct way to set up a database that will be used by multiple users. A database should never be opened by two users at the same time. Linking table to a back end, does not open the back end database in the same way so it can be shared.

Wow, that was it, thanks so much for the fast response!

Wow, that was it, thanks so much for the fast response!


You're welcome!

Glad I could assist. :)

This takes FOREVER for some reason. I have about 30 tables, and it takes about 10 minutes per table. So now instead of trying to do them all at once, I am doing one table at a time. Access 2000 waits about 5 minutes before popping open a box asking me where to select the new location. Is that normal?

I've usually found the Linked Table Manager to be slow when updating a large set of linked tables to change the back-end location. The following code works for me:

Public Sub BulkUpdateLinkSource( _
Optional ByVal sPath As String, _
Optional ByVal dPath As String)

Dim db As DAO.Database
Dim td As DAO.TableDef
Dim objFileDialog As FileDialog

Set objFileDialog = Application.FileDialog(msoFileDialogOpen)

With objFileDialog
If sPath = "" Then
.Title = "Old File Name"
.Show
If .SelectedItems.Count = 1 Then
sPath = .SelectedItems(1)
End If
End If
If dPath = "" Then
.Title = "New File Name"
.Show
If .SelectedItems.Count = 1 Then
dPath = .SelectedItems(1)
End If
End If
End With

If sPath = "" Or dPath = "" Then Exit Sub

Set db = CurrentDb
For Each td In db.TableDefs
If td.Attributes = dbAttachedTable Then
If InStr(1, td.Connect, sPath, vbTextCompare) > 0 Then
td.Connect = Replace(td.Connect, sPath, dPath)
td.RefreshLink
End If
End If
Next

db.TableDefs.Refresh
db.Close

Set db = Nothing

End Sub


Example:
BulkUpdateLinkSource "\\MyOldServer\MyDbFolder\MyDatabase.mdb", _
"\\MyNewServer\MyDbFolder\MyDatabase.mdb"

This takes FOREVER for some reason. I have about 30 tables, and it takes about 10 minutes per table. So now instead of trying to do them all at once, I am doing one table at a time. Access 2000 waits about 5 minutes before popping open a box asking me where to select the new location. Is that normal?

You should only need to relink the tables once on the "master" copy on the server. Then copy database(s) from the server to each or workstations to update the links.