close / rename linked .mdb file

[waynemb]

I'm trying to update an mdb file that contains tables linked to my current
mdb - by renaming it, and then replacing it with an updated file. Access
won't let me rename the file, I assume because the links exist. I tried
deleting all the links, but Access still refuses, I guess because it hasn't
broken all the links and closed that file. What do I have to do so Access
will let me rename that file?

Delete all tables linked in TableSourceFile.mdb ' is this necessary?

Name TableSourceFile.mdb as RetiredFile.mdb ' rename old file

Name NewTableSource.mdb as TableSourceFile.mdb ' rename updated file

Thanks

 

[Smartin]

I'm trying to update an mdb file that contains tables linked to my current
mdb - by renaming it, and then replacing it with an updated file. Access
won't let me rename the file, I assume because the links exist. I tried
deleting all the links, but Access still refuses, I guess because it hasn't
broken all the links and closed that file. What do I have to do so Access
will let me rename that file?

Delete all tables linked in TableSourceFile.mdb ' is this necessary?

Name TableSourceFile.mdb as RetiredFile.mdb ' rename old file

Name NewTableSource.mdb as TableSourceFile.mdb ' rename updated file

Thanks

Have you tried trapping the error? Is the external file in use when
you try to rename it?

 

[Tony Toews [MVP]]

I'm trying to update an mdb file that contains tables linked to my current
mdb - by renaming it, and then replacing it with an updated file. Access
won't let me rename the file, I assume because the links exist.

What's the error message when you try to rename the file?

It sounds like that MDB file is still in use somewhere.

You shouldn't need to muck with the linked tables on the linking MDB.

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/

 

[waynemb]

Thanks, The error I'm getting is

Path/File access error

which I guess means Access can't "access" the file to close it, since it
has, or had, tables linked to my open mdb.

I never specifically open the TableSourceFile.mdb, but Access opens it in
some manner when I open my Main.mdb which contains links to tables in
TableSourceFile.mdb

Theoretically I'd say that TableSourceFile.mdb is not open when I try to
rename it. I'd say Access should close it once I delete all links to its
tables, since those links are the reason Access needed to open it in the
first place - but it seems that Access is not doing that, thus the problem.

 

[waynemb]

Thanks, the error message is

Path/File access error

The TableSourceFile.mdb in question never gets opened by itself, and it
never gets opened period, except when Access "opens" it to deal with the
linked tables when Main.mdb is opened. So theoretically, that file is not in
use at all, once the table links from Main.mdb have been deleted.

I think you're probably wrong about not needing to muck around with the
linked tables, but it'd be great if you were right. It looks to me like
Access considers the linked mdb "open" and thus won't allow any renaming as
long as my open database has links to tables in the file to be renamed.
Unfortunately, even after mucking around with the linked tables, to the
extent of deleting them, Access still wants to consider that other file
"open" and unrenameable.

Thanks again

 

[Smartin]

Thanks, The error I'm getting is

Path/File access error

which I guess means Access can't "access" the file to close it, since it
has, or had, tables linked to my open mdb.

I never specifically open the TableSourceFile.mdb, but Access opens it in
some manner when I open my Main.mdb which contains links to tables in
TableSourceFile.mdb

Theoretically I'd say that TableSourceFile.mdb is not open when I try to
rename it. I'd say Access should close it once I delete all links to its
tables, since those links are the reason Access needed to open it in the
first place - but it seems that Access is not doing that, thus the problem.

The external MDB shouldn't be "open" (create a locking file) unless
the local file is using one of the linked tables--either one of the
linked tables is open, or a recordset, query, form or report using a
linked table is open. Can you rule out all of the above?

 

[Tony Toews [MVP]]

Path/File access error

The TableSourceFile.mdb in question never gets opened by itself, and it
never gets opened period, except when Access "opens" it to deal with the
linked tables when Main.mdb is opened. So theoretically, that file is not in
use at all, once the table links from Main.mdb have been deleted.

Agreed. Unless Access still has the Main.mdb open and you have some
code in the startup procedure which opens a database or recordset
object to improve performance.

But if you are trying to rename main.mdb and it won't let you then
someone is still in it. There is no reason to actually be inside
Access to be removing linked tables.

Are multiple users sharing that Main.MDB file?

Are there any ldb files in existence? That would also be an
indicator.

Opening the .ldb file using notepad will show you both who's currently
in the database and some of the workstations which were in the
database. When a person exits Access their workstation name and
Access login id, Admin unless you are using Access security, are left
in a "slot" or record in the ldb file. This slot or record may get
overwritten the next time someone enters the MDB depending on what
slot or record is available previous to it in the ldb file.

Determining the workstation which caused the Microsoft Access MDB
corruption
http://www.granite.ab.ca/access/corruption/workstation.htm

I think you're probably wrong about not needing to muck around with the
linked tables, but it'd be great if you were right. It looks to me like
Access considers the linked mdb "open" and thus won't allow any renaming as
long as my open database has links to tables in the file to be renamed.

But why is your main.mdb database open in the first place?

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/

 

[waynemb]

Yes, I can rule out any current usage of the linked tables - and I still have
the renaming problem, even after I delete all the links to those tables, so
theoretically my Main.mdb doesn't even know those tables exist anymore. And,
as I said, the mdb with the linked tables doesn't get opened by itself, and
is not referenced by any other database, so it can't have been opened by some
other means.

 

[waynemb]

My Main.mdb is an application that is trying to update tables and data it
uses, by retiring the old TableSourceFile.mdb and replacing it with an
updated version.

 

[Tony Toews [MVP]]

