Location of back end database

Gkirkup · Oct 8, 2009

I am starting to work with a split database, and need to change the location of the back end. Where in the front end is this location stored?

Robert

Mr. B · Oct 8, 2009

Robert,

Use the "Linked Table Manager" to specify the new location of your linked tables.

namliam · Oct 8, 2009

In the menu:
Tools > Database utilities > Linked table manager

You can also do it via code to automate the work but this is the easy answer.

Gkirkup · Oct 8, 2009

Thanks. I have changed the name of the back end. I used the Link Table Manager to link all tables, but on startup I get the error message 'Cannot find file...' and then the OLD name of the back end. So somewhere it is not getting changed. I was hoping to find where the back end name is stored, and change it.

Robert

boblarson · Oct 8, 2009

Gkirkup said:
Thanks. I have changed the name of the back end. I used the Link Table Manager to link all tables, but on startup I get the error message 'Cannot find file...' and then the OLD name of the back end. So somewhere it is not getting changed. I was hoping to find where the back end name is stored, and change it.

Just hold the shift key down when opening the database and then go immediately and do the linked table manager. You can't change the values in the system table directly anyway.

namliam · Oct 9, 2009

boblarson said:
Just hold the shift key down when opening the database and then go immediately and do the linked table manager. You can't change the values in the system table directly anyway.

But you can change the linked table source...

I have a form with 2 boxes on it... along with this code which runs on open

Code:

