table indexes messed up

[chirpyform]

Hi

I have a major problem with access 2000. I have a function that imports my data from another access 2000 database. The problem is that after all the data has been imported my indexes in the tables are messed up. This means that when I create a new record the identifiant created automatically, say 455 is given when really it should be 601, giving an error that the creation would give an index already used.

The code used for the import is the following :

If RecSetImport.RecordCount <> 0 Then
Var = RecSetImport.GetRows(RecSetImport.RecordCount)
NbCol = UBound(Var, 1) + 1
NbLigne = UBound(Var, 2) + 1
Cpt1 = 0
Cpt2 = 0

While Cpt1 < NbLigne
Recset.AddNew
While Cpt2 < NbCol
Recset.Fields(Cpt2) = Var(Cpt2, Cpt1)
Cpt2 = Cpt2 + 1
Wend
Recset.Update
Cpt1 = Cpt1 + 1
Cpt2 = 0
Wend
End If

If anyone has had the same problem or any ideas on how to get passed I would be grateful

Chris

 

[BNPMike]

This isn't an index issue. It looks like you are using autonumber fields and then trying to control the numbers in them. What number is generated is not related to indexes for the simple reason you don't have to have an index on an autonumber field.

Can you explain why you think it should be 601, and why you think 455 is wrong.

 

[chirpyform]

Well, a real example is :
In my table I have 1256 records after the import
The next autonumber is 2953
The last number in the autonumber column is 5081

If I save the table under another name the table works as I wish. ie the next autonumber is 5082

Chris

 

[BNPMike]

Can I just get clear what is happening here.

You have a table.

You import some records and there are now 1256 records. The highest number in the autonumber column is 5081. If you try and add another record, Access tries to assign numbers from 2953 onwards and presumably clashes with an existing number at some point? Is that the problem.

I don't understand your code (which would be perhaps better in SQL) but I suspect you are trying to assign autonumber values which, of course, is not an easy thing to do.

 

[PHV]

If the autonumber is the first field, let access assign it automagically, by replacing the 2 instances of:
Cpt2 = 0
By this:
Cpt2 = 1

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[chirpyform]

I can't because there are about 100 tables that have relationships between themselves which means that it must stay coherent.

Thanks for the help

I think the problem comes from the fact that the tables have relationships.

Chris

 

[PHV]

Either take a look at replica or don't rely on autonumber for relationships.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[chirpyform]

I tried the replica and after the numero auto was completely lost. Instead of 5081 I got -354180457!!!!


Now the database is lost.(It was a copy)

Thanks for your help

Chris

 

[PHV]

This is standard behaviour for replica.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[willir]

I can't because there are about 100 tables that have relationships between themselves which means that it must stay coherent.

You can not instruct Access what number to use for an AutoNumber field.

You can however, find out what the AutoNumber was used after creating the record.

Cpt1 = Recset!Cpt1

...or whatever the field name you are using.
Make sure you do not advance to the next record before capturing the autonumber.

And then use the autonumber as a forign key in the related tables.

Richard

 

[chirpyform]

It's strange because we have used this functionality (same code, same database) with Access 1997 without any problems. It is only since we have converted the database to Access 2000 that it doesn't work.


Chris