using modules in another database 1

[JeroenNL]

Hello,

I've divided my project into 2 seperate MDB files. One containing the user interface and one containing the tables. I've defined a few general functions in one of these databases and would like to use those functions in the other database as well (like, logging functions etc). But I don't want to just copy the function and paste it in the other database of course. Is there any way to use a function that is defined in one database in another database?

Bye,
Jeroen

 

[MikeLoon]

Here is a snippet for you:
Dim AccApp As New Access.Application, varProc As Variant

varProc = "sp_YourProcedure"

With AccApp
.OpenCurrentDatabase "Your_be.mdb", False
.Visible = True
.Run sProc, oid
.Quit
End With

 

[GKProgrammer]

Hi Jeroen,

Why don't you want to copy the code to the BE (I am assuming you would be copying to the BE DB) instead of using a connection method? The code would run alot smoother not having to connect over a network to another DB. Regards,
gkprogrammer

 

[JeremyNYC]

Wait, wait, wait. The whole point is that there should be NO CODE at all in the back end. Why do you need code there? Want to make changes to the data directly from your code? Just do it in the front end. You're linked to the tables in the BE so the changes will happen.

Tell us what you're trying to do, and we'll give you more accurate answers.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developers' section of the site for some helpful fundamentals.

 

[MichaelRed]

Of course, it could be that there is a FE, BE and a Me (MiddleEnd?) If so, there may be good (or at least sufficient) reason to reference the "Code" in (at least) the 'ME'. This could also be accomplished (for either the BE or the 'ME' by adding the appropiate .MDB (or .MDE) as a (static) reference to the FE. At least ONE reason to do this is to keep source code away from those who should not be there, and another is to minimize the footprint of the other db.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[pelegs]

It is correct and highly recommended that an application be divided into a front-end and a back-end. The FE should contain all code, reports, forms, and queries. The BE should contain nothing but tables. An exception is that the FE may contain some small, static tables, such as the list of USPS state code abbreviations. The advantages to this are several -- 1) updates to the code, forms, reports, and queries can be distributed relatively easy without having to worry about the tables; you just distribute a new FE mdb 2) compacting the tables is easy 3) this is the only way to share the BE data over a network 4) backing up only requires that the BE, perhaps on the network, be backed up 5) there may be more, but I that should be enough...

Now, that should convince you to use a BE/FE configuration. If not, every Access pro I know does it this way, and I've seen it recommended as standard practice all over the place.

Now, the middle-end is usually called a Library. It is just another mdb that contains some handy procedures that are generally useful in many applications. The easiest way to call procedures in a Library is to set a Reference to it. Open any code, either a module or code behind a form or report. Click Tools/Reference and then Browse to your library. Now, your Library procedures are available the the FE database just like Access built-in procedures. Nothing else needs to be done to call them. You can even put forms in your Library and call them from the FE although this is not quite as straight forward.

Peleg peleg@pstrauss.net

 

[JeroenNL]

Thanks for all your suggestions guys, I'm sure I will be able to get things working the way I want.

The way I've set things up sofar is as follows: I have a frontend (FE) containing the userinterface, a few macros and linked tables. I have a backend (BE) that contains the actual tables. The BE also has the queries though, and I wonder if that's the way to go.

Now I have a Logging table in which I store the activities of my application and possible errors. I want to log the activity of my FE and BE. I use a function to log the stuff and so this function must be available for both the FE and BE.

Question is: is this a good way to setup the application or should I realy move the queries to the FE too?

 

[pelegs]

Yes, definitely get everything out of the BE, including the queries. Depending on how you are intending to use the logging tables, you might consider putting them into yet another BE mdb. Even though this means having another mdb, there might have some advantages. Peleg
PelegNOSPAM@PStrauss.net

 

[GKProgrammer]

Hello Everyone,

OK how about this scenario? I have created a DB FE, BE for a company with about 10 end users of the FE. This company has no regular tape backup routine for their company, which I did suggest they adopt something but the final decision is obviously theirs. There is one person in the company that will be responsible for the maintenance of the computers and software etc, and there was a request for me to make a backup routine for the BE. I created an autoexec macro in the BE that will run the Backup routine and then close the BE automatically after it has run. Are you guys suggesting that I should have created another DB just to do this (ME), I don't see the sense in that, this code has nothing to do with the FE and I don't see a need to have to have another DB to backup this DB. Any and all info is grateful, I am trying to use this to learn what others strategy would be in this situation.
Regards,
gkprogrammer

 

[pelegs]

GK:

It seems like you understand it pretty well. You mentioned something about some logging and errors tables. I suggested that these be put in another mdb simply because it would be easier to flush these tables periodically if they where in a separate mdb. This is also in line with the idea of logically grouping things. I worked on a large app once and we had something around 6 BE mdb's because each one held a particular kind of data. It didn't have to be that way, but it just made things a bit cleaner, easier to maintain, and easier to deal with. The point is that multiple BE's are not harmful.

The ME was not necessarily for doing backups, although it would work. What I have is a whole passel of handy procedures that I use in almost every app I code. For example, I have a pair of functions that I put in the OnGotFocus and OnLostFocus events that change the background color of a control when the focus moves on or off the control to make it easy to find out where the cursor is. There is another routine that I use to properly quote text strings when used in SQL statements, and a similar one to properly format and quote date and time values. These all go in my ME library. It makes my life so much easier, makes my code better, and I tout this to my clients -- you get all this powerful code for free.

If you haven't invested too much in the backup macros, you might want to check this site. They are the premier toolmaker for Access:

http://www.fmsinc.com/.

They have a very nice program that, on schedule, can backup and compress your BE. It can also do the backup when users are actually connected because it does the backups by executing queries against each table and saving the results in the backup mdb. This is handy if you need to take backups several times a day. Compressing must be done when no one is connected, so this had to be done in the wee-hours, but since it has a built in scheduler, this is easily accomplished. Peleg
PelegNOSPAM@PStrauss.net

 

[MichaelRed]

MikeLoon

I haven't seen this approach. There are a couple of ??? here (for those of us who are less nimble).


1[tab]The variable [varProc] is defined as variant, but how could it ever be anything except a string? wouldn't this be more properly dimensioned as String?

2[tab]You decalare [varProc], but appear to use "sProc". I assume this is a typo?

3[tab]You include the argument "oid" in the .Run method, but this is does not appear to have a definition within the scope of the procedure. Please elaborate or explain.

4[tab]The .Run method is not an assignment statement (e.g. not XYZvar = .Run ...) so the assumption is that this version can only be used with a "SUB" (no return value) and something different is necessary for use with a procedure. True? False? Commentary?

5[tab]The prototype, as shown does not include any argument(s) to the procedure called, but logically, it must accept arguments according to the called procedure declaration statement, so the "sp_YourProcedure" would be expanded to:

"sp_YourProcedure(Arg1, Agr2, ... ArgN)"

Is this how you use the procedure in actual pratice?

Thanks for your explination.



MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[JeroenNL]

Hi!

To summarize things:

I should only have tables in the BE.
All code, macros and queries should be in the FE (so I also link to the tables in my BE from the FE)
Handy functions could be put in a ME

Can someone verify this?

Bye,
Jeroen