OenCurrentDatabase question

[elwi]

Access 2013

I have a database open and a form with a button to accomplish the following:

1. open another database instance
2. import Excel files into db2
3. close the other database instance

I'm using the following code:

Dim appAccess As Access.Application
Set appAccess = New Access.Application
appAccess.OpenCurrentDatabase DLookup("strImp", "qlkpImpSb"), False
'make it visible
appAccess.Visible = True

'do the import using appAccess.DoCmd.TransferSpreadsheet...

appAccess.CloseCurrentDatabase
Set appAccess = Nothing

Database 1 and Database 2 share some linked tables, and sometimes when the above code above executes I get the error "You already have the database open."

How do I test to see if the database is open, and if it is, use it?

 

[spikepl]

Use
Dim appAccess as Object
Set appAccess = GetObject(FullPathOFTheDB)

It will open it if closed, or just get it if open.

 

[elwi]

I had seen this suggestion on another post and tried it, but doing something wrong

Using this..
Dim appAccess As Object
>>Set appAccess = GetObject(DLookup("strImp", "qlkpImpSb"))
appAccess.Visible = True
'import code here
Set appAccess = Nothing

Got "Automation error, Unspecified error" on the >>set statement. Can you see what I'm doing wrong?

 

[spikepl]

You are asking me to do the impossible and guess what is inside your dlookup. In the future, don't make people guess. Show what you have and what the values of the variables are. Besides, a dlookup without criteria just grabs the first value - where "first" is not very well defined, unless you have a query with an ORDER BY clause.

 

[elwi]

Revised to

Set appAccess = GetObject(DLookup("strImp", "qlkpImpSb"), "Access.Application")

Works fine now. Thanks for your assist.

 

[elwi]

Re: OpenCurrentDatabase question

Followup:

Uncovered the issue with my original code.

The problem was an Access setting. In Access Options | Client Settings | Advanced | Open last used database when Access starts was checked. When that was removed. Original code worked.

:banghead: