Access.Application Object question? 1

[tekvb1977]

I alread have an open access file. When I click on the command button, it opens up another access file. Following is the code that I am using:

Dim appAccess as Access.Application
Set appAccess = CreateObject ("Access.Application")

appAccess.OpenCurrentDatabase (CurrentProject.Path & "\"), False

This code works. But I am creaing another access application when I already have one open.
Instead of using:

Set appAccess = CreateObject ("Access.Application")

can I use some other command which sets appAccess to an already open Access Application rather than creating a new application object.

Thanks a lot.

J

 

[easyit]

use

CloseCurrentDatabase

first, then

OpenCurrentDatabase ("path to my database")

(and don't create an application object).

Try F1 for more info...

 

[easyit]

...sorry, please ignore the remark between the brackets.

 

[tekvb1977]

Thanks. But I can't close the current database because i have some code that needs to run later.

I have Main Database opened
Then I open 2nd database from my Main Database using VBA
Then I make 2nd database invisible
Then I call some function that resides in my 2nd database
Then I close my 2nd Database

Then I run some code which is in my Main Database.

I am not sure whether I am converying my point right. I hope I did. Please let me know if you figure something out. Thanks again

J

 

[Ravenous1]

You say that you are opening the second database to run a function in it? Do you also require data from the tables in the second database?

I agree with EasyIt's post; don't use another application object. Instead, you might work with a workspace object, create two database objects, assign the first to the current database, open the second database, run the functionality within it that you need, and close it.

Dim ws as Workspace
Dim db1 As Database
Dim db2 As Database

Set ws = DBEngine(0) 'Since "Workspaces" is the default
'collection for the DBEngine object,
'assigning ws to DBEngine(0) is
'effectively the same as setting =
'to the current ws...

Set db1 = CurrentDb 'not needed unless you need to work
'with/manipulate the 1st database
'from within the same workspace as 2nd

Set db2 = ws.OpenDatabase("MyOtherDatabase")

... 'Whatever the functionality you needed to open the
... 'second database for...
...

db2.close
ws.close

Technically, since you already have an implicit workspace open, you could simply "Set db2 = OpenDatabase('MyOtherDatabase')" if you don't need to use the 1st database object while the 2nd db object is open.

However, I like to use explicit workspace objects in any event for code clarity, because the workspace object gives you access to the "BeginTrans", "CommitTrans", and "Rollback" methods, which allow you to commit or rollback blocks of code based upon the success or failure of all steps in a transaction.

In addition to the workspace object, you might also define and "execute" a temporary QueryDef object against a second database object without ever opening the 2nd database.

 

[tekvb1977]

Thanks Ravenous1 for your e-mail.

Set db2 = ws.OpenDatabase("MyOtherDatabase")

... 'Whatever the functionality you needed to open the
... 'second database for...

Could you please tell me what command (syntax) to use in order to run function from my 2nd database.

Thanks

J

 

[Zion7]

tekvb, do you want to Run the code in the 2nd database, or have it manipulate data in the first?
If it is ti manipulate data in the 2nd, I don't know.
If it is to change data in 1st, then you can make a reference to it, as a library. from VBE window, Tools=> refrences

Here's code to do similiar, I got off the web...

Use Automation: make a reference to the Access Object Library


Dim AccApp as Access.Application
Set AccApp = CreateObject("Access.Application.9")
AccApp.Run "Sub1", arg1, arg2, ...


Rich