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?
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?
jerryb30
What type of db in back end?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
I can't reproduce the error now, but I was messing around with various query code.
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 SubI can't reproduce the error now, but I was messing around with various query code.
I think jerryb30 was asking what type of database you are using for the back-end (Access, MySQL, SQL Server, Oracle, etc.).
ET
ET
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Sorry jerryb30 I didn't understand the depth of your question ..... it's an access db.
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.
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.
<I compacted front and backends. >I ran it after the #error problems showed which was after the sql code. My previous compact was about a week ago.
Did you do this *before* you ran the SQL code?
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" operationsIf 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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.