I have a form and select a table based on a code.
I open a subform and the data is the data i want.
I change the code.
DoCmd to delete the link and
DoCmd to link to the new table.

The Subform opens with the old data.
I go to TABLES and look at the data it is the new code's data.
I refresh in VBA but no change.
If I go to "View" design and "View" Form back the data is now correct.

Does Access Lock the links when the subform opens and never releases them.
Does it happen wnen the tables are used?

I am really confused.

Have you tried Requery on the subform? Refresh only "redisplays" data that may have changed on the data that is already displayed. Requery fetches the records again.

Randall J Weers
Membership Vice President
Pacific NorthWest Access Developers Group
http://www.pnwadg.org

Thank you for a reply.

I have tried a lot of code:

Once I link to the new tables I then do the following code
DoCmd.RepaintObject


CurrentDb.Relations.Refresh
CurrentDb.TableDefs.Refresh
DoEvents

Me.Refresh

Me.Requery

Me.Subform.Form.Requery

I am wondering if the top form somehow locks the files that are linked when it opens and does not release them.
OR the subform locks in tables and old links when it opens the first time.
This is the strangest thing in my many years of programming.

When you say "I open a subform", what does that mean? In Access terminolgy, a subform is a form that is on another from. Also know as a Child form on a Master form.

When you say you "DoCmd" to delete the link and link the new table, what are you actually doing? (Maybe give the full syntax of your DoCmd.) Are you deleting a linked table and creating a new one? If so, why? Why not just give the tables different names and change the record source of the form?

When a form is open, it essentially locks the table. If you're trying to delete a linked table while the form is open, you will not be able to do this. Are you sure the DoCmds that you are executing are successful? Even if they are successful, they may be pending the actual delete until the form is closed.

Just what are you trying to accomplish?

I have tables taht can be quite large that have names that directly link to a vendor name and when I am accessing a particular vendor I unlink the old vendor and relink to the new one. That way I dont have to change any querys or code.

I did have all the vendors in one table but the sorting and displays got very slow.

When I open the oroginal form I select the vendor I want or I can switch vendors while in the form.

Your statement that the files are locked was important because the links to the old vendor are still in place when I delete the link to the old one and set a link to the new one.

The DoCmd did work before it was just that the links establishe to the old table were not released until I closed and opened the form.

 What I did to fix this.
I copied the SubformName and renamed it SubformNameDummy
Then in the code that resets the links I added the line
Before the link delete and relink to the new tables

Me.SubformName.SourceObject = "SubformNameDummy"

Then after setting the new links I added the line

Me.SubformName.SourceObject = "SubformName"

And the CORRECT DATA SHOWED.

Ah yes. Now that I am clear that you are using a subform on a master, that would have been an idea I would have tried. Good find!!!

Note: you don't have to have a copy of your sub form. Try something like this... The DoCmd.Echo will shut off screen refreshing while you hide and restore the subform.

Very nice.

And cleaner since I don't have to have an extra form.

Tried the code and it works very well. Gives me the results I wanted.
Thank You