My Main.mdb is an application that is trying to update tables and data it
uses, by retiring the old TableSourceFile.mdb and replacing it with an
updated version.

Ah, ok. That makes a bit more sense. I do the same thing in my apps
except I do a compact on the backend.

Once you're at the point just before you are ready to rename the
TableSourceFile.mdb is there an ldb file present? If there is there
is still something open against the TableSourceFile.mdb.

So you will need to ensure all bound forms are closed as well. Or
unbound forms with combo boxes bound to a table.

Along with any recordsets which may have been left open.

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/

 

[waynemb]

I don't know if there is any .ldb; I never paid much attention to those, and
don't understand what they're doing. Please let me know if you think ldb's
are relevant, but I'm assuming that once I've successfully deleted all the
links to tables in TableSourceFile I can be certain that there's nothing in
my Main file that is still open against the TableSourceFile. That's what
makes this so puzzling - why and how is Access keeping TableSourceFile open,
when there are no longer any links to it from any other file?

 

[Tony Toews [MVP]]

I don't know if there is any .ldb; I never paid much attention to those, and
don't understand what they're doing. Please let me know if you think ldb's
are relevant, but I'm assuming that once I've successfully deleted all the
links to tables in TableSourceFile I can be certain that there's nothing in
my Main file that is still open against the TableSourceFile. That's what
makes this so puzzling - why and how is Access keeping TableSourceFile open,
when there are no longer any links to it from any other file?

No, disregard the table links. Don't bother trying to delete those or
work with those in any way. Those have *NOTHING* to do with your
problem.

I suspect a recordset pointing to a linked table on the backend would
survive removing the linked table.

The ldb contains all kinds of "phantom" locking information so that
Access can track which of the users is locking what page of data or
record.

The key thing, as far as you're concerned, is the existence of such a
file. Reread my previous posting for comments about bound forms or
bound combo boxes or recordsets.

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/

 

[Smartin]

I don't know if there is any .ldb; I never paid much attention to those, and
don't understand what they're doing. Please let me know if you think ldb's
are relevant, but I'm assuming that once I've successfully deleted all the
links to tables in TableSourceFile I can be certain that there's nothing in
my Main file that is still open against the TableSourceFile. That's what
makes this so puzzling - why and how is Access keeping TableSourceFile open,
when there are no longer any links to it from any other file?

The existence of TableSourceFile.ldb means the corresponding mdb file
is "open", directly or indirectly.

In addition to Tony's latest reply, is there any chance you are using
recordset objects in your code, and are you certain they are all set
to NOTHING before trying to rename the file?

 

[David W. Fenton]

The TableSourceFile.mdb in question never gets opened by itself,
and it never gets opened period, except when Access "opens" it to
deal with the linked tables when Main.mdb is opened. So
theoretically, that file is not in use at all, once the table
links from Main.mdb have been deleted.

I just ran a test -- I created a new linked table, opened a
recordset on it and then deleted the table link. The recordset
remained valid, and kept the back-end LDB open. So, no, deleting the
table links does *not* invalidate any open recordsets.

 

[David W. Fenton]

Once you're at the point just before you are ready to rename the
TableSourceFile.mdb is there an ldb file present? If there is
there is still something open against the TableSourceFile.mdb.

So you will need to ensure all bound forms are closed as well. Or
unbound forms with combo boxes bound to a table.

Along with any recordsets which may have been left open.

And, as I said in my earlier post, deleting linked tables does not
invalidate recordsets that were opened using those links before they
were deleted.

 

[David W. Fenton]

I don't know if there is any .ldb; I never paid much attention to
those, and don't understand what they're doing.

That's the record locking file for your back end file. If it exists,
it means one of two things:

1. someone is actively using the back end file, OR

2. Access could not delete the LDB file when it closed the last
connection to the back end (or Access crashed or was shut down with
Task Manager).

Please let me know if you think ldb's
are relevant,

Of course they're relevant, as they are an indicator that someone is
using the back end. If you can delete the LDB file, then nobody is
using the corresponding MDB. If you can't delete it, then the LDB is
in use, which means the back end is still in use.

but I'm assuming that once I've successfully deleted all the
links to tables in TableSourceFile I can be certain that there's
nothing in my Main file that is still open against the
TableSourceFile.

No, you can't assume that. You can open a recordset on a linked
table, then delete the linked table, and the back end will still be
in use, because deleting the linked table does not invalidate
recordsets opened using it.

That's what
makes this so puzzling - why and how is Access keeping
TableSourceFile open, when there are no longer any links to it
from any other file?

What makes you think this is true?

 

[David W. Fenton]

I suspect a recordset pointing to a linked table on the backend
would survive removing the linked table.

Indeed, that is correct.

 

[Tony Toews [MVP]]

I just ran a test -- I created a new linked table, opened a
recordset on it and then deleted the table link. The recordset
remained valid, and kept the back-end LDB open. So, no, deleting the
table links does *not* invalidate any open recordsets.

Thanks. I was thinking I should test the same thing but never got
around to it.

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/

 

[waynemb]

Thanks. I was closing a dependent form shortly before trying to rename the
file with the linked tables. It seems that even though I closed it, Access
wasn't completely letting go, because the calls I was making were contained
in an event procedure called from a button on that form. It looks like
Access doesn't complete a "close" command until the calling event procedure
has terminated. Thanks again for your help.

 

[waynemb]

Thanks. I was closing a dependent form shortly before trying to rename the
file with the linked tables. It seems that even though I closed it, Access
wasn't completely letting go, because the calls I was making were contained
in an event procedure called from a button on that form. It looks like
Access doesn't complete a "close" command until the calling event procedure
has terminated. Thanks again for your help.