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

Groups > comp.databases.ms-access > #10673 > unrolled thread

Database Corruption

Back to article view | Back to comp.databases.ms-access

Contents

  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

#10673 — Database Corruption

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

[toc] | [next] | [standalone]

#10675

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

It seems odd that your data table autonumber is getting corrupted.  Maybe calling the code from Allen Browne can assist.  Call it before you do your insert.  http://allenbrowne.com/func-adox.html#SetSeed

[toc] | [prev] | [next] | [standalone]

#10679

Patrick Finucane formulated the question :
> 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
>
> It seems odd that your data table autonumber is getting corrupted.  Maybe 
> calling the code from Allen Browne can assist.  Call it before you do your 
> insert.  http://allenbrowne.com/func-adox.html#SetSeed

We think it odd as well. However, I can't even imagine what else would 
break if I attempted to re-set the autonumber seed before every insert. 
 As I said in the origional post it is not unusual for us to have to 
insert 10,000 to 40,000 records per session.

Rdub

[toc] | [prev] | [next] | [standalone]

#10676

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

Also see another link from Allen.  http://allenbrowne.com/ser-40.html

[toc] | [prev] | [next] | [standalone]

#10680

Patrick Finucane has brought this to us :
> 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
>
> Also see another link from Allen.  http://allenbrowne.com/ser-40.html

Been there, done that, bought the T shirt.  No Love:-(

[toc] | [prev] | [next] | [standalone]

#10677

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."

[toc] | [prev] | [next] | [standalone]

#10681

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

[toc] | [prev] | [next] | [standalone]

#10690

On Mon, 27 Apr 2015 11:53:28 -0400, Ron Weiner <rw@domain.com> wrote:

>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

Just a thought - if it works when the VB app is on the same PC as the Access table, would it be
possible for each PC to have its own Access table, which get read later by a central Access
application and copied to a consolidated table?  That assumes the VB app runs only part of the day.

[toc] | [prev] | [next] | [standalone]

#10678

On Monday, April 27, 2015 at 10:03:43 AM UTC-4, Ron Weiner wrote:
> ...
> Performing a compact and repair to the database typically fixes the 
> issue and we are able to proceed with the downloading session.

Try SQL Server Express as the backend.  It should do better than Access at avoiding such corruption.

James A. Fortune
CDMAPoster@FortuneJames.com

[toc] | [prev] | [next] | [standalone]

#10682

CDMAPoster@fortunejames.com expressed precisely :
> On Monday, April 27, 2015 at 10:03:43 AM UTC-4, Ron Weiner wrote:
>> ...
>> Performing a compact and repair to the database typically fixes the 
>> issue and we are able to proceed with the downloading session.
>
> Try SQL Server Express as the backend.  It should do better than Access at 
> avoiding such corruption.
>
> James A. Fortune
> CDMAPoster@FortuneJames.com

Yea, I think it may be time to put Access bahind me, and start using 
Sql as the backend databse.  This creates lots of logistical problems 
for us, but nothing that can not be overcome.

I was hoping for a "Magic Access Bullet" that i coule shoot at the 
problem, or even some "Pixie Dust" I could sprinkle around to make it 
all go avay.  :-)

Rdub

[toc] | [prev] | [next] | [standalone]

#10683

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

[toc] | [prev] | [next] | [standalone]

#10684

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

[toc] | [prev] | [next] | [standalone]

#10685

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

[toc] | [prev] | [next] | [standalone]

#10686

Ulrich Möller wrote on 4/27/2015 :
> 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

Ulrich, thanks for your thoughts on this.  I have no doubt that you are 
right on target, that it is a concurrency issue.  But after reading the 
knowledge base article you linked, I don’t think I want to start 
messing around with the table structure, removing the PK, other 
index's, and "roll my own" autonumber.  I think I deep enough in this 
hole, it's time to stop digging.

