DAO problem

[Doug Bell]

Hi,
I am a bit puzzled. I need to programatically compact a database so that an
Autonumber field is reset.

I am getting an error stating that the database is opened exclusively.
It is open but I don't understand why.

The front end (Access) has linked tables but at the time where compacting is
required, all bound forms are closed. The controlling form is unbound and
closes the other forms releasing the database. As the controlling form
opens, I used DAO to open the database and then a recordset and populate the
controls on the form. Then I set the recordset to nothing (Set rs = Nothing)
and then the database (Set db=Nothing) but it is not releasing the database.
Closing the form does release the database.

Any ideas?

 

[Ken Snell]

Simply setting the recordset to Nothing *does not* close the recordset.
Thus, ACCESS maintains the recordset's connection even though you destroyed
your reference to it.

Close the recordset before you set it equal to Nothing.

 

[Doug Bell]

I had tried closing the Recordset and the Database but the connection still
persists.

 

[Ken Snell]

How are you "populating" the controls? By setting the form's recordsource
equal to the recordset that you opened? If yes, then you can't break the
connection, I don't think.

Post the code that you're using and let's see.

 

[Doug Bell]

Close is not required, setting the rs object and db object to nothingdoes
release the database.

Like most problems it was a stupid mistake on my part, I had a comboBox
recordsource set to a Table on the linked DB rather than to a temporary
table in the local db and it was this holding the connection open.

Doug Bell

 

[Doug Bell]

Ken,
Thanks,
I have spent 2 days chasing this and found it only a minute ago.

1 control, a combobox's recordsource was set to the linked table instead of
a local temporary table.

 

[Ken Snell]

Glad you found it.

 

[david epsom dot com dot au]

Simply setting the recordset to Nothing *does not* close the recordset.

However, Access/VBA will close the recordset for you after you
reduce the reference count to zero. I notice that world+dog
has come around to this sensible approach to memory management.

(david)

 

[Ken Snell]

Yes, ACCESS is supposed to do this ... but there are times when it fails. So
my (and others') recommendation is always to close it explicitly before
setting the recordset variable to Nothing.

 

[david epsom dot com dot au]

Can you demonstrate a specific piece of code where Access
fails to close a recordset?

I have to say that the only problems I ever had (Application
fails to close) with this were caused by looping through a
recordset collection and closing all open recordsets. That
problem went away when I just let Access close the global
recordsets.

(david)