Creating reference through code

I need to find code that will create a reference to another Access DB which contains generic code used throughout several other company applications.  All of our user's applications are shared in a drive created specifically for them.  Here is an idea of what I am trying to use, but it doesn't seem to work on a properly.    (This code is called with an AutoExec Macro):
(Also, just a note, the DIOS database contains the SetUpMenu Function)
Public Function SalesAppStartUp()
On Error GoTo Err_Handler

Dim ref As Reference
Dim strDBPath As String
Dim strUser As String
    strDBPath = "\\genesys08\users\" & fOSUserName & "\databases\DIOS.accdb"
MsgBox strDBPath
'CHECK FOR MISSING REFERENCES, IF MISSING REMOVE REFERENCE
    For Each ref In References
        If ref.IsBroken Then
            References.Remove ref
            MsgBox "A Missing reference has been detected and removed", vbOKOnly
        End If
    Next ref

'ADD DIOS REFERENCE IF IT DOESN'T EXIST
AddRef:
    For Each ref In References
        If ref.NAME = "DIOS" Then
            GoTo MenuSetup
        End If
    Next
    References.AddFromFile (strDBPath)

'SET UP MAIN DATABASE MENUS USING FUNCTION IN DIOS
MenuSetup:
    SetupMenu
    GoTo Exit_Function
Exit_Function:
    Set ref = Nothing
    Exit Function
Err_Handler:
    If Err.Number = 29060 Then
        MsgBox strDBPath & " was not found.  Please Notify Systems Dept."
        Resume Exit_Function
    End If
    MsgBox Err & ": " & Err.Description
    Resume Exit_Function
End Function
sf_garrisonAsked:
Who is Participating?
 
aesmikeConnect With a Mentor Commented:
SF, this is a thought that you might try to help you get closer to the problem.
In the DIOS library Mdb, create a very simple function that you can call in the autoexec of the Front End app to determine if the library is functional.  Consider the following:
Public Function DIOSReady()
     DIOSReady= True
end function

Now, after you've checked and added references BUT before calling MenuSetup, call this function.  This function is just a test to see if the library is properly loaded.  If the function returns a value without a run-time error, you know the problem is NOT with the library being loaded or available and you can exclude this.  It's now a problem somewhere within SetupMenu
0
 
GRayLCommented:
Are you saying you want to use the UDFs in another mdb?  If so, on your machine, type Alt+F11 to get to the VB Editor, click Tools, Rererences, Browse, set files of type to .mdb, enter the address of the other machine's mdb, and click on it and press Open.  This will place a reference to the modules of the other mdb in your list of references.  Click the checkbox, and you will now have access to the functions and subroutines of the other mdb by name.  In the event there are duplicate names, preceed the function/sub call with the module name and a period.
0
 
sf_garrisonAuthor Commented:
Thanks GRayL, but I'm looking for another solution, but that may be the best route.  However, that would mean we have to keep the DIOS application in one location, and not place a copy in each user's drive, correct?  My boss was wanting me to find a way, I could put it in each user's drive.  Therefore, if I used the method you suggested, I would have to go to each user's machine, map that Reference to the DIOS database in their drive, then create the MDE, etc. , would I not?  
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
aesmikeCommented:
Hey, SF.  Questions for ya:
Is the ultimate goal to get a local copy of the front end on each PC but utilize a shared, common library on a network drive?

What happens when you use the code you posted?  What are the problems?
0
 
sf_garrisonAuthor Commented:
Yes, we actually have 3 developer's each in charge of differents apps (for instance I'm in charge of the Sales, Estimating, and PM applications, another developer is in charge of the Production and Purchasing, etc.)  However, there is certain code and forms which we want to maintain in another database which we call DIOS.  We actually store some procedures as well as forms in the DIOS database.  (for instance our forms allowing them to choose a job, select dates, and primarily our menu form, so they all look alike).  

The code I posted works sparatically (sp?).  Which with my skill level, makes if very hard to debug!  It may work 8 out of 10 times, but as for the times it doesn't work, I've yet to determine any rhyme or reason behind it.  When I say it doesn't work, what happens is the reference is just not created, so the code that tries to call the SetupMenu function crashes, because it resides in DIOS so it can not "see" it.
0
 
