Solved

Access app with multiple locations for an mda library file

Posted on 2014-03-11
4
421 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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

743 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now