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

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

Re: Database Corruption

Show all headers | View raw

Ulrich Möller wrote on 4/27/2015 :
> Am 27.04.2015 um 16:03 schrieb Ron Weiner:
>> 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
>
> Hallo Ron,
>
> you do not mention whether you are using dao or ado, but i think you have a 
> concurrency problem with your cached transactional inserts statements. As in 
> this article https://support.microsoft.com/en-us/kb/240317 described i would 
> remove the autonumber from the primary key and instead use a normal long 
> value as an ID field. Then implement the nextkeyvalue function and use a 
> predefined query or stored procedure with parameters as standardized insert 
> commands.
>
> Ulrich

Thanks for the Link Ulrich.  I will definitely look into that.

However I am not sure that the advice will apply, as I am using the dot 
net OLEDB provider.  I was using the "Microsoft.Jet.OLEDB.4.0" provider 
until my problems started to arise, and changed to the 
"Microsoft.ACE.OLEDB.12.0" provider hoping for relief.  It made no 
difference.

I suspect that the Sql Server direction might be the best bet for me.  
It will require the most work, and training, but should provide the 
greatest level of relief.

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