Reference code in other DB?

[PieterLinden via AccessMonster.com]

Before anyone says it, please don't suggest that "all the studies should be
in one database". I know that. But the boss says no. Further, I would need
a really flexible GUI.... And yes, I *do* have a copy of Duane's At Your
Survey db...

I work on drug study databases in Access. Most of the databases I inherit
are suffering from serious cases of Spreadsheet. Instead of having columns
like this:

CREATE TABLE PatientSymptom(
PatientID long,
TestDate Date,
Symptom Text(75),
Grade Int,
Relatedness int,
Causality int,
PRIMARY KEY (PatientID, TestDate, Symptom)

I have something like this:
PatientID long,
TestDate date,
SymptomName[n]
Relatedness[n]
Causality[n]

So I wrote a module that has all the code I need to normalize the data, and I
use it all the time, in pretty much every database I inherit. What is the
easiest way to put it in one database and just reference the database the
code lives in? This is purely code - no forms, tables, etc.

I also tweaked the MultiPik UI from Access 2002 Developer's Handbook (still
gotta love that book!) to crank out a report in Word (needed to be editable),
which I use in every DB as well.

Is there any way I can just put everything in a Utility-type database and
just set a reference to it?
I know I could put it all the code in a Library database and then reference
it in my code.

It looks like my best option is to create the library for the normalization
routines (it's all just code modules anyway), and then I'd have to either use
my ReportingDB as a front end to the StudyDB backend....

The basic idea is to have all my Utilities in ONE place, so if I add new
functions to it, I can just use them and not have to worry about which
database they're in.

Any thoughts? (Of course, if you have questions, please ask!)

Thanks!

Pieter

 

[Jack Leach]

Do you mean like the SharedModules question posted a few hours ago? You want
one db to keep your code in that you can reference from many other
databases...

Save your mdb with just your modules, and then go to the one you want to
reference it from. Tools -> References -> Browse... select your mdb that you
want to reference. Poof... done (more or less)

I would recommend heavy disambiguation when working like this. For
instance... if your reference mdb is named libConversions.mdb, then call out
the functions like so:

libConversions.FunctionName(args)

a few posts below you someone had a very similar question, to which I
pointed them to some answers Graham Mandeno provided me with on the subject
not too long ago. Here it is...

http://www.microsoft.com/communitie...19b5&mid=0994f58d-0aee-40c5-89df-84eb1e1419b5

Graham explains the (much need-to-know) differences between using an MDE vs
and MDB for doing this.

hth... this was a great tool for me to use, luckily someone was able to get
me rolling with it.

gl!



--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

Before anyone says it, please don't suggest that "all the studies should be
in one database". I know that. But the boss says no. Further, I would need
a really flexible GUI.... And yes, I *do* have a copy of Duane's At Your
Survey db...

I work on drug study databases in Access. Most of the databases I inherit
are suffering from serious cases of Spreadsheet. Instead of having columns
like this:

CREATE TABLE PatientSymptom(
PatientID long,
TestDate Date,
Symptom Text(75),
Grade Int,
Relatedness int,
Causality int,
PRIMARY KEY (PatientID, TestDate, Symptom)

I have something like this:
PatientID long,
TestDate date,
SymptomName[n]
Relatedness[n]
Causality[n]

So I wrote a module that has all the code I need to normalize the data, and I
use it all the time, in pretty much every database I inherit. What is the
easiest way to put it in one database and just reference the database the
code lives in? This is purely code - no forms, tables, etc.

I also tweaked the MultiPik UI from Access 2002 Developer's Handbook (still
gotta love that book!) to crank out a report in Word (needed to be editable),
which I use in every DB as well.

Is there any way I can just put everything in a Utility-type database and
just set a reference to it?
I know I could put it all the code in a Library database and then reference
it in my code.

It looks like my best option is to create the library for the normalization
routines (it's all just code modules anyway), and then I'd have to either use
my ReportingDB as a front end to the StudyDB backend....

The basic idea is to have all my Utilities in ONE place, so if I add new
functions to it, I can just use them and not have to worry about which
database they're in.

Any thoughts? (Of course, if you have questions, please ask!)

Thanks!

Pieter

 

[PieterLinden via AccessMonster.com]

Do you mean like the SharedModules question posted a few hours ago? You want
one db to keep your code in that you can reference from many other
databases...

Save your mdb with just your modules, and then go to the one you want to
reference it from. Tools -> References -> Browse... select your mdb that you
want to reference. Poof... done (more or less)

I would recommend heavy disambiguation when working like this. For
instance... if your reference mdb is named libConversions.mdb, then call out
the functions like so:

libConversions.FunctionName(args)

Ohh... gotta LOVE that!!! This way I can link to my utility tables...
PERFECT!

 

[Stuart McCall]

Ohh... gotta LOVE that!!! This way I can link to my utility tables...
PERFECT!

One thing you need to know if you're going the library route is that should
an error occur in your library code, Access will let you debug it, edit it,
re-run it, edit it etc. etc. BUT the moment this current database closes,
any changes you made are lost. For good. That's because you're editing the
code in a copy of the module in RAM.

That doesn't mean you can't work this way though. All you have to do is copy
the changes to the clipboard, close the current db and open your lib db,
then paste in the changes. Or if the changes are spaced apart or very
complex, paste into notepad before switching db's, then copy 'n paste from
there.

 

[Jack Leach]

PERFECT!

Isn't it?

Between work and home, I can't count the number of times I've written a
function to get the filename from a full path, or return just the folder of a
full filename path. Oh wait... in one of these &#$*ing projects I had a
function to do this task... where was that??? Buried away in my dev trashcan
folder from two years ago?? Oh boy, time to go digging... hey! I found it.
Awww... that was the crap one... hadda better one...

I was purely ecstatic to pick up on this technique. It's like MZTools... I
couldn't imagine programming without it.

--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)