Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Creating reference through code

Posted on 2007-08-06
16
Medium Priority
?
2,312 Views
Last Modified: 2013-11-27
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
0
Comment
Question by:sf_garrison
  • 6
  • 5
  • 3
  • +1
16 Comments
 
LVL 44

Expert Comment

by:GRayL
ID: 19638968
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
 

Author Comment

by:sf_garrison
ID: 19639067
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
 
LVL 10

Expert Comment

by:aesmike
ID: 19639333
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

Author Comment

by:sf_garrison
ID: 19639495
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
 
LVL 10

Expert Comment

by:aesmike
ID: 19639585
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
 

Author Comment

by:sf_garrison
ID: 19639667
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
 
LVL 44

Expert Comment

by:GRayL
ID: 19642670
sf:  Yes.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 19649261
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
 
LVL 44

Expert Comment

by:GRayL
ID: 19649497
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
 
LVL 10

Accepted Solution

by:
aesmike earned 800 total points
ID: 19649671
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
 

Author Comment

by:sf_garrison
ID: 19656835
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
 

Author Comment

by:sf_garrison
ID: 19656902
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
 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 800 total points
ID: 19656925
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
 
LVL 44

Assisted Solution

by:GRayL
GRayL earned 400 total points
ID: 19656952
Why not locate the mdb in S:\ - the root of drive S?
0
 

Author Comment

by:sf_garrison
ID: 19657651
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
 
LVL 44

Expert Comment

by:GRayL
ID: 19657700
Good work.  Enjoyed watching;-)
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question