Auto relink to current folder

[gschimek]

I've searched the forums for days and I can't find exactly what I need to know, even though this is a very common topic, so if it's already been answered, I apologize.

My problem is simple. I have a database that tracks clients for me. I have a front end and back end in the same folder. Under normal circumstances, the static links that Access uses are fine. I just link from the front end to the back end and all is well.

But some other users of the database will need to track multiple people's clients. So instead of putting the database in c:\database, I may need to have a copy in c:\database, c:\database2, c:\database3, etc.

So what I need is a (hopefully) simple way to get Access to look for the backend in the current directory, no matter what that directory might be. It may be on a network drive, or the c: drive, or any of a dozen various subdirectories. And the users who will be using the database may be very computer illiterate, so I don't want to have any user intervention required.

And so that I'm not any more confused than I already am by any code you might offer up, the name of the database frontend is tracking.mdb and the backend is tracking_be.mdb

Any ideas? Thanks in advance. You'll make my week if you can figure this out with me.

 

[RuralGuy]

Maybe this link will help. Reconnect Attached tables on Start-up

 

[gschimek]

Perfect! That was easy. I knew there had to be some fairly simple answer.

Thanks.

 

[RuralGuy]

You're welcome. Glad I could help.

 

[gschimek]

I'm having trouble getting this code to work on Access 2000? I get a VBA error and all I can do is select Halt. Should it work on 2000? The database is in Access 2000 format already.

 

[boblarson]

Instead of just telling us that you get a VBA error, how about telling us what error it actually is? We might be able to help as there are thousands of possible errors that can occur in VBA.

 

[gschimek]

Sorry, you're right. But the error was not exactly descriptive. I get a box that says "Action Failed" at the top, and inside the box it has the following:

Macro Name: Autoexec
Condition: True
Action Name: RunCode
Arguments:Reconnect()

And my option is just to click "Halt." This is probably obvious, but Reconnect() is the code called from my Autoexec macro, and the code resides in a function called fncReconnect.

Incidentally, if I remove the call to that code from my autoexec, I get the same error with some other code that updates the backend. So I don't think it's the code itself, but some issue with Access 2000.

 

[boblarson]

Actually, it could be a MISSING reference problem. That's one of the problems with darn macros is that they don't give you much to go on.

 

[gschimek]

OK, so I opened up the database and ran the Autoexec macro manually. The VBA Editor opened and I got the error "Compile Error: Can't find project or library" and the word "Mid" is highlighted. (In Red below)

Function Reconnect()
'Dim db As Database, source As String, path As String
Dim dbsource As String, i As Integer, j As Integer

Set db = DBEngine.Workspaces(0).Databases(0)
'*************************************************************
'*                     RECOGNIZE THE PATH                    *
'*************************************************************

For i = Len(db.Name) To 1 Step -1
    If Mid(db.Name, i, 1) = Chr(92) Then
        path = Mid(db.Name, 1, i)
        'MsgBox (path)
        Exit For
    End If
Next
'*************************************************************
'*              CHANGE THE PATH   AND   CONNECT  AGAIN       *
'*************************************************************

For i = 0 To db.TableDefs.Count - 1
    If db.TableDefs(i).Connect <> " " Then
        source = [COLOR="Red"]Mid[/COLOR](db.TableDefs(i).Connect, 11)
        'Debug.Print source
        For j = Len(source) To 1 Step -1
            If Mid(source, j, 1) = Chr(92) Then
               dbsource = Mid(source, j + 1, Len(source))
               source = Mid(source, 1, j)
                   If source <> path Then
                        db.TableDefs(i).Connect = ";Database=" + path + dbsource
                        db.TableDefs(i).RefreshLink
                        'Debug.Print ";Database=" + path + dbsource
                    End If
                Exit For
            End If
         Next
    End If
Next
End Function

 

[boblarson]

That would be a reference problem. And, it sounds like the one where it won't show as MISSING when you go to Tools > References and look at the list. If you do that and it doesn't show it, then click the browse button, go to C:\Windows\System32 and look for Comctl32.ocx and click on it and then click the open button.

Then, close your database and reopen it.

 

[gschimek]

Thanks boblarson. You were right. I was missing 2 references. Now here's a question. This database is designed to be used by a number of people. The 2 references I was missing were drivers for a signature pad. Not everyone who uses this database will have those drivers installed.

Is there a way to make those optional somehow? Or have some kind of test to see if the actual driver files are present on the hard drive, and if so, include those references? Maybe I'm grasping...

Thanks.

 

[boblarson]

As far as I know, if you have it in there it will generate a problem for any computers that don't have the item. So, you may want to have a special front-end for those with the signature pad and a separate one for those who don't.