error# corruption in Linked Tables

After doing nothing special (just a few append and delete queries) about 1/3 of linked tables became corrupted in some of their fields (#error). I restarted the pc and did a compact and repair which did not work. I deleted and re-established the links which fixed the problem.

Has anyone any idea why this might have happened? Is this a "feature" of Access 2010 that I have to live with, or was I just unlucky?
LVL 24
EirmanChief Operations ManagerAsked:
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.

jerryb30Commented:
What type of db in back end?
0
Eric ShermanAccountant/DeveloperCommented:
Did you compact/repair the back end database the tables are linked to???  Sounds like you deleted some records in your back-end db without refreshing the form or table view in your front-end application ... could that be the case???

ET
0
EirmanChief Operations ManagerAuthor Commented:
The backend is just regular tables.

I compacted front and backends. The database has no relationships.

I didn't delete any records in the affected tables .... I did append from SOME (not to) of them to a new table. This new table was the only one where records were deleted en masse. This is the kind of thing I was doing.
Private Sub CommandNEWOLD_DblClick(Cancel As Integer)
DoCmd.SetWarnings False
    DoCmd.RunSQL "DELETE * FROM NEWANDOLD;"
    DoCmd.RunSQL "INSERT INTO NEWANDOLD ( THEWORDs ) SELECT INPUTS.THEWORD FROM INPUTS WHERE (((INPUTS.THEWORD) Is Not Null));"
    DoCmd.RunSQL "INSERT INTO NEWANDOLD ( THEWORDs ) SELECT VOCAB.THEWORD FROM VOCAB;"
    MsgBox "The New-Old Table Has Been Updated"
DoCmd.SetWarnings True
End Sub

Open in new window

The tables were fine with no corruption ... it was just the link to the tables that got screwed up.

I can't reproduce the error now, but I was messing around with various query code.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Eric ShermanAccountant/DeveloperCommented:
I think jerryb30  was asking what type of database you are using for the back-end (Access, MySQL, SQL Server, Oracle, etc.).

ET
0
Jeffrey CoachmanMIS LiasonCommented:
<I didn't delete any records in the affected tables>
Then what is this doing?
    DoCmd.RunSQL "DELETE * FROM NEWANDOLD;

<I compacted front and backends. >
Did you do this *before* you ran the SQL code?

What is also not clear is if these SQL statements are for any of the linked tables...
...or if any of the tables listed in the SQL are for the linked tables...

<After doing nothing special (just a few append and delete queries) >
If these queries affect hundreds, or thousands, of records, this may be part of the issue.

For example, I could "Only" run 1 little query, ...but if this 1 "little" query insets 100,000 records, then this would be something "special"
So it is not the number of queries that matters, it is more of what these queries are doing.
;-)

In any event, Access can sometimes have trouble maintaining links when you do "Bulk" operations (Like unloading and loading a table as you appear to be doing).

So sometimes you need to refresh the links.
This has been noted as something that happens from time to time in all versions of Access.

Common code to relink tables can be found here:
http://access.mvps.org/access/tables/tbl0009.htm

;-)

JeffCoachman
0
EirmanChief Operations ManagerAuthor Commented:
Sorry  jerryb30 I didn't understand the depth of your question ..... it's an access db.

<I compacted front and backends. >
Did you do this *before* you ran the SQL code?
I ran it after the #error problems showed which was after the sql code. My previous compact was about a week ago.

The NEWANDOLD table is temporary and is not for data storage. It's just a merge of two other tables and this is the only one I delete from with sql. I was only drawing data from the others for the append. Most bizarre of all was a table with 5 items only, that is only used as a lookup for the text values in a five item combo box, had to have the link repaired.



boag2000 said ... In any event, Access can sometimes have trouble maintaining links when you do "Bulk" operations
If that's the case, that's problem!
However, given that it's only a small uncomplicated db of <700 records at the moment, it's not a good omen. On the plus side, it only takes 90 seconds to delete and restore the links, so I can live that.
0
Eric ShermanAccountant/DeveloperCommented:
Since you can't duplicate the problem on a consistent basis, it will be mostly speculation at this moment.  If I had to pick one I'd say a Network connection issue between the workstation and your server.

ET
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
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
Microsoft Access

From novice to tech pro — start learning today.