Private Sub Form_Open(Cancel As Integer)
    Dim tbl As TableDef
    Dim x As Long, MaxX As Long
    Dim tblDB As String
    Me.Visible = False
    tblDB = myFolder & "Database\Your_be.mdb"
    If Dir(tblDB) = "" Then
        MsgBox "No table database has been found, " & vbCr & vbCr & _
                "This application will not work, so its beeing closed", vbCritical
        Application.Quit
    End If
                       
    MaxX = 1 ' first count all attached tables
    For Each tbl In CurrentDb.TableDefs()
        If tbl.Attributes = dbAttachedTable Then MaxX = MaxX + 1
    Next tbl
    x = 1 ' Now update them
    For Each tbl In CurrentDb.TableDefs()
        If tbl.Attributes = dbAttachedTable Then
            tblDB = myFolder & Mid(tbl.Connect, InStr(1, tbl.Connect, "Database\"))
            If tbl.Connect <> ";Database=" & tblDB Then
                Me.Visible = True
                Me.Repaint
                tbl.Connect = ";Database=" & tblDB
                tbl.RefreshLink
            End If
            x = x + 1
        End If
        Me.Fill.Width = x / MaxX * Me.FillTo.Width
    Next tbl
    If Me.Visible Then
        Me.lblWait.Caption = "Done relinking ... "
        Me.Repaint
        MaxX = Timer + 2
        Do While Timer <= MaxX
        Loop
    End If
    

    DoCmd.OpenForm "frmMain"
    DoCmd.Close acForm, Me.Name
    
End Sub

Function myFolder()
    myFolder = Left(CurrentDb.Name, InStrRev(CurrentDb.Name, "\"))
End Function

I am sure you can addapt this to your needs

DCrake · Oct 9, 2009

Nailman,

Just for cosmetic reasons you have mispelt the word being in your message box (beeing).

No doubt users will pick up on this and comment on it.

David

namliam · Oct 9, 2009

LOL The message box is there for 100% full proofing, the BE will always be at my designated spot

but thanks for pointing it out.

wiklendt · Nov 7, 2009

namliam said:

But you can change the linked table source...

I have a form with 2 boxes on it... along with this code which runs on open

Code:

Private Sub Form_Open(Cancel As Integer)
    Dim tbl As TableDef
    Dim x As Long, MaxX As Long
    Dim tblDB As String
    Me.Visible = False
    tblDB = myFolder & "Database\Your_be.mdb"
    If Dir(tblDB) = "" Then
        MsgBox "No table database has been found, " & vbCr & vbCr & _
                "This application will not work, so its beeing closed", vbCritical
        Application.Quit
    End If
                       
    MaxX = 1 ' first count all attached tables
    For Each tbl In CurrentDb.TableDefs()
        If tbl.Attributes = dbAttachedTable Then MaxX = MaxX + 1
    Next tbl
    x = 1 ' Now update them
    For Each tbl In CurrentDb.TableDefs()
        If tbl.Attributes = dbAttachedTable Then
            tblDB = myFolder & Mid(tbl.Connect, InStr(1, tbl.Connect, "Database\"))
            If tbl.Connect <> ";Database=" & tblDB Then
                Me.Visible = True
                Me.Repaint
                tbl.Connect = ";Database=" & tblDB
                tbl.RefreshLink
            End If
            x = x + 1
        End If
        Me.Fill.Width = x / MaxX * Me.FillTo.Width
    Next tbl
    If Me.Visible Then
        Me.lblWait.Caption = "Done relinking ... "
        Me.Repaint
        MaxX = Timer + 2
        Do While Timer <= MaxX
        Loop
    End If
    

    DoCmd.OpenForm "frmMain"
    DoCmd.Close acForm, Me.Name
    
End Sub

Function myFolder()
    myFolder = Left(CurrentDb.Name, InStrRev(CurrentDb.Name, "\"))
End Function

I am sure you can addapt this to your needs

this code works great, however, to make it work after adjusting for my database, i had to rem out the

line. i.e., MY full code version of this is (with the remmed out line in red):

Code:

Private Sub Form_Open(Cancel As Integer)
    Dim tbl As TableDef
    Dim x As Long, MaxX As Long
    Dim tblDB As String
    Me.Visible = False
    tblDB = myFolder & "\ORDERS_ReferenceLabs - be.mdb"
    If Dir(tblDB) = "" Then
        'tblDB = \\wm-icpmr\...
        tblDB = myFolder & "\test\ORDERS_ReferenceLabs - be.mdb"

'        MsgBox "No table database has been found, " & vbCr & vbCr & _
'                "This application will not work, so its beeing closed", vbCritical
'        Application.Quit
    End If
                     
    MaxX = 1 ' first count all attached tables
    For Each tbl In CurrentDb.TableDefs()
        If tbl.Attributes = dbAttachedTable Then MaxX = MaxX + 1
    Next tbl
    x = 1 ' Now update them
    For Each tbl In CurrentDb.TableDefs()
        If tbl.Attributes = dbAttachedTable Then
[COLOR=Red]'            tblDB = myFolder & Mid(tbl.Connect, InStr(1, tbl.Connect, "\"))[/COLOR]
            If tbl.Connect <> ";Database=" & tblDB Then
                Me.Visible = True
                Me.Repaint
                tbl.Connect = ";Database=" & tblDB
                tbl.RefreshLink
            End If
            x = x + 1
        End If
        Me.Fill.Width = x / MaxX * Me.FillTo.Width
    Next tbl
    If Me.Visible Then
        Me.lblWait.Caption = "Done... "
        Me.Repaint
        MaxX = Timer + 2
        Do While Timer <= MaxX
        Loop
    End If
    
    DoCmd.OpenForm "frmLogin"
    DoCmd.Close acForm, Me.Name
    
End Sub

and it works fine without it (with it, it would repeat the path in one string and say that path did not exist, e.g., \\c\users\agnieszka\databases\ordering\users\agnieszka\databases\ordering - or to that erfect. once i remmed out the red line, it worked fine to link the appropriate tables...

i haven't done anything bad, have i? it doesn't SEEM to do anything bad...i can still create and delete records ...

namliam · Nov 9, 2009

tblDB = myFolder & Mid(tbl.Connect, InStr(1, tbl.Connect, "Database\"))

The database\ that database is important, I stick all my backends into a subfolder from my front end, guess what that folder is called?? Database, right...

So effectively this searching my full path for the last folder name "Database\" and cuts out the Database\Backend.MDB to be used in the new path.
By actively fetching the BE database from the existing string the code is able to dynamicaly be used in any database without modification and can use multiple backends if needed.

The fact that I do a dir for one specific one, makes it a bit less flexible... hey it is a work in progress... Sue me

With a fixed and single backend you can take out that line no problem.

gemma-the-husky · Nov 9, 2009

just for the op

the linked database is not a single item

each non-local table in the database is a linked table - each table is individually linked - so although often there is a single "backend" - there doesn't have to be.

eg - if you wish to include data from a propietary account system, you may link some tables by a odbc connection - such as Sage, or SQLServer connection

parley · Nov 11, 2009

namliam said:
But you can change the linked table source...

I have a form with 2 boxes on it... along with this code which runs on open

I'm curious how the two boxes are set up. Are they list boxes?

When I open the form in Form View it goes to Design View and is inaccessble. Meaning, I can't edit or close the form.

This is how I've edited the code so far. I'm using MS Access 2007.

I did follow your pattern of putting the the be in the .\database directory.

--EDIT-- I added the file attachment for help. Thanks.

Code:

Option Compare Database
Private Sub Form_Open(Cancel As Integer)
    Dim tbl As TableDef
    Dim x As Long, MaxX As Long
    Dim tblDB As String
    Me.Visible = True
    tblDB = myFolder & "Database\FR Leads_be.mdb"
    If Dir(tblDB) = "" Then
        MsgBox "No table database has been found, " & vbCr & vbCr & _
                "This application will not work, so its being closed", vbCritical
        Application.Quit
    End If
 
    MaxX = 1 ' first count all attached tables
    For Each tbl In CurrentDb.TableDefs()
        If tbl.Attributes = dbAttachedTable Then MaxX = MaxX + 1
    Next tbl
    x = 1 ' Now update them
    For Each tbl In CurrentDb.TableDefs()
        If tbl.Attributes = dbAttachedTable Then
            tblDB = myFolder & Mid(tbl.Connect, InStr(1, tbl.Connect, "Database\"))
            If tbl.Connect <> ";Database=" & tblDB Then
                Me.Visible = True
                Me.Repaint
                tbl.Connect = ";Database=" & tblDB
                tbl.RefreshLink
            End If
            x = x + 1
        End If
        Me.Fill.Width = x / MaxX * Me.FillTo.Width
    Next tbl
    If Me.Visible Then
        Me.lblWait.Caption = "Done relinking ... "
        Me.Repaint
        MaxX = Timer + 2
        Do While Timer <= MaxX
        Loop
    End If
 
    DoCmd.OpenForm "frm_city"
    DoCmd.Close acForm, Me.Name
 
End Sub
Function myFolder()
    myFolder = Left(CurrentDb.Name, InStrRev(CurrentDb.Name, "\"))
End Function

pitt_ph · Jan 17, 2013

Alright, so commenting on (and asking questions about) a thread that was last answered about four years ago may not be the smartest idea, but here goes --

I use Access 2007 primarily, so most of the split database relinking codes I've seen don't seem to apply to me (because they *appear* to be built for Access 2000/2003 mdb files). I can't code well myself, so I might be interpreting these modules incorrectly.

I noticed that there was the following lines in the code (lines 34-40), and I wasn't sure what their express purpose was. Could anyone give some clarification about this?

Code:

        Me.Fill.Width = x / MaxX * Me.FillTo.Width
    Next tbl
    If Me.Visible Then
        Me.lblWait.Caption = "Done... "
        Me.Repaint
        MaxX = Timer + 2
        Do While Timer <= MaxX

On a different note -- if there are two databases (both using homemade, starting-from-blank-form switchboards) with the same linked-table back end, this code would go *specifically* on each switchboard's Form_Open event, correct?

Thank you so much for your feedback in advance! I'm sorry if these questions are terribly unclear -- so if you need any clarification, please let me know.

pitt_ph · Jan 17, 2013

pitt_ph said:
I noticed that there was the following lines in the code (lines 34-40), and I wasn't sure what their express purpose was. Could anyone give some clarification about this?

Code:

Me.Fill.Width = x / MaxX * Me.FillTo.Width Next tbl If Me.Visible Then Me.lblWait.Caption = "Done... " Me.Repaint MaxX = Timer + 2 Do While Timer <= MaxX

As an update, I attempted to run this code on the Form_Open event of the switchboard (as well as a data entry form). In both scenarios, I obtained the following error (highlighting line 34 of the code, specifically the bolded part below):

Compile error: Method or data member not found
Me.Fill.Width = x / MaxX * Me.FillTo.Width

Would anyone have an idea of why the FillTo method may not exist? (As far as my internet searches have shown, the Fill method appears to exist.)

Thanks in advance!

wiklendt · May 3, 2013

pitt_ph said:
As an update, I attempted to run this code on the Form_Open event of the switchboard (as well as a data entry form). In both scenarios, I obtained the following error (highlighting line 34 of the code, specifically the bolded part below):

Would anyone have an idea of why the FillTo method may not exist? (As far as my internet searches have shown, the Fill method appears to exist.)

Thanks in advance!

FillTo is not a method - "Fill" and "FillTo" are just (edit: ) rectangles (/edit) called "Fill" and "FillTo". Their purpose is to generate a progress bar (handy if the linking takes a long time).

Location of back end database

Gkirkup

Registered User.

Mr. B

"Doctor Access"

namliam

The Mailman - AWF VIP

Gkirkup

Registered User.

boblarson

Smeghead

namliam

The Mailman - AWF VIP

DCrake

Remembered

namliam

The Mailman - AWF VIP

wiklendt

i recommend chocolate

namliam

The Mailman - AWF VIP

gemma-the-husky

Super Moderator

parley

REALTOR / DBA

Attachments

pitt_ph

Registered User.

pitt_ph

Registered User.

wiklendt

i recommend chocolate

Similar threads

Users who are viewing this thread