Close external database using VBA

[CT25]

I am looking for code to close external database. I am "opening" an external
database by assigning it as a reference in my current database. With prior
help from this site, I can easily add and remove the references but the
external database does not close when the reference is removed. The .lbd
file remains and can not be deleted. I have not been successful so far in
trying to close the database - I'm going to try reopening it and then closing
it but I would prefer not to do it that way.

I would appreciate help with code that does not care how the external
database was "opened". Or is this a reference issue?

 

[Dymondjack]

http://www.mvps.org/access/api/api0025.htm

I haven't gotten to use this yet, but I did save the link just in case.
Maybe it will put you on the right path...

 

[Graham Mandeno]

Hi CT25

When you say "reference", do you mean a reference in your VBA project, or do
you mean you have added linked tables from the external database?

If it is linked tables, then the external database should close (and the
..ldb file should be deleted) when the last reference to one of the linked
tables is closed. Such references could be created several ways:
- opening one of the linked tables
- opening a query, form or report based on a linked table
- opening a Recordset in code based on a linked table
- assigning a linked table to a TableDef object in code
- directly opening the external database with OpenDatabase in code
- etc ...

All of these references should close themselves when the code completes or
when the object is closed, but Recordsets and Database objects should always
be closed explicitly.

If you are referring to VBA project references, then I can't imagine why you
would want to add and remove references on the fly in your code. Can you
please give a bit more information about what you are trying to do?

 

[CT25]

VBA project references. I would prefer not to remove them.

My goal is to have active VBA project references to an external database and
be able to compact the external database using VBA code.

I need to close the external database (or my VBA code for compacting does
not work) and I thought that I needed to remove the reference to do that but
it was not enough.

I had intially tried VBA code to open external database - it ran fine in
debug - but I could not eliminate file access error that occurred in realtime
- so I switched to references.

Any ideas?

Hi CT25

When you say "reference", do you mean a reference in your VBA project, or do
you mean you have added linked tables from the external database?

If it is linked tables, then the external database should close (and the
.ldb file should be deleted) when the last reference to one of the linked
tables is closed. Such references could be created several ways:
- opening one of the linked tables
- opening a query, form or report based on a linked table
- opening a Recordset in code based on a linked table
- assigning a linked table to a TableDef object in code
- directly opening the external database with OpenDatabase in code
- etc ...

All of these references should close themselves when the code completes or
when the object is closed, but Recordsets and Database objects should always
be closed explicitly.

If you are referring to VBA project references, then I can't imagine why you
would want to add and remove references on the fly in your code. Can you
please give a bit more information about what you are trying to do?
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I am looking for code to close external database. I am "opening" an
external
database by assigning it as a reference in my current database. With
prior
help from this site, I can easily add and remove the references but the
external database does not close when the reference is removed. The .lbd
file remains and can not be deleted. I have not been successful so far in
trying to close the database - I'm going to try reopening it and then
closing
it but I would prefer not to do it that way.

I would appreciate help with code that does not care how the external
database was "opened". Or is this a reference issue?

 

[CT25]

I'll check it out to see if this will work for me. Thanks.

 

[Graham Mandeno]

If this is a VBA reference, then I imagine removing the reference will cause
your code to fail to compile, therefore attempting to run the code that
compacts the external database will fail anyway.

But, let's take a step back here...

I can't imagine why you would want to have a VBA reference to a database
that contained anything except code and perhaps a few unbound forms. If it
contains no data, then why does it need regular compaction?

.... or am I missing something here?

--
Cheers

Graham Mandeno [Access MVP]
Auckland, New Zealand

VBA project references. I would prefer not to remove them.

My goal is to have active VBA project references to an external database
and
be able to compact the external database using VBA code.

I need to close the external database (or my VBA code for compacting does
not work) and I thought that I needed to remove the reference to do that
but
it was not enough.

I had intially tried VBA code to open external database - it ran fine in
debug - but I could not eliminate file access error that occurred in
realtime
- so I switched to references.

Any ideas?

Hi CT25

When you say "reference", do you mean a reference in your VBA project, or
do
you mean you have added linked tables from the external database?

If it is linked tables, then the external database should close (and the
.ldb file should be deleted) when the last reference to one of the linked
tables is closed. Such references could be created several ways:
- opening one of the linked tables
- opening a query, form or report based on a linked table
- opening a Recordset in code based on a linked table
- assigning a linked table to a TableDef object in code
- directly opening the external database with OpenDatabase in code
- etc ...

All of these references should close themselves when the code completes
or
when the object is closed, but Recordsets and Database objects should
always
be closed explicitly.

If you are referring to VBA project references, then I can't imagine why
you
would want to add and remove references on the fly in your code. Can you
please give a bit more information about what you are trying to do?
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I am looking for code to close external database. I am "opening" an
external
database by assigning it as a reference in my current database. With
prior
help from this site, I can easily add and remove the references but the
external database does not close when the reference is removed. The
.lbd
file remains and can not be deleted. I have not been successful so far
in
trying to close the database - I'm going to try reopening it and then
closing
it but I would prefer not to do it that way.

I would appreciate help with code that does not care how the external
database was "opened". Or is this a reference issue?