Solved

Access app with multiple locations for an mda library file

Posted on 2014-03-11
4
432 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server 2005 running VERY slow on Virtual Machine 18 68
access vba 5 58
Access 2003, find all instances of database ODBC 3 53
Export individual report to pdf 2 41
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
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 …

752 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