Remove reference using VBA code

[CT25]

I have a reference to an external Access database that I would like to remove
using code so that I can compact the "closed" external database using code.
There was code for removing references in "help" but I need help on how to
determine name of the reference. Will removing the reference "close" the
external database?

 

[Daniel Pineault]

Take a look at the following post, it should answer your question (the last
post by Terry Kreft).

http://bytes.com/groups/ms-access/210488-how-delete-missing-references-vba
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.

 

[Dale Fye]

When you say you have a "reference" to an external Access database, do you
mean it shows up as checked in the References dialog box that you get to be
selecting Tools - References while in your vba editor window?

If not, what makes you think you have a reference to the database?

If you have tables linked from the external access database, you should
still be able to compact it, as long as you don't have any forms open that
use data from that database in their recordsource or as the rowsource for a
control.

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

 

[Tony Toews [MVP]]

you should
still be able to compact it, as long as you don't have any forms open that
use data from that database in their recordsource or as the rowsource for a
control.

Or reports, unlikely I know, recordset or database variables also open
against the BE database file. I'm being picky but ...

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[aaron.kempf]

wow Tony, it does sound like you've got an awfully flaky-ass database.

maybe if you just learned how to use a real database-- like SQL
Server-- then maybe you wouldn't have to deal with this problem
_AT_ALL_
maybe if you just learned how to use a real database-- like SQL
Server-- then maybe you wouldn't have to deal with this problem
_AT_ALL_
maybe if you just learned how to use a real database-- like SQL
Server-- then maybe you wouldn't have to deal with this problem
_AT_ALL_
maybe if you just learned how to use a real database-- like SQL
Server-- then maybe you wouldn't have to deal with this problem
_AT_ALL_

 

[Tony Toews [MVP]]

Please ignore Aaron's reply as he is monomaniacal on the subject of
ADPs and SQL Server.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[CT25]

Thank you. It worked great. I still need to figure out how to close the
external database - removing the reference does not close it.

I was using the following code which worked great in debug but gave me a
file access error in realtime - which is why I switched to using references.

Dim appAccess As Access.Application
Dim strCURRENTDIR as string
strCURRENTDIR = C:\...... (fullpath name to external database)
Set appAccess = CreateObject("Access.Application")
appAccess.OpenCurrentDatabase strCURRENTDIR, False
appAccess.Run "basSECTIONA"
Set appAccess = Nothing

Was I missing any code? Is using references better (seems to run faster)?

Cathy

 

[CT25]

It's the code I'm using to compact it - using a "Name" command to rename to
backup name and compacting to original name. Doesn't like the "Name" command
as long as the lbd file is open.

 

[Tony Toews [MVP]]

It's the code I'm using to compact it - using a "Name" command to rename to
backup name and compacting to original name. Doesn't like the "Name" command
as long as the lbd file is open.

That's correct. That's why we're telling you have to close all open
forms, reports, recordset or database variables.

Indeed the absence of the ldb file means that you've successfully
closed all such.

Tony

--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/