Is it possible to store the location of the backend database in a table?

[Seth Schrock]

My idea is to have it so that I would have a table that has the file location saved in it. I would then have the database check to see if that value is blank when the database is first opened. If it is blank, then a little form would appear that allows the location to be entered and then the database can open like normal. Is this possible?

The reason I need this is that I plan on (hopefully) needing to install this at multiple companies, not just multiple users of the same company. Therefore, I won't know where the backend will be stored in each case and I want an easy way of telling the front end where that is.

[zmbd]

Short answer, yes it is possible to store the path. I would recomend using the UNC path withever possible.

[Seth Schrock]

Okay. So I know how to get the value put into the database. How do I then tell Access that the location of the linked tables is X?

[zmbd]

Ah, that would be via the link manager.
I have a pretty simple one... let me find it.

You can also get super fancy that looks at application launch location, revisions, and so forth.

[TheSmileyCoder]

I designed a hazard log system once. Depending on the project they would then choose which backend to link to during startup (A form handles this) Try looking through our insights section. I am quite sure we have a article relating to relinking backend tables.

[zmbd]

Not to leave you hanging... sorry, had an urgent event in the lab... stupid Gremlins!
-
Anyway, I have a form_splash that opens with the database. It has the code to check for updates and backends... a lot of code.

The basics; here's the main loop behind one of the simpler relinking codes I use (one of my oldest too):

:Just a little setup:
- So, first I open a record set on tbl_databaseconfig in the front end. The reason I open the record set is because I store/retrieve other information such as current open count, current version, and the like.
- I then pull the path location from the table just like any other record and put it in a string variable. The "zjpath" is where that string is stored for use in the re-link.
- I then use the DIR() function to check for the file. If it's not there then I raise an error and let the error trap send me the information.
- If the file is found then I start the relinking. Now some will try to open a recordset on one of the linked tables and move within it, trapping any errors and then running the re-linker code of needed. However, this database has like 5 tables in the backend and only a three in the front end so I just refresh the links as it takes like 20 seconds to do and I often have messages in the splash screen about things so this gives a little delay for the user to read :)
- Now... I could store the information about which tables are linked and pull that as a record set; however, the database has that information in the table definitions so I loop thru that and look to see if the table is attached and if so I reattach/refresh the link

 '(.... preamble code omitted)
'Log any files that didn't connect
On Error GoTo ZJ_ConnectError
'
    For Each ztdf In zdb.TableDefs
            ''Set some user feed back to the splash form
        zjsize = zjsize + 1
        zj_msgtxt = "Loading...(" & Format((zjsize / zdb.TableDefs.Count) * 100, "Standard") & "%) "
        'zj_msgtxt = zj_msgtxt & ".."
        Form_frm_Startup.Label6.Caption = zj_msgtxt
        'Form_frm_Startup.Label6.Caption = "Loading..." & zj_msgtxt
        Form_frm_Startup.Repaint
            '
            'Get the table information and if attached set and refreash the link
        If ztdf.Attributes = dbAttachedTable Then
        ztdf.Connect = ";DATABASE=" & zjpath & ";TABLE=" & ztdf.SourceTableName
        ztdf.RefreshLink
                '
                'on an error the email message is being created
                'to send me so we can kill the error
                'and move on to the next table.
                'once the resume next is executed.
        If Err.Number > 0 Then Err.Clear
        End If
    Next
'(..... more code omitted)

On Error GoTo ZJ_ConnectError is the code that sends me an email that someone couldn't connect to the backend or to one of the tables it also posts the issue to the splash form.

(Edit) as TheSmileyCoder suggested: Relinking ODBC Tables using VBA

[NeoPa]

Smiley is quite correct of course. See Relinking Tables using VBA. It's fundamentally about setting the .Connect property then calling .RefreshLink().

I'll include the post here for ease of use, but the whole thread is linked for any that want the fuller picture :

NeoPa:
I have one that works specifically for Access database linked tables. It may be worth including that and making it less specific :