aesmikeCommented:
wierd.  
Are you creating this reference to the shared library each time the user opens the app?
Can you test to see if this reference is "broken"?
0
 
sf_garrisonAuthor Commented:
yes, if you will notice in my code posted above, I'm checking for missing ref:
 For Each ref In References
        If ref.IsBroken Then
            References.Remove ref
            MsgBox "A Missing reference has been detected and removed", vbOKOnly
        End If
    Next ref
Then I am looping through references, and if there is not one called "DIOS", I try to recreate it.
0
 
GRayLCommented:
sf:  Yes.
0
 
mbizupCommented:
Your code looks like it notifies the user of any type of error that might be encountered when the reference is created.  

>This code is called with an AutoExec Macro
>so the code that tries to call the SetupMenu function crashes
Is the SetupMenu function also called by the autoexec menu?  Is SetupMenu run in the same sequence of code as the references code, or is it run at a later time?

If the SetupMenu function is called immediately after the references code, this could be a timing issue and using DoEvents might help:

AddRef:
    For Each ref In References
        If ref.NAME = "DIOS" Then
            GoTo MenuSetup
        End If
    Next
    References.AddFromFile (strDBPath)
    DoEvents   '<------------------------------- add this


We also don't know much about the macro that calls this procedure.  Is it possible that the macro is encoutering conditions that are preventing this procedure from running?

0
 
GRayLCommented:
Re my last post:  If you can navigate to the mdb in question by clicking the Browse button in References in the source mdb using the path as shown in the strDBPath variable in the code above you should be able to set that reference before you generate the MDE's;
0
 
sf_garrisonAuthor Commented:
Sorry everyone... was sided tracked on another issue... in regards to this one, I'll try to address questions/comments below:

GRayL - Yes, I know I would have to set the reference before I create the MDE (or ACCDE in 2007), but the issue is that every application needs to be pointing to a copy of the referenced DB in a different location:  So for John Doe, I would have to go in and set the reference to S:\DoeJ\Databases\DIOS.accde, for Jane Smith, I would have to go in and set the reference to S:\SmithJ\Database\DIOS.accde, etc.  We have over 100 employees, so this obvioulsy isn't practical.  I may not have been clear, but my boss wants a separate copy of the DIOS db in everybody's drive.

aesMike - I know that the MenuSetup function works properly, as it actually creates the Menu form each time the user opens the app.... and 80% of the time this works fine.  When it does "bug out", and I hit debug, it takes me to that line of code "SetupMenu", but it's unrecognized because the "SetupMenu" resides in the DIOS db, and when I look, there is no reference created for DIOS.  If I go and manually created the references (Tools > References > Browse...etc.), then run the MenuSetup code it works fine.

mbizup - You may have something, I will try that. Maybe it's trying to call MenuSetup too soon at times  and because that makes my code crash, it halts the process of it even trying to continue with creating the reference.

I'll let you know... and thanks to you all for your patience and assistance.

mbizup - I wasn't thinking this was the issue, but you are right it could be.  I will test with the DoEvents function.  The reason I was not thinking this was it, was because
0
 
sf_garrisonAuthor Commented:
mbizup - Nope, that didn't do it... dang it!  Anyway, what are my other options for calling a Function in another DB without first creating a reference, or is there one?  Sorry, I'm still a beginner !
0
 
mbizupConnect With a Mentor Commented:
Can you add a debugging message box in the code to make sure it is actually running, and that there is not a problem with the calling procedure?
0
 
GRayLConnect With a Mentor Commented:
Why not locate the mdb in S:\ - the root of drive S?
0
 
sf_garrisonAuthor Commented:
Thanks to all of your inputs, I got it working.  I was calling the MenuSetup to soon, I am working in accdb(mdb's), and it was trying to compile on each open... since the reference to DIOS had not yet been established, but I was trying to call the MenuSetup ( in the DIOS)  in that same procedure, it couldn't compile/run the code.  I simply created a separate function only to call MenuSetup.  Then I changed my AutoExec, to first call the function to set the references, then to call a separate function which, called a second function which opened the MenuSetup program .....  I hate it when it ends up being something so obvious, that I've overlooked, but thanks to all of you, I got it figured out !
0
 
GRayLCommented:
Good work.  Enjoyed watching;-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.