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_databasecon fig 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.