In that all of the computers we use for this application already have 
Sql Express installed, I think we will be best served to go that route. 
 I have never had anything even remotely like this happen to me when 
using Sql Server.

[toc] | [prev] | [next] | [standalone]

#10687

Ron Weiner pretended :

Wow, just had a brain storm.  Thought I'd run it past all of you.

What if I create a new table (tblUploadScan) with the same structure at 
the tblScan table we are currently using but without the AutoNumber 
column and without a PK?  I am thinking if there is no AutoNumber 
column and no Primary key, there is no concurrency problem.  All of the 
other columns that have Index’s allow for duplicate values.

Theoretically all of the data from multiple scanners goes in there 
without any chance of a AutoNumber getting out of sync.  Then later 
after all of the rows have been uploaded to this scratch table I can 
run a simple append query to get em all in the tblScan table.  Truth is 
I do not use the ScanID column for anything other than to have a PK in 
that table.  In our Schema it has no relationship with anything.

Does anyone see a problem with this?

Thanks
Rdub

[toc] | [prev] | [next] | [standalone]

#10688

Am 27.04.2015 um 22:46 schrieb Ron Weiner:
> Ron Weiner pretended :
>
> Wow, just had a brain storm.  Thought I'd run it past all of you.
>
> What if I create a new table (tblUploadScan) with the same structure 
> at the tblScan table we are currently using but without the AutoNumber 
> column and without a PK?  I am thinking if there is no AutoNumber 
> column and no Primary key, there is no concurrency problem.  All of 
> the other columns that have Index’s allow for duplicate values.
>
> Theoretically all of the data from multiple scanners goes in there 
> without any chance of a AutoNumber getting out of sync.  Then later 
> after all of the rows have been uploaded to this scratch table I can 
> run a simple append query to get em all in the tblScan table.  Truth 
> is I do not use the ScanID column for anything other than to have a PK 
> in that table.  In our Schema it has no relationship with anything.
>
> Does anyone see a problem with this?
>
> Thanks
> Rdub

Hi Ron

i can't see any obvious problem in doing so. Perhaps you don't even need 
any index in tblUploadScan for a better insert performance. You should 
only take care of counting the records before and after the insert 
operations to ensure that no record has been lost.

Ulrich

[toc] | [prev] | [next] | [standalone]

#10689

Ulrich Möller expressed precisely :

> Hi Ron
>
> i can't see any obvious problem in doing so. Perhaps you don't even need any 
> index in tblUploadScan for a better insert performance. You should only take 
> care of counting the records before and after the insert operations to ensure 
> that no record has been lost.
>
> Ulrich

We currently are counting the records immediately after committing the 
transaction to ensure that we have really written the number of records 
that we thought we wrote.  In order to get reasonable query performance 
we need to have index's on the 3 Columns we are selecting on.  So those 
index's will need to stay.  I hope to have some time later this week to 
code and test.  I’ll report back my findings.

Thanks to all for your help.

Rdub

[toc] | [prev] | [next] | [standalone]

#10709

Ron Weiner was thinking very hard :
> Ulrich Möller expressed precisely :
>
>> Hi Ron
>>
>> i can't see any obvious problem in doing so. Perhaps you don't even need 
>> any index in tblUploadScan for a better insert performance. You should only 
>> take care of counting the records before and after the insert operations to 
>> ensure that no record has been lost.
>>
>> Ulrich
>
> We currently are counting the records immediately after committing the 
> transaction to ensure that we have really written the number of records that 
> we thought we wrote.  In order to get reasonable query performance we need to 
> have index's on the 3 Columns we are selecting on.  So those index's will 
> need to stay.  I hope to have some time later this week to code and test.  
> I’ll report back my findings.
>
> Thanks to all for your help.
>
> Rdub

We just finished testing with the new schema, and it appears that our 
corruption problems are behing us.

I want to thank all who helped.

Rdub

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.ms-access

csiph-web