Replacing text in my database

[sbrobin]

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!

 

[HiTechCoach]

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.

 

[sbrobin]

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

 

[HiTechCoach]

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

You're welcome!

Glad I could assist.

 

[sbrobin]

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?

 

[ByteMyzer]

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:

[COLOR="Navy"]Public Sub[/COLOR] BulkUpdateLinkSource( _
    [COLOR="navy"]Optional ByVal[/COLOR] sPath [COLOR="navy"]As String[/COLOR], _
    [COLOR="navy"]Optional ByVal[/COLOR] dPath [COLOR="navy"]As String[/COLOR])

    [COLOR="navy"]Dim[/COLOR] db [COLOR="navy"]As[/COLOR] DAO.Database
    [COLOR="navy"]Dim[/COLOR] td [COLOR="navy"]As[/COLOR] DAO.TableDef
    [COLOR="navy"]Dim[/COLOR] objFileDialog [COLOR="navy"]As[/COLOR] FileDialog
    
    [COLOR="navy"]Set[/COLOR] objFileDialog = Application.FileDialog(msoFileDialogOpen)
    
    [COLOR="navy"]With[/COLOR] objFileDialog
        [COLOR="navy"]If[/COLOR] sPath = "" [COLOR="navy"]Then[/COLOR]
            .Title = "Old File Name"
            .Show
            [COLOR="navy"]If[/COLOR] .SelectedItems.Count = 1 [COLOR="navy"]Then[/COLOR]
                sPath = .SelectedItems(1)
            [COLOR="navy"]End If
        End If
        If[/COLOR] dPath = "" [COLOR="navy"]Then[/COLOR]
            .Title = "New File Name"
            .Show
            [COLOR="navy"]If[/COLOR] .SelectedItems.Count = 1 [COLOR="navy"]Then[/COLOR]
                dPath = .SelectedItems(1)
            [COLOR="navy"]End If
        End If
    End With
    
    If[/COLOR] sPath = "" [COLOR="navy"]Or[/COLOR] dPath = "" [COLOR="navy"]Then Exit Sub
    
    Set[/COLOR] db = CurrentDb
    [COLOR="navy"]For Each[/COLOR] td [COLOR="navy"]In[/COLOR] db.TableDefs
        [COLOR="navy"]If[/COLOR] td.Attributes = dbAttachedTable [COLOR="navy"]Then
            If[/COLOR] InStr(1, td.Connect, sPath, vbTextCompare) > 0 [COLOR="navy"]Then[/COLOR]
                td.Connect = Replace(td.Connect, sPath, dPath)
                td.RefreshLink
            [COLOR="navy"]End If
        End If
    Next[/COLOR]
    
    db.TableDefs.Refresh
    db.Close
    
    [COLOR="navy"]Set[/COLOR] db = [COLOR="navy"]Nothing

End Sub[/COLOR]

Example:

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

 

[HiTechCoach]

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.