Call function in other Database

[Bieke]

Hello,

I'm trying to run a public function from another database (kardexcommands.accdb) by a sub_click function of a button in my local database.
It works fine but every time i push this button a copy of kardexcommands.accdb database opens multiple times and then the functions is called correctly.
It would be nice if i can call this function ("Test") in an already open database .... (not duplicating)

The code:

Private Sub IN_Click() (This button is on my local database)
Dim appAccess As New Access.Application
appAccess.OpenCurrentDatabase ("W:\Bieke\Allerlei\kardexcommands.accdb") (This is the 'other' database where the function "Test" is)
appAccess.Run "Test"
End Sub

Can someone help me with that?

Thanks,

Bieke

 

[arnelgp]

the function Test() should be declared as Public.

 

[Bieke]

the function Test() should be declared as Public.

Correct, it is a Public function and is also working.

 

[Gasman]

Perhaps do not use the NEW keyword then?
Plus you do not appear to close that NEW instance?

 

[arnelgp]

you need to close the database afterward and clear it's variable:

appAccess.CloseCurrentDatabase
appAccess.Quit
set appAccess = Nothing

 

[arnelgp]

or you can make the appAccess object as Local to the form:

Option Compare Database
Option Explicit

Dim appAccess As  Access.Application

Private Sub IN_Click()
If appAccess Is Nothing Then
  Set appAccess = New Access.Application
   appAccess.OpenCurrentDatabase ("W:\Bieke\Allerlei\kardexcommands.accdb") (This is the 'other' database where the function "Test" is)
End If
appAccess.Run "Test"
End Sub

Private Sub Form_Unload()
   'destroy the object
   If Not (appAccess Is Nothing)
      appAccess.CloseCurrentDatabase
      appAccess.Quit
      Set appAccess = Nothing
   End If
End Sub

 

[Bieke]

you need to close the database afterward and clear it's variable:

appAccess.CloseCurrentDatabase
appAccess.Quit
set appAccess = Nothing

I tested it and it works but the database is used also bij other users so it has to stay open.

 

[Gasman]

I tested it and it works but the database is used also bij other users so it has to stay open.

You are not using their instance though, surely?

 

[arnelgp]

see

it has to stay open.

see post #6 for the code to stay open.

 

[The_Doc_Man]

I tested it and it works but the database is used also bij other users so it has to stay open.

If another person launches this "other" database they will launch their own private instance of it. The database can be marked "shared" in whatever way, but each USE of the DB, by Windows rule, must be individual and private because of the "no task interference" rule. The database DOES NOT have to stay open to be used by someone else. (OR, if it does, it is a design flaw.)

My thought is that this function you want to run should have a copy of the test function code in the database that wants to run the test. Particularly if the test function doesn't depend on data from the 2nd DB, it is cheaper to copy the code and make it local. AND there is the "IN" clause option that would allow your copy of the file to reference data in the 2nd DB without actually opening it as a database instance. Access back-end files and monolithic files are first and foremost just files. They don't have to be actively open (OPENDATABASE); they can be PASSIVELY open by any of several other methods.

 

[Pat Hartman]

There is something terribly wrong with this setup. If you have code that you need to run from multiple databases, the best solution is to make an .mda and use it as an add in. If you don't want to go to that trouble, then just copy the code so it is in both databases.

When you automate Access to run the code from it, you perforce open an instance of the database which you then have to close. And, it goes without saying that we NEVER share the same existence of any FE. Each user always has his own copy of the FE.

 

[Bieke]

If another person launches this "other" database they will launch their own private instance of it. The database can be marked "shared" in whatever way, but each USE of the DB, by Windows rule, must be individual and private because of the "no task interference" rule. The database DOES NOT have to stay open to be used by someone else. (OR, if it does, it is a design flaw.)

My thought is that this function you want to run should have a copy of the test function code in the database that wants to run the test. Particularly if the test function doesn't depend on data from the 2nd DB, it is cheaper to copy the code and make it local. AND there is the "IN" clause option that would allow your copy of the file to reference data in the 2nd DB without actually opening it as a database instance. Access back-end files and monolithic files are first and foremost just files. They don't have to be actively open (OPENDATABASE); they can be PASSIVELY open by any of several other methods.

I understand that is not the perfect solution for my problem. Here is wat i try to realise. The application is Back-end Front. I use one database to manage a warehouse (Kardex). This application controls the electrical magazin and also for looking up the inventory, here the user also needs to login with a badge reader. An other databases runs with barcode readers to book the stock when taking some parts out of stock. The tables are all in the same back-end. When the user is finished taking parts out of the stock (the 2e database) then he has to logout bij scanning also a barcode (logout). This is where i need a solution that when scanning de barcode 'logout' in the 2e database, the user in the 1e database (where the badge-reader is installed) is need to logout. The 2e database read the loginname from the first database, when the user is not logged-in in the first database he gets an warning to login first at the first database (badge-reader).
I want these 2 databases be seperated for some reason.

I hope it is a little bit understandable....

