Solved

Access app with multiple locations for an mda library file

Posted on 2014-03-11
4
429 Views
Last Modified: 2014-03-12
Hi

I have an accdb Access app which is going to be used by different people in different departments. Each department is only allowed to access certain directories on a shared disk. So I will have to store the app in multiple, different directories so that users can run it. That app references an mda which contains lots of common functions and subroutines. I would therefore also like to have that same mda to be in multiple different directories.

So what I tried to achieve is that, when the app starts, it should delete the existing reference to the mda and add a reference to the "local" mda copy. I have added code to that effect in the app. But that doesn't work, because when the app starts, it has by definition a reference to an mda which it is not able to access because it is in a directory which it may not access (in this particular case, my development directory where I have the mda), so it cranks out an error (there is a broken reference...) which the users can't react to, and neither can the code at that point.

So, what are my options ?
is it possible to not have any reference at all to the mda in the app and add it on form load ? Or is it going to tell me that a reference is missing before the code can add it ?
Is it possible to trap the broken reference error ?
Or is the best solution to keep the mda in a directory which all users may access ? There is such a directory, but I'd like to avoid using it if only because anybody could then delete the mda by accident or because somebody might think that this file is not needed

Thanks for your opinions.
Bernard
0
Comment
Question by:bthouin
  • 2
  • 2
4 Comments
 
LVL 57
ID: 39920274
Bernard,

Before I answer your questions, I think a better solution in this case would be either:

a. Forget about the MDA and pull in the functions to the main DB.

b. Do a specific version for each department

Now for your questions:

<<    is it possible to not have any reference at all to the mda in the app and add it on form load ? Or is it going to tell me that a reference is missing before the code can add it ?>>

   Yes, but your app won't be in a complied state.   You need to be very careful a start-up to use fully qualified references for everything.

  For example

  Dim rst As DAO.Recordset and not

  Dim rst as Recordset.

  The second that you force VBA to figure out what you mean, it will hit the reference list, find the broken reference, and throw an error.

<<    Is it possible to trap the broken reference error ?>>

  Yes, but your app won't be compiled and performance will suffer a bit.

<< Or is the best solution to keep the mda in a directory which all users may access ? There is such a directory, but I'd like to avoid using it if only because anybody could then delete the mda by accident or because somebody might think that this file is not needed.>>

  That's another option, but it's a poor choice because your now forcing stuff to be pulled over the network, which is a performance hit.

  A MDA won't be too bade depending on what your doing with it; in VBA, as soon as a procedure is referenced, the whole module is loaded.   So with a MDA, mostly everything should load up on the first hit anyway.

  With all that said, I have to add that I never bother to use MDA's.   I find it far easier to keep a "master" DB with all my procedures, then simply delete and re-import all my modules from there.

 I keep one called OCS_AppSpecific, which holds all the routines specific to that one application, which I don't touch.

Jim.
0
 
LVL 1

Author Comment

by:bthouin
ID: 39920425
Hi Jim

Thanks for your quick answers.

>>You need to be very careful a start-up <<
Indeed. I do NOT have fully qualified refs everywhere. Actually, adding the reference would have to be the very first lines of code, does that make a difference ? I.e., has everything in the whole app to be fully qualified, or just the bit until the reference is added ?

>> trap the broken reference error <<
What's the error number and at what point can one trap it ?

>>your now forcing stuff to be pulled over the network, which is a performance hit<<
For all interactive applications, both the apps and the MDA would be on the same shared disk in different directories, so performace would not be impacted.

>>So with a MDA, mostly everything should load up on the first hit anyway<<
That's OK, because: a) I need lots of the functionality in the MDA very frequently in the apps; b) if i did not have an MDA, the code would be in the app anyway, so size would be the same and total load time probably also very similar. Also, I do not have any real performance requirements for the interactive apps.

>>I have to add that I never bother to use MDA's<<
What ? I thought it made a lot of sense to put commonly used stuff in a library, like in any other language. I really don't fancy deleting and re-importing even one module (which would hold what is in the MDA currently) to each of my apps everytime I change something in the common stuff. Especially not when I have 3 sets of all apps: dev, test and prod.
0
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 300 total points
ID: 39920658
<<>>You need to be very careful a start-up <<
Indeed. I do NOT have fully qualified refs everywhere. Actually, adding the reference would have to be the very first lines of code, does that make a difference ? I.e., has everything in the whole app to be fully qualified, or just the bit until the reference is added ?>>

  Everything up to the point of checking/fixing references.  And I mean everything.  It's a royal pain.  ie. if you use a Mid(), it needs to be vba.Mid()

 If you miss fully qualifying one thing, you'll get the reference error.  You can't even open a form at startup until your done fixing references.

 You also need to use the minimal amount of code possible, and it needs to be in it's own module.

<<What's the error number and at what point can one trap it ?>>

 It's not an error.  If you hit the error it's too late.   You need to check the references like this:

Public Function CheckReferences()

Dim ref As Reference

Dim sref As String

For Each ref In Application.References
  If (ref.IsBroken) Then
    sref = ref.Name
    sref = ref.FullPath
  End If
Next ref

End Function

<<What ? I thought it made a lot of sense to put commonly used stuff in a library, like in any other language. I really don't fancy deleting and re-importing even one module (which would hold what is in the MDA currently) to each of my apps everytime I change something in the common stuff. Especially not when I have 3 sets of all apps: dev, test and prod. >>

  With Access, not from my viewpoint.  If find it far simpler to deal with importing.   I  have modules grouped by function, and only about ten in total.   With MDA's, there's always the reference issue, which I don't like to deal with.

 For example, under terminal services, the MDA will always be shared unless you generate a specific version for each user, or fix the reference at start-up as your intending to do.

 First it's a pain to do that, and second, your app always ends up in an uncompiled state, so it executes slower.  There is an undocumented syscmd() call to compile an app on the fly, but I don't know if it works anymore.

 Not worth the effort in my book.

Jim.
0
 
LVL 1

Author Comment

by:bthouin
ID: 39922793
Hi Jim

>> For example, under terminal services, the MDA will always be shared <<
OMG ! I had NO idea this was the case. I thought the MDA was loaded in each app separately when the apps start. I am working at a client who has a Citrix-only infrastructure, so terminal services is a well known word around here...

I had lots of very strange things happening lately with common routines from the MDA, like "Too many users in timer event" (I'm using timers quite a bit in many "server-like" applications, and the timers often call common routines in the MDA), key corruption with not just duplicates, but triplicates and more in one particular table having an autonumber as primary key and the inserts in that table being made by an MDA routine... Maybe this has somthing to do with the MDA sharing !

So I think I will swiftly follow your concept of an off-the-shelf collection of routines packed in modules in a "spare parts" application, so that I can copy/paste these modules in my various applications and junk the MDA.

Thanks for your help and precise answers. That was jolly good advice. You get the well-deserved points.

Regards
Bernard
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
VBA Access 2016 syntax 6 43
Criteria for Date for DCount 4 24
Field naming convntions - MS Access 7 29
Modify report 8 8
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

777 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