After relinking a table, it still shows old column names
I hope someone can shed light on this weirdness. The actual details would take hours to describe, but the simplest description of this repeatable problem is as follows:
My front end has a link to tblHistory in the back end database.
I double click the link to open it and see that it has 100 columns BUT it is supposed to have 115.
Repairing database and relinking does NOT fix the problem.
Trying again on a different machine with more recent version of Access does NOT fix the problem
Deleting the link and recreating it DOES fix the problem.
I have been using Access for many years, and this just seems wrong to me. I suspect there may be corruption that is not being caught by the repair utility.
Here are some more details.
rberke
---------
I have 3 databases on 3 commputers:
my Lenovo laptop with Access 2010 has c:\FEFolder\FE.mdb (A single image of this front end file is copied to every client's c: drive every day.)
my "production machine" has a network share \\wseserver\sharename\BEFo
my "development machine" Dell Desktop has Access 2007 and c:\myprofile..\documents\B
The Back Ends both contain tblHistory which has 115 columns. Originally that table only had 100 columns, but a month ago I added 15 columns on both machines.
On Saturday night, I deleted BEProd.mdb then copied BENew to production and renamed it to BEProd.mdb
On Monday morning I opened FE.mdb and noticed that FE.mdb only had 100 columns in tblHistory!
I tried running my relinkEverything macro, but there were still only 100 columns.
I then used linked table manager with "always prompt for new location" to manually relinked tblHistory to BEProd.mdb but I still only had 100 columns.
I then deleted the link and recreated it using Ribbon > External Data > Import and Link > Access > link.
THAT WORKED, all 115 columns were now visible.'
If found this very perplexing, so I restored Monday morning's FE data base and the same thing happened.
I again restored FE and repaired both BEProd and FE. Once again, FE started with 100 columns; relinking did not fix it; deleting and recreating did fix it;
So, I then decided try on an Access 2013 Desktop. Same problem I must delete the tblHistory link and recreate it to get 115 columns.
I am not too worried because everything else seemed OK and there were no error messages on any of the client computers.
But, I cannot easily restore the Back End since users are frequently making updates. But perhaps some experts have seen similar behavior and can reassure me that it is normal??
My front end has a link to tblHistory in the back end database.
I double click the link to open it and see that it has 100 columns BUT it is supposed to have 115.
Repairing database and relinking does NOT fix the problem.
Trying again on a different machine with more recent version of Access does NOT fix the problem
Deleting the link and recreating it DOES fix the problem.
I have been using Access for many years, and this just seems wrong to me. I suspect there may be corruption that is not being caught by the repair utility.
Here are some more details.
rberke
---------
I have 3 databases on 3 commputers:
my Lenovo laptop with Access 2010 has c:\FEFolder\FE.mdb (A single image of this front end file is copied to every client's c: drive every day.)
my "production machine" has a network share \\wseserver\sharename\BEFo
my "development machine" Dell Desktop has Access 2007 and c:\myprofile..\documents\B
The Back Ends both contain tblHistory which has 115 columns. Originally that table only had 100 columns, but a month ago I added 15 columns on both machines.
On Saturday night, I deleted BEProd.mdb then copied BENew to production and renamed it to BEProd.mdb
On Monday morning I opened FE.mdb and noticed that FE.mdb only had 100 columns in tblHistory!
I tried running my relinkEverything macro, but there were still only 100 columns.
I then used linked table manager with "always prompt for new location" to manually relinked tblHistory to BEProd.mdb but I still only had 100 columns.
I then deleted the link and recreated it using Ribbon > External Data > Import and Link > Access > link.
THAT WORKED, all 115 columns were now visible.'
If found this very perplexing, so I restored Monday morning's FE data base and the same thing happened.
I again restored FE and repaired both BEProd and FE. Once again, FE started with 100 columns; relinking did not fix it; deleting and recreating did fix it;
So, I then decided try on an Access 2013 Desktop. Same problem I must delete the tblHistory link and recreate it to get 115 columns.
I am not too worried because everything else seemed OK and there were no error messages on any of the client computers.
But, I cannot easily restore the Back End since users are frequently making updates. But perhaps some experts have seen similar behavior and can reassure me that it is normal??
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Technically, linked Access tables should always refresh any design changes when the fe database opens.
If your database opens with linked Access table with 100 columns, then you're linked to a table with 100 columns, not 115... the link information in the MSysObjects table should easily tell you what you are linked to.
Also make sure your source table is not an old table by another name and it's being linked by code that's using a different linked table name on the fe side (again, the MSysObjects table will tell you your [Foreign] source and linked [Name] table names as well as your [Database] Access db connection source.
Also, go to the Navigation Pane and make sure that both Hidden and System objects are set to be visible (unhidden) so you can make SURE you can see all the tables in you be and fe databases.
The cause is in there somewhere.
As a side-note, if it was linked ODBC tables (like SQL Server), those structures will NOT automatically refresh and show any structural changes, even on reopening the front-end db - you MUST drop and relink them to get the changes. (learned the hard way....)
If your database opens with linked Access table with 100 columns, then you're linked to a table with 100 columns, not 115... the link information in the MSysObjects table should easily tell you what you are linked to.
Also make sure your source table is not an old table by another name and it's being linked by code that's using a different linked table name on the fe side (again, the MSysObjects table will tell you your [Foreign] source and linked [Name] table names as well as your [Database] Access db connection source.
Also, go to the Navigation Pane and make sure that both Hidden and System objects are set to be visible (unhidden) so you can make SURE you can see all the tables in you be and fe databases.
The cause is in there somewhere.
As a side-note, if it was linked ODBC tables (like SQL Server), those structures will NOT automatically refresh and show any structural changes, even on reopening the front-end db - you MUST drop and relink them to get the changes. (learned the hard way....)
You don't have to drop links for ODBC tables to see schema changes, you just have to refresh the link either via code or using the Linked tables manager.
One other thing you might want to do is to change the Auto correct setting to no. This "feature" can be extremely helpful if you understand how it works and turn it on only when you want to use its functionality and then immediately turn it off.
One other thing you might want to do is to change the Auto correct setting to no. This "feature" can be extremely helpful if you understand how it works and turn it on only when you want to use its functionality and then immediately turn it off.
ASKER
I was trying to be careful to always point to the correct database, but it turns out I was wasn't careful enough.
Out of the 100 tables in this front end, this is the only table that intentionally points to a different backend.
Sorry to have wasted everybody's time, but EE comes through for me again.
Out of the 100 tables in this front end, this is the only table that intentionally points to a different backend.
Sorry to have wasted everybody's time, but EE comes through for me again.
rberke: Since manually relinking the table fixed the problem UNTIL you closed and reopened the database, then there MUST have been an automatic linking routine run on startup, otherwise the changed link should have held.
Did you have a relinking process running on startup? If you did, that little piece of info would have helped tremendously.
Also, Pat H is right, you only have to TableDef(TableName).Refres
Did you have a relinking process running on startup? If you did, that little piece of info would have helped tremendously.
Also, Pat H is right, you only have to TableDef(TableName).Refres
ASKER
Mark, My automatic relinker was working perfectly, I was just misinterpreting the results.
If I had been more careful I would have noticed that the one problem link's original address was a different from the 99 other links.
Once again, I apologize for wasting peoples time, so let's drop it.
If I had been more careful I would have noticed that the one problem link's original address was a different from the 99 other links.
Once again, I apologize for wasting peoples time, so let's drop it.
You're welcome Mark.
rberke: Agreed. One advantage to using the MSysObjects table in a filtered and summarized query is that you are using the computer to analyze what's there instead of having to eyeball it. I've had to help with many an issue where the folks looking at the problem couldn't figure out what was wrong, only for the computer to point out the minute difference that they were missing.
A distinct listing of connection paths in the [Database] field of the MSysobjects table would have resulted in TWO records with different paths - however different - instead of one, which would have forced you to take a closer look, even if you didn't want to. Other issues which can trick the eye are leading and trailing spaces, spaces or double quotation marks ("") instead of nulls, and invisible characters such as carriage returns or line feeds, etc. The computer can "see" a lot of things that the human eyeball can't, and probably never would....
I've always appreciated getting that tip many years ago, and just wanted to pass it on....
A distinct listing of connection paths in the [Database] field of the MSysobjects table would have resulted in TWO records with different paths - however different - instead of one, which would have forced you to take a closer look, even if you didn't want to. Other issues which can trick the eye are leading and trailing spaces, spaces or double quotation marks ("") instead of nulls, and invisible characters such as carriage returns or line feeds, etc. The computer can "see" a lot of things that the human eyeball can't, and probably never would....
I've always appreciated getting that tip many years ago, and just wanted to pass it on....
ASKER
Now I get it! You are saying I should run this query every time I have link problems.
SELECT distinct msysobjects.Database FROM msysobjects;
Or, even better
SELECT msysobjects.Database, Max(msysobjects.Name), Min(msysobjects.Name), Sum(1)
FROM msysobjects
GROUP BY msysobjects.Database;
That is a great idea. Thanks.
SELECT distinct msysobjects.Database FROM msysobjects;
Or, even better
SELECT msysobjects.Database, Max(msysobjects.Name), Min(msysobjects.Name), Sum(1)
FROM msysobjects
GROUP BY msysobjects.Database;
That is a great idea. Thanks.
If this is not the case..then repeat the process and check the linking info on the linked table...just go.to the Linked Table Manager and read carefully