Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-access > #10681

Re: Database Corruption

Show all headers | View raw

Patrick Finucane pretended :
> On Monday, April 27, 2015 at 10:03:43 AM UTC-4, Ron Weiner wrote:
>> I have a VB app that gets data from an external piece of hardware and 
>> saves the data into an access table.  The Access database we are saving 
>> the data in contains only tables, as it serves as a back end database 
>> for an Access application.  is We have been using this VB app for 
>> years, and in that time we have transitioned from Access 2000, to 
>> Access 2007, and Access 2010 as the target database.  All of the data 
>> is written into a single table.  The VB app wraps up all of the 
>> database inserts inside of a transaction.
>> 
>> When the Access database and VB application are on the same computer 
>> all is well.  We can blow through a couple hundred of the hardware 
>> devices, where each device could have  as few as 20 records or as many 
>> as 4-500 without error.  It is not uncommon  to have inserted 10-20,000 
>> rows in a single sitting.
>> 
>> There are times that we need to move data from 4-500 of these external 
>> hardware devices, and so in order to get the task done in a timely 
>> manner, we'd like to have more than one computer extracting and saving 
>> data at the same time.  When we need to do this we create an isolated 
>> wired network using computers with static IP's and a 4 port switch.  
>> The Access backend database is located in a Shared folder with "Full 
>> Access" for "Everyone".  All of the computers are running Win 7 Pro.
>> 
>> When using this networked solution we are starting to see cases of the 
>> Access database corrupting itself.  We get the following error message:
>> Error Num:5
>> Error Descr: The changes you requested to the table were not successful 
>> because they would create duplicate values in the index, primary key, 
>> or relationship.  Change the data in the field or fields that contain 
>> duplicate data, remove the index, or redefine the index to permit 
>> duplicate entries and try again. - strSql = INSERT INTO tblScan 
>> (ScanDateTime ,AttendeeID , OrderNum , ShowCode, RawDateTime, 
>> RawAttendeeID, AssetNum, DeviceID) Values ('4/22/2015 8:06:46 AM' 
>> ,29474005 ,23056 ,21722 ,'2015/04/22 08:06:46' ,'29474005' ,'12092752' 
>> ,'12D7007754')
>> 
>> The Primary Key in this table is an Access AutoNumber field (ScanID) 
>> and when I inspect the table after getting this error I see that the 
>> AutoNumber field IS trying to use a number that has already been used.  
>> Sometimes the number it wants to use is just a dozen or so numbers 
>> before whatever the next AutoNumber ought to be.  Sometimes it is 
>> several hundred numbers behind.  There does not seem to be any pattern.
>> 
>> Performing a compact and repair to the database typically fixes the 
>> issue and we are able to proceed with the downloading session.
>> 
>> This thing is causing me to loose hair at a horrific rate.  Any ideas 
>> at all would be greatly appreciated.  Thanks in advance for your help.
>> 
>> Rdub
>
> One guy said to create a new table (he did for his table coruption) and the 
> problem went away.  
>
> MS said in an article "Create a new table and append the existing records to 
> the new table. New records that are added to this table will start with an 
> AutoNumber value of one unless you set the increment value programmatically."

I should have mentioned that this table starts with Zero records for 
each session.  We essentially use one database per Downloading session.

Rdub

Back to comp.databases.ms-access | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread

Thread

Database Corruption Ron Weiner <rw@domain.com> - 2015-04-27 10:03 -0400
  Re: Database Corruption Patrick Finucane <patrickfinucanetx@gmail.com> - 2015-04-27 08:23 -0700
    Re: Database Corruption Ron Weiner <rw@domain.com> - 2015-04-27 11:50 -0400
  Re: Database Corruption Patrick Finucane <patrickfinucanetx@gmail.com> - 2015-04-27 08:30 -0700
    Re: Database Corruption Ron Weiner <rw@domain.com> - 2015-04-27 11:51 -0400
  Re: Database Corruption Patrick Finucane <patrickfinucanetx@gmail.com> - 2015-04-27 08:47 -0700
    Re: Database Corruption Ron Weiner <rw@domain.com> - 2015-04-27 11:53 -0400
      Re: Database Corruption Optimist <v.meldrew@i.do.not.believe.it> - 2015-04-29 13:25 +0100
  Re: Database Corruption CDMAPoster@fortunejames.com - 2015-04-27 08:48 -0700
    Re: Database Corruption Ron Weiner <rw@domain.com> - 2015-04-27 11:58 -0400
  Re: Database Corruption Ulrich Möller <knobbi38@arcor.de> - 2015-04-27 18:41 +0200
    Re: Database Corruption Ron Weiner <rw@domain.com> - 2015-04-27 13:57 -0400
      Re: Database Corruption Ulrich Möller <knobbi38@arcor.de> - 2015-04-27 20:21 +0200
        Re: Database Corruption Ron Weiner <rw@domain.com> - 2015-04-27 14:55 -0400
          Re: Database Corruption Ron Weiner <rw@domain.com> - 2015-04-27 16:46 -0400
            Re: Database Corruption Ulrich Möller <knobbi38@arcor.de> - 2015-04-28 01:13 +0200
              Re: Database Corruption Ron Weiner <rw@domain.com> - 2015-04-27 21:29 -0400
                Re: Database Corruption Ron Weiner <rw@domain.com> - 2015-05-05 22:16 -0400

csiph-web