Using an .accde file as a Reference in Other Access Apps

[Lisab]

Is there a better way to use objects and code from one Access application in
other Access applications.

I have an Access 2007 application (the Login Tool). The Login Tool is added
as a reference in several other front-end Access Applications.
(VBA Code Window--Tools--References)

The Login Tool
•Displays a login form to get the UserID and Password
•Recreates the ODBC connection string using the UserID and Password
•loops through all the ODBC tables in the front-end application and re-links
the ODBC tables using the new connection string
•Resets the Primary Keys for tables that are views
•looks for PassThrough Queries in the front-end application and changes
their connection string to the new connection string
•checks to see if the password will be expiring in the next 14 days and
notifies the user accordingly
•returns control to the calling front-end application

The Problem:
Once the code in the login tool is called from the front-end application,
the lock file (.laccdb) remains open until the front-end application is
closed.

The Question:
Is there a way to force the Login Tool to close without closing the
front-end application? I tried adding Docmd.closeDatabase to the end of the
login process but that closes both login tool and the calling front-end
application

Is there a better way? Can the login tool be converted to some other format
ie .dll that would be more efficient.

What are the implementations if two or more users hit the Login Tool at
about the same time?

Thanks.

Answers preferred but, I'll accept theories, best guesses and logical
conclusions

 

[Albert D. Kallal]

The Problem:
Once the code in the login tool is called from the front-end application,
the lock file (.laccdb) remains open until the front-end application is
closed.

It not clear why the above is a problem. You might want to explain further
as to what the above problem is, or would be?

What are the implementations if two or more users hit the Login Tool at
about the same time?

Well, for the most part if you allowing more then on user to run your
application, then you need to place that application on each computer. I
explain the "reasoning" for this here:

http://www.members.shaw.ca/AlbertKallal/Articles/split/index.htm

The same goes for that logon system. It needs to be placed on each computer,
so the problem of more then on user kind of goes away....

 

[Lisab]

Thanks for your response.

The front-end application is installed locally for each user. always.

The Login Tool is on the network drive. This was done so that any changes
made to the Login Tool could be done and implemented without having to change
anything in the front-end application. No interruptions to the user.

So far there hasn't been a problem with having the Login Tool on the network
drive and having all the applications use it from that location. I just
thought that since after the login process was complete, the front-end
application no longer need it or refers to any code or object within it, so
why not "release it". Also, minor issue, If I make updates, and I often have
since this is new and in the initial phase, I have to wait till the end of
the day to replace the old version with the new one ... and hope that
everyone closes their access applications before they leave for the day.

my big question - is adding it as a reference the best way for other
applications to use the Login Tool or Should it be converted to a .dll or
something/can it be converted to a .dll or something more efficient.

 

[Albert D. Kallal]

my big question - is adding it as a reference the best way for other
applications to use the Login Tool or Should it be converted to a .dll or
something/can it be converted to a .dll or something more efficient.

If you have the developers and resources to create that logon system
in a different programming language + system, then sure you might be able to
convert it to something else. So, if you do have a team of experienced
developers at your disposal and have budgets for this type of work, then
yes you could do this. It really depends on the resources and developers
you have at your disposal. How you talked to them, what have they told
you?

Of course keep in mind that much of this problem can be avoided by simply
doing what the information technology industry has done for about the
last 20 years -> we installed word, power-point, excel, and <insert
whatever> application on EACH workstation. So, trying to "change" the
way you done things for such a long time is a uphill battle.

So, the "easy" solution is to start installing that software on each
computer like you done with every other application you have.

Another possible solution is to NOT use a reference in tools->references,
and try late binding....

Late binding is explained here:
http://www.granite.ab.ca/access/latebinding.htm

Perhaps another solution would be have the login tool installed on each
workstation, but modify it to "read" a common file from a shared
folder that has the information that changes. That way you not modifying
the code and could even make the "logon" part of your access application
and get rid of that reference stuff altogether.

So, late binding is a possible. I also think just changing the re-link +
logon code to ONLY grab some information from a "configuration file" from a
shared location on the server side sounds like another avenue that could
yield a solution here.