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\BEFolder\BEProd.mdb
my "development machine" Dell Desktop has Access 2007 and c:\myprofile..\documents\BENew.mdb.

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??
LVL 5
rberkeConsultantAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<Repairing database and relinking does NOT fix the problem.>>

Should have worked when you selected "always prompt for new location."

<<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??>>

  Not normal.  refreshing the links should have done it.

  Are you sure you were pointing to the correct BE?  

  In any case, I would not worry about it as long as it's functional.  Try writing a new record and putting something in one of those new fields to double check.  Then try updating one of those fields, and last, delete the record.

 If that all checks out then your are set.

Jim.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
John TsioumprisSoftware & Systems EngineerCommented:
any chance of having a duplicate history table in the back end.....just open the BE in design view and check...
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
0
Mark EdwardsChief Technology OfficerCommented:
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....)
1
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

PatHartmanCommented:
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.
0
rberkeConsultantAuthor Commented:
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.
0
Mark EdwardsChief Technology OfficerCommented:
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).RefreshLink to pickup structure changes to linked ODBC tables, which is much more efficient than dropping and relinking.  Every app with linked ODBC tables should probably have a startup process that includes automatically refreshing the links... I forgot about that one.  Thanks Pat.
0
rberkeConsultantAuthor Commented:
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.
0
PatHartmanCommented:
You're welcome Mark.
0
Mark EdwardsChief Technology OfficerCommented:
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....
0
rberkeConsultantAuthor Commented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.