Thanks.

Bieke

 

[Pat Hartman]

I want these 2 databases be seperated for some reason.

The process sounds overly complicated. What is the purpose of "logging in" to a database that you are not actually using for any other task?

I once had a work order database where the users scanned their badge and then scanned the workorder to indicate that they were starting work on the work order. Then when the work order was complete, they scanned the workorder again and choose the complete option or chose the incomplete and entered a reason. Is this similar to your workflow? It was all handled by a single database.

 

[The_Doc_Man]

The reasons for keeping the database separate need to be good on a practical level, because keeping them separate isn't doing you any good. That situation over-complicates what would otherwise be a much simpler problem. I'm not saying that you HAVE to merge the two databases. I'm saying that you need a really good reason to NOT merge them.

You said that the tables are the same back-end. So let me ask this in a different way. Do you have ONE DB file for the data for everything but multiple different front-end files for, essentially, two different functions?

 

[MarkK]

I want these 2 databases be seperated for some reason.

When you build a house you do not build a kitchen under one roof, and a bedroom under a different roof, and a bathroom under still another. To make living your life easier, you build one roof, and you build all the rooms of your house very closely connected.

Apply this thinking to your database design.

Also, you can set a reference to another database. Open your references window, browse to the database you want reference, reference it. Now open the object browser, find the database you just referenced, and notice that all of its public methods and classes are visible and available for use. This is a much easier way to run code from a different database file.

 

[Bieke]

Here some more info.
A Kardex is a warehouse with 60 drawers in a box above each other who will bring the drawers to an opening on demand. There are two kardex next to each other.
The first database runs on a pc where an access program with tasks who controls the kardex en manage the parts and stock. You need to login with a badge so the program decides who has access to which drawers and who may edit the part-database. All tables are on a back-end where all other databases put/get there data.
Near each kardex is a pc one for each kardex, these running both a separate database and are provided with each a barcode reader.
When a worker ask for a certain drawer on the first pc kardex bring it to an output location. The worker scans on the other pc the barcode of the part to keep the stock up to date. He only has a scanner and all parameters will be inputted bij barcode. So far no problems. When the worker is finished he has to logout so that the next worker can not scan parts on his badge-id. The quickest way is scan the barcode ‘logout’ on the pc near the kardex where he booked out the article with the scanner but the logout needs to be done one the other pc where he logged in earlier. So there must be a command send to the other pc to execute the logout function.

Regards,

Bieke

 

[isladogs]

I agree with all the previous comments.
Whilst you certainly can run a function from an external database (and there are occasions when I do so), the simplest solution would just be to import the function to your current database.
Otherwise, I would either create an add-in (see post #11) if you need access to any objects such as forms, or add the file as a reference library (see post #15) if you only need the code from the file

 

[Bieke]

Here some more info.
A Kardex is a warehouse with 60 drawers in a box above each other who will bring the drawers to an opening on demand. There are two kardex next to each other.
The first database runs on a pc where an access program with tasks who controls the kardex en manage the parts and stock. You need to login with a badge so the program decides who has access to which drawers and who may edit the part-database. All tables are on a back-end where all other databases put/get there data.
Near the kardex are 2 pc’s one for each kardex, these running both a separate database and are provided with each a barcode reader.
When a worker ask for a certain drawer on the first pc kardex bring it to an output location. The worker scans the barcode of the part to keep the stock up to date. He only has a scanner and all parameters will be inputted bij barcode. So far no problems. When the worker is finished he has to logout so that the next worker can not scan parts on his badge-id. The quickest way is scan the barcode ‘logout’ on the pc near the kardex where he booked out the artikels with the scanner but the logout needs to be done one the other pc where he logged in earlier. So there must be a command to run the logout procedure on the other pc.

When you build a house you do not build a kitchen under one roof, and a bedroom under a different roof, and a bathroom under still another. To make living your life easier, you build one roof, and you build all the rooms of your house very closely connected.

Apply this thinking to your database design.

Also, you can set a reference to another database. Open your references window, browse to the database you want reference, reference it. Now open the object browser, find the database you just referenced, and notice that all of its public methods and classes are visible and available for use. This is a much easier way to run code from a different database file.

I’m not an expert but I’ll try to find out how to do this. Thanks for the reply.

 

[Bieke]

I agree with all the previous comments.
Whilst you certainly can run a function from an external database (and there are occasions when I do so), the simplest solution would just be to import the function to your current database.
Otherwise, I would either create an add-in (see post #11) if you need access to any objects such as forms, or add the file as a reference library (see post #15) if you only need the code from the file

Thanks…

 

[Bieke]

The process sounds overly complicated. What is the purpose of "logging in" to a database that you are not actually using for any other task?

I once had a work order database where the users scanned their badge and then scanned the workorder to indicate that they were starting work on the work order. Then when the work order was complete, they scanned the workorder again and choose the complete option or chose the incomplete and entered a reason. Is this similar to your workflow? It was all handled by a single database.

I added some more explanation. It’s about warehouse not workorders.