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

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

Re: Database Corruption

Show all headers | View raw

Am 27.04.2015 um 19:57 schrieb Ron Weiner:
> 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

The SQL-Server might be a future solution, but i think it is little bit 
over sized for your application. It makes no difference whether you use 
the Jet/ACE provider or vb classic or vb.net because the problem are the 
cached inserts in an concurrency environment. A pragmatical solution is 
just the implementation of an handmade sequence.

Ulrich

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