BACKEND database, Path not found ... (1 Viewer)

[simonwar]

Hi,

I am working on a database, at home, that is networked at the office.

When I open the Database, I get the error message, cannot locate BACKEND.mdb, including the network path used at work - seems obvious.

I have a copy of BACKEND.mdb stored locally, at home, but how do I redirect the database FRONT END, (Forms etc), to access the newly located BACKEND.mdb for development at home?

Thanks, Simon.

 

[pbaldy]

You want the Linked Table Manager.

 

[simonwar]

What's happening is I am not getting any access to the FRONTEND. I can open the BACKEND and see the tables, but when I go into DATABASE UTILITIES > LINKED TABLE MANAGER, it states "no linked tables"?

When I try and open the FRONTEND, even when using the "Shift-Key", I get the error messages that my database is looking for the BACKEND.mdb located at a location on my network at work, and I have no option to "Find", or "Relink" etc, I have to click "OK", and subsequently nothing opens, even though there is 3Mb of Access Forms and Reports etc within it?

Any ideas.

Thanks, Simon.

 

[Dezirous]

Hi Simon,

Well there could be one thing as I think....... you should check your front end tables. Link Manager creates link between front-end and back-end, its mean your front-end table linked with back-end. You can confirm it when you look at Table icon of front-end, there will be an arrow before table icon. Just right click on white-space in table section and select Link table Manager. Browse back-end.mdb and select the table you want. it will create new table with same name. Just try............

 

[boblarson]

What's happening is I am not getting any access to the FRONTEND. I can open the BACKEND and see the tables, but when I go into DATABASE UTILITIES > LINKED TABLE MANAGER, it states "no linked tables"?

When I try and open the FRONTEND, even when using the "Shift-Key", I get the error messages that my database is looking for the BACKEND.mdb located at a location on my network at work, and I have no option to "Find", or "Relink" etc, I have to click "OK", and subsequently nothing opens, even though there is 3Mb of Access Forms and Reports etc within it?

Any ideas.

Thanks, Simon.

1. You can't open the Linked Table Manager on a database that has no linked tables (and the backend would qualify as that).

2. Once you've opened your FE, you say you can select the Linked Table Manager but nothing shows up there?

If #2 is true then perhaps you have a corrupt FE.

 

[NigelShaw]

Hi,

i had the very same problem. i had a back end and then moved the FE as it was part of a distributed app i was making. the BE was not in the right place so it failed and the linked table manager did not work as i didnt have any linked tables........... my db was NOT corrupt

i did this to get around m,y problem

Sub RefreshTableLinks()

    'On Error GoTo err_ RefreshTableLinks

    Dim CurDB As DAO.Database, tdfLinked As DAO.TableDef
    Dim TBDef As DAO.TableDef, InstallPath As String
    Dim DBPath As String
    Dim DBLink As String
    
    'Set as current path as the BE is in the same folder
    DBPath = Application.CurrentProject.Path
    'Set back end file name.
    'This could also be a full path to a different location "C:\MyFolder\MyBELocation\MyBE.accdb"
    DBLink = "\MyBE.accdb"

    Set CurDB = CurrentDb
    For Each TBDef In CurDB.TableDefs
            If Len(TBDef.Connect) > 0 Then
            'This could also be set to a different location using the example                  above DBLink. in which case you would use
            'TBDef.Connect = ";DATABASE=" & DBLink
            TBDef.Connect = ";DATABASE=" & DBPath & DBLink

            TBDef.RefreshLink
        End If
    Next TBDef

exit_ RefreshTableLinks:
    Set CurDB = Nothing
    Set tdfLinked = Nothing
    Set TBDef = Nothing
    Exit Sub

err_ RefreshTableLinks:
    MsgBox Err.Number & ": " & Err.Description, vbOKOnly + vbcrf, "An Error has occured in refresh tables"
    Resume exit_ RefreshTableLinks

End Sub

put this the the AutoExec of your app so it runs before anything else. it refreshes the links to your BE tables



HTH



nigel

 

[simonwar]

Thanks for all of the responses.

To confirm.

After the error message as per the attachment, nothing opens at all, so I can't access an Autoexec - as far as my knowledge will take me.

So, first question:

How is this done, is the Autoexec accessible outside Access, or am I missing something, I am a relative newbie to Access.

Secondly,

If I COPY and PASTE the code into the Autoexec file, once located, is it the first piece of code, if there is other code in there or does that matter.

Also, am i only replacing the line

"C:\MyFolder\MyBELocation\MyBE.accdb"
DBLink = "\MyBE.accdb"

with my BACKEND Path.

Also, whats the .accdb, my backend is called .mdb, does this matter?

Appreciated, Simon.

 

[NigelShaw]

Hi Simon,

AutoExec is an Access Macro NOT vba module. in the macro, you use the "RunCode" option and enter the function in the space provided. in this case, the function is RefreshTableLinks

here is your updated code

Public Function RefreshTableLinks()
 
    'On Error GoTo err_ RefreshTableLinks
 
    Dim CurDB As DAO.Database, tdfLinked As DAO.TableDef
    Dim TBDef As DAO.TableDef, InstallPath As String
    Dim DBPath As String
    Dim DBLink As String
 
    'Set as current path as the BE is in the same folder
    DBPath = Application.CurrentProject.Path
    'Set back end file name.
        DBLink = "[FONT=Arial]C:\Documents and Settings\simonwar\My Documents\Linde HTD\$$$ DowntimeDatabase\Data\BACKEND.mdb[/FONT]"
 
    Set CurDB = CurrentDb
    For Each TBDef In CurDB.TableDefs
            If Len(TBDef.Connect) > 0 Then
 
            TBDef.Connect = ";DATABASE=" & DBLink
            TBDef.RefreshLink
        End If
    Next TBDef
 
exit_ RefreshTableLinks:
    Set CurDB = Nothing
    Set tdfLinked = Nothing
    Set TBDef = Nothing
    Exit Function
 
err_ RefreshTableLinks:
    MsgBox Err.Number & ": " & Err.Description, vbOKOnly + vbcrf, "An Error has occured in refresh tables"
    Resume exit_ RefreshTableLinks
 
End Function

your attachement shows the error you received which is looking for a backend reference on "O:\" drive NOT C:\ drive that your back end dictates. im guessing that this is your problem.

the code should now work on the condition your AutoExec is looking for the correct function.


HTH

Nigel

 

[boblarson]

So Simon -

So, you still can't get in if you keep your shift key down even when clicking the error message? It would seem you would need to re-enable the ShiftBypass then. You can get a database which will let you re-enable your bypass key.

 

[Galaxiom]

Perhaps there is some security information in a linked table. The database could be configured such that without being able to access this data, nothing will work.

One possibility to consider is locating the backend on a mapped drive rather than a UNC path. This allows the backend to be located anywhere and mapped to a drive letter without having to change the database for different locations.

Use the SUBST command on your home computer to map the drive letter to the backend folder.

Using a mapped drive also has the advantage of being able to easily move the backend database to another physical drive or machine and change the mapping without having to redistribute the frontend to all users.

This drive letter can be assigned in Group Policy to change the location across the entire domain.

 

[simonwar]

Thanks all,

Once I had Enabled the "Shift-Key", I could use the Linked Table Manager.
The database opens fine now, when Shift Key is ON.

***

Also, there seems to be several Modules containing code, without delving too deeply at this stage, where can I get some tutorial support on Code, why its needed, useful code for General Databases, etc.

I would like to do some background reading on this?

Thanks, Simon.