'ReLink() Updates links of all tables that currently link to strDBName to point
'to strDBName in the strLinkDest folder (if specified, otherwise the same folder
'as the current database).
Public Sub ReLink(ByVal strDBName As String, _
                  Optional ByVal strFolder As String = "")
    Dim intParam As Integer, intErrNo As Integer
    Dim strOldLink As String, strOldName As String
    Dim strNewLink As String, strMsg As String
    Dim varLinkAry As Variant
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
 
    Set db = CurrentDb()
    If strFolder = "" Then strFolder = CurrentProject.Path
    If Right(strFolder, 1) = "\" Then _
        strFolder = Left(strFolder, Len(strFolder) - 1)
    strNewLink = strFolder & "\" & strDBName
    For Each tdf In db.TableDefs
        With tdf
            If .Attributes And dbAttachedTable Then
                varLinkAry = Split(.Connect, ";")
                For intParam = LBound(varLinkAry) To UBound(varLinkAry)
                    If Left(varLinkAry(intParam), 9) = "DATABASE=" Then Exit For
                Next intParam
                strOldLink = Mid(varLinkAry(intParam), 10)
                If strOldLink <> strNewLink Then
                    strOldName = Split(strOldLink, _
                                       "\")(UBound(Split(strOldLink, "\")))
                    If strOldName = strDBName Then
                        varLinkAry(intParam) = "DATABASE=" & strNewLink
                        .Connect = Join(varLinkAry, ";")
                        On Error Resume Next
                        Call .RefreshLink
                        intErrNo = Err.Number
                        On Error GoTo 0
                        Select Case intErrNo
                        Case 3011, 3024, 3044, 3055, 7874
                            varLinkAry(intParam) = "DATABASE=" & strOldLink
                            .Connect = Join(varLinkAry, ";")
                            strMsg = "Database file (%F) not found.%L" & _
                                     "Unable to ReLink [%T]."
                            strMsg = Replace(strMsg, "%F", strNewLink)
                            strMsg = Replace(strMsg, "%L", vbCrLf)
                            strMsg = Replace(strMsg, "%T", .Name)
                            Call MsgBox(Prompt:=strMsg, _
                                        Buttons:=vbExclamation Or vbOKOnly, _
                                        Title:="ReLink")
                            If intErrNo = 3024 _
                            Or intErrNo = 3044 _
                            Or intErrNo = 3055 Then Exit For
                        Case Else
                            strMsg = "[%T] relinked to ""%F"""
                            strMsg = Replace(strMsg, "%T", .Name)
                            strMsg = Replace(strMsg, "%F", strNewLink)
                            Debug.Print strMsg
                        End Select
                    End If
                End If
            End If
        End With
    Next tdf
End Sub

The reason it's much longer is that it has to handle errors, and recover from them. The meat of it is very similar. Maybe we could look at a version of the ODBC one that handles the various failure scenarios too. That would be a very useful article to link to I expect.

Good thinking Seth. This can certainly help the flexibility of your projects. I use something very similar with some of my clients. Code determines which branch they're running from then it checks to see if that is the currently linked BE. If not, it switches all the relevant tables to the correct one and, Bob's your uncle :-)

[Seth Schrock]

I'm starting to work on it and I noticed that there is a significant difference in the amount of code between what Z has posted and what was posted in the link provided. Post #1 is smaller, Post #2 is huge, and I don't think that Post #4 actually resets the BE location. My question is, what is the difference between these options? Most of this code I have no clue about so I can't figure what the differences mean to me and my situation. What I plan on doing is to split my database once I have finished making my test database, so the BE will be an Access file (not sure if this matters).

[zmbd]

Seth,
Now the code I posted was/is from a much larger set of instructions as noted on line1.

-As for the code in the link insight:
Post1: is a "refresh" only in that as you noted it doesn't reset the path as in my post nor as in the others.

Post2: is a set and refresh with a lot of error checking.

The other code talks about enhancements to speed/optimization and really doesn't apply unless you start having issues.

[Seth Schrock]

Okay. I'll start with the one that has error checking. That will help me catch my mistakes :)

[Seth Schrock]

Got a problem. I have copied and pasted the code from the link into a module on a test database. Here is the code:

Option Compare Database
Option Explicit
 
