Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 436
  • Last Modified:

Access app with multiple locations for an mda library file

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
bthouin
Asked:
bthouin
  • 2
  • 2
1 Solution
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
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
 
bthouinAuthor Commented:
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
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<>>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
 
bthouinAuthor Commented:
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

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now