How do I truly close mdb in DAO?

[bdfigler]

I run a routine that interacts via DAO w/ Access database. When it's done, I want to open the database for the user to see. The only way I know how to do this is with ADO (e.g., Access.Application.OpenCurrentDatabase and .UserControl = True).

Problem is... I get an error saying that the database is open by another user -- even though I've closed db object and set to nothing. I understand that closing db object via DAO's close method affects the variable and not the db itself, but how do i effectively close the db so that I can open it again in ADO? Or can I accomplish the same thing another way? Thanks. -Brad

 

[Spellman]

Why don't you use Ado to interact with the database and to open it. Mark

The key to immortality is to make a big impression in this life!!

 

[CPUburn]

hi,

not sure if this is what you're looking for, but try

set db = nothing

this will destroy the database object if i'm not mistaking.

grtz
CPUburn

 

[bdfigler]

Sorry, maybe I wasn't clear. I close the connection AND set it to nothing, but that doesn't help. Just read a knowledge base article, and Microsoft says they keep a connection open for 10 minutes after I destroy the connection variable (how nice of them). They offer a solution, but it means changing the default time of 10 minutes to 1 second in VB.ini. Needless to say, that's a pain. Plus, I don't necessarily want to disable that feature in all my applications. Any ideas? -Brad

 

[bdfigler]

Mark, DAO is faster and I've already written the program.

 

[Spellman]

Have you tried to delete the databases .ldb file once you've shut down the connection. Make sure you back up your database before trying this cause i've never actually tried it but this file just stores locking information so I don't think it can do any harm. Mark

The key to immortality is to make a big impression in this life!!

 

[bdfigler]

great idea... but the .ldb file doesn't exist at this point.

 

[Sawatzky]

Dear Brad,

I am not sure why you use ado for opening the database.

but whenever I want to show up an accessdb I do it like this (with much more errorhandling ;-) )

Dim accapp As Access.Application
Dim mypath As String
mypath = "PATH/PATH/FILE.mdb"

Set accapp = New Access.Application
accapp.OpenAccessProject (mypath)
accapp.Visible = True

Or

Set accapp = GetObject(mypath)
accapp.Visible = True



then I do whatever I want with the access thing and afterwords I do
accapp.Quit

which closes the instance.

HTH
regards Astrid

 

[bdfigler]

Thought that was ADO... Is it not?

 

[Sawatzky]

Dear Brad,

could be I am missing something, but when I talk of ADO , I think of thinks like

dim my_recordset as ADODB.Recordset

as that is part of the Microsoft ActiveX Data Objects library

regards astrid

 

[bdfigler]

The code you wrote (e.g., Access.Application...) is automation, and I guess I just confused that with ADO. Anyway, that's how I was trying to open the mdb -- with the opencurrentdatabase method. Any idea why that's not working, astrid?

 

[Sawatzky]

Dear brad,

AFAIK

opencurrentdatabase assumes you have an instance of access running and have the handle of your instance in your hand as described above. Furthermore it assume a database is already open.

I never tried to use opencurrentdatabase for instantiating Acces. I just do not use the static reference ACCESS.APPLICATION for other things than creating new instances and than taking the handle/variable and work with it.


The only time when I see the error-message you described is when there are several instances running, of which at least one has the database open. You can check the number of instances running in the task manager.

HTH

regards Astrid