Public Sub ReLink(ByVal strDBName As String, _
                  Optional ByVal strFolder As String = "")
    Dim intParam As Integer, intErrNo As Integer
    Dim strOldLink As String, strOldName As String
    Dim strNewLink As String, strMsg As String
    Dim varLinkAry As Variant
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
 
    Set db = CurrentDb
    If strFolder = "" Then strFolder = CurrentProject.Path
    If Right(strFolder, 1) = "\" Then _
        strFolder = Left(strFolder, Len(strFolder) - 1)
    strNewLink = strFolder & "\" & strDBName
    For Each tdf In db.TableDefs
        With tdf
            If .Attributes And dbAttachedTable Then
                varLinkAry = Split(.Connect, ";")
                For intParam = LBound(varLinkAry) To UBound(varLinkAry)
                    If Left(varLinkAry(intParam), 9) = "DATABASE=" Then Exit For
                Next intParam
                strOldLink = Mid(varLinkAry(intParam), 10)
                If strOldLink <> strNewLink Then
                    strOldName = Split(strOldLink, _
                                       "\")(UBound(Split(strOldLink, "\")))
                    If strOldName = strDBName Then
                        varLinkAry(intParam) = "DATABASE=" & strNewLink
                        .Connect = Join(varLinkAry, ";")
                        On Error Resume Next
                        Call .RefreshLink
                        intErrNo = Err.Number
                        On Error GoTo 0
                        Select Case intErrNo
                        Case 3011, 3024, 3044, 3055, 7874
                            varLinkAry(intParam) = "DATABASE=" & strOldLink
                            .Connect = Join(varLinkAry, ";")
                            strMsg = "Database file (%F) not found.%L" & _
                                     "Unable to ReLink [%T]."
                            strMsg = Replace(strMsg, "%F", strNewLink)
                            strMsg = Replace(strMsg, "%L", vbCrLf)
                            strMsg = Replace(strMsg, "%T", .Name)
                            Call MsgBox(Prompt:=strMsg, _
                                        Buttons:=vbExclamation Or vbOKOnly, _
                                        Title:="ReLink")
                            If intErrNo = 3024 _
                            Or intErrNo = 3044 _
                            Or intErrNo = 3055 Then Exit For
                        Case Else
                            strMsg = "[%T] relinked to ""%F"""
                            strMsg = Replace(strMsg, "%T", .Name)
                            strMsg = Replace(strMsg, "%F", strNewLink)
                            Debug.Print strMsg
                        End Select
                    End If
                End If
            End If
        End With
    Next tdf
End Sub
 
 

Line 20 is evidently coming back wrong because it then jumps to it End If statement. I just ran it again and got the following values for the criteria:

.Attributes = 2
dbAttachedTable = 1073741824

As I said before, this stuff is totally new and I have no clue where to start looking. Hopefully this information will help you help me. Is Line 20 missing what .Attributes and dbAttachedTable are supposed to equal? What are the values supposed to be?

PS: After stepping through the code, I found that after several loops through the code (not sure how many), it did end up going through the True portion of the If Then statement. However, the tables didn't relink to the BE in the new location.

[NeoPa]

Line #20 is within a For loop Seth (See line #18). This code is indented as proscribed and not randomly. That means that any indented lines are within a maningful block. Line #20 is within a With block from line #19, a For block from line #18 and a Sub block from line #4. Hence it's indented 12 spaces.

The reason I mention that is because every time it proceeds through that block of code the value of .Attributes will refer to those of a different table from your TableDefs collection. Thus, it is important to understand which table is being processed at the time.

The .Attributes value of 2 is not recognised. By this I mean none of the constants in the Help page has a value of 2, so I don't know what it means. I suspect when you see the table it's referring to though, it will be clear why it was skipped in that loop.

[Seth Schrock]

As you guessed, once I figured out the table names (using ?tdf.Name in the immediate window) I found out why it wasn't doing anything with them: they were MSys... tables. Duh...

And I also figured out why my tables weren't relinking. Since I wasn't passing a file path to strFolder, it was setting itself to the CurrentProject.Path location and that wasn't where I had put the BE. So I tried passing a file path to it like this

Private Sub Form_Load()
Dim strDBName As String
Dim strNewFolder As String
 
 
strDBName = "IntakeMktgDB_ver2_be.accdb"
strNewFolder = "C:\Users\Seth Schrock\Documents\Database Test Files\IntakeMktgDB_ver2\NewFolder\"
ReLink (strDBName, strNewFolder)
MsgBox ("Relinking Complete")
 
End Sub

but I get the following error:
Compile error:

Expected: =
with line 8 selected. I guess I'm not totally sure how to pass a file path to the ReLink function.

I did end up moving the FE and BE databases into the same folder and ran the code (without passing a value to strFolder) and it worked perfectly. So my only problem now is passing a value to strFolder.

[NeoPa]

Bad timing here Seth. I'm just off out. I'll be back Sunday and pick up from there. It's all straightforward. No worries, I just ran out of time for now :-(

[zmbd]

Line 8 place the key word CALL before the subroutine name sometimes that helps when using a different sub.

[Seth Schrock]

It works okay if I just have

ReLink (strDBName)

I could leave it like that, but I would be limited to having the BE in the same folder as the FE so I would really like to figure out how to pass the folder path to the ReLink function. I have had this issue before when trying to pass multiple variable to a function. It is just something that I have never figured out.

Just curious, why the = "" at the end of line 5 in post #11? Is that just to make the value not null but blank if nothing is passed to it>

[NeoPa]

Just popping in Seth. We'll need to see the exact values of the two parameters at the time passed and the full error message. Using Call makes sense here as Z says.

Seth:
Just curious, why the = "" at the end of line 5 in post #11? Is that just to make the value not null but blank if nothing is passed to it?

Yes. That syntax specifies a default value.

[zmbd]

Neopa: perhaps Lines 6 and 7?
I hate the spaces in the file path name.

Seth,
As a side line to the thread; however it relates to lines 6 and 7 of the code:
IMHO: it is best practice to only use alphanumerics and the underscore in any file, directory, variable, or other name. No spaces, no dashes, etc... This comes from an oldschool perspective and the fact that parsers will occatinally choke on a space or special character.

[NeoPa]

Indeed Z. Line #6 is clearly not fully specified and may cause issues unless you know where the current default folder is.

[Seth Schrock]

I can't change the \Seth Schrock\ portion of the file path as that is my user name. I can do something about the other spaces though. I guess I could also move the database out of my documents and that would remove the user name from the file path.

NeoPa, lines 6 & 7 are what I'm passing to the function. I hard coded them just to take that variable out of the equation (no pun intended). I have another plan to pass the value of a textbox to the variables to pass them onto the function. Also, the error message that I get is in post #13. The compile error: Expected =.

[Seth Schrock]

@NeoPa, do I need the full file path of the stored in the strDBName? I thought it was just looking for the name. It did work however in the two locations that I tried when I left strNewFolder out when I called the ReLink function.

[NeoPa]

Z:
IMHO: it is best practice to only use alphanumerics and the underscore in any file, directory, variable, or other name. No spaces, no dashes, etc... This comes from an oldschool perspective and the fact that parsers will occatinally choke on a space or special character.

Indeed so, but nowadays that's proving less and less practical. Generally, when creating new folders for use, I would recommend avoiding the use of spaces, but we all need to ensure that code handles spaces consistently in folder and file names now that so many system folders have them embedded within. It's essentially a fact of life now :-(

Seth:
Also, the error message that I get is in post #13. The compile error: Expected =.

You should have followed Z's advice in post #15 Seth. It should read either of (but I prefer and recommend #1) :

1. Call ReLink(strDBName, strNewFolder)
2. ReLink strDBName, strNewFolder

Seth:
do I need the full file path of the stored in the strDBName?

No. I was in a bit of a rush at the time and forgot how it was supposed to work (blush).

strDBName is the string that matches the current BE filename, as well as the new BE filename. The only part that changes is the folder. Frankly, line #7 looks fine as long as you have such a folder available on your system. What you are reporting seems to be saying that you don't. If you still believe you do then run the following code for me and then post the result back in here :

ChDir "C:\Users\Seth Schrock\Documents\Database Test Files\IntakeMktgDB_ver2\NewFolder"
?CurDir "C:"

As a matter of interest too, it would be good to see the .Connect string of (any) one of your tables linked to "IntakeMktgDB_ver2_be.accdb".

strT="???"
?CurrentDb().TableDefs(strT).Connect

Where "???" reflects the name of the table chosen.

[Seth Schrock]

Got it to work. I used your preferred method. When I did the ChDir() with the New Folder at the end it didn't, it said that the file path was invalid. I took the New Folder off the end and it worked. I then renamed New Folder to BE_File. I was then able to do the ChDir() with the BE_File on the end and it worked. So here is that I have and it does work perfectly.

Private Sub Form_Load()
Dim strDBName As String
Dim strNewFolder As String
 
strDBName = "IntakeMktgDB_ver2_be.accdb"
strNewFolder = "C:\Users\Seth Schrock\Documents\Database Test Files\IntakeMktgDB_ver2\BE_File\"
Call ReLink(strDBName, strNewFolder)
 
MsgBox ("Relinking Complete")
 
End Sub

The ReLink code is the same as posted before. Thanks to everyone for your help.

[NeoPa]

Seth:
When I did the ChDir() with the New Folder at the end it didn't, it said that the file path was invalid.

That, indeed, was my suspicion. Clearly you found the problem that I expected to be found. With that understood, getting the already working code to work for you was straightforward (as I would expect).

In such a situation I expect the last request, for the current BE .Connect string, is no longer required.

PS. I will need to change the Best Answer chosen. I appreciate the sentiment, but post #22 doesn't reflect anything too helpful to anyone else coming along looking for answers. I'll look for the post that most appropriately reflects that and set it for you :-)

[Seth Schrock]

It was a tough decision since the main code came from a different thread. I guess I didn't think about the post referencing the other thread. Thanks NeoPa.

[NeoPa]

Indeed. No criticism goes your way for that Seth. Not at all. I'm probably just in a better position to see how best to make it work as I have Mod powers to edit existing (older) posts, as well as a bit more experience to recognise what people most need.