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
(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
GRayL
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.
ASKER
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?
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?
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?
ASKER
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.
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.
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"?
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"?
ASKER
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.
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.
sf: Yes.
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 '<------------------------
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?
>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 '<------------------------
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?
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;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.acc
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
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.acc
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
ASKER
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 !
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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 !
Good work. Enjoyed watching;-)