Importing data

[Jenny1]

Can Anyone help with this I am desperate?

 

[KenHigg]

Are they sequential or random?

 

[Jenny1]

the column is auto increment and they are sequential I assume because normaly they increas from 1 upwards

 

[Rabbie]

Try this link for instructions on resetting the autonumber

http://support.microsoft.com/kb/812718

 

[Jenny1]

Rabbie to reset the auto number you need to delete the auto number field - I cant do this because the auto number field is the primary key, and I am warned that i need to redo the rleationsships before I delete

 

[Rabbie]

Sorry! Pardon Me I was only trying to help

 

[Rabbie]

It doesnt actually matter what the autonumber field is set to as long as it is unique to that record. It shouldn't have any other meaning.

 

[Jenny1]

True, but its a bit strange that excel decides to generate a number that begins with 40246824! I dont understand why it would do this

 

[Rabbie]

Looking at the Access help files I see that if you have replicated your database then among other changes autonumber fields no longer increase sequentially but are generated as random numbers.

This MAY be the explanation.

 

[Jenny1]

I see BUT they are increasing sequentially :
org_id
40247345
40247346
40247347
40247348
40247349
40247350
40247351
40247352
40247353

 

[GaryPanic]

you are almost there .(also got your message)

have you still got an empty dbase - good

do everything again - but the PK field - primary key

ok now a couple of ways to do this - either reset -and you have the post on this

or number them your self in xls 1, then 2 etc all the way down - this will work

or leave the auto field blank - it will do the numbers for you - remember its only a number ( although they start bloody high) - i usally start at 10,000- this allows me to put in some extras if need be

 

[GaryPanic]

or do it manually import and then
do 1
2
3
4
5
6
7
8
9
long winded but might be the quickest way - otherwise we are into heavy coding

 

[Jenny1]

Hi Gary, please explain again (sorry)

Option 1

or number them your self in xls 1

So your saying number each row in xls format then do the import thing again??

Option 2

Or leave the auto field blank - it will do the numbers for you

Again do you mean leave the first coloumn blank and import again

 

[KenHigg]

Sorry to butt in...

1. So the table you are importing into has a relationship with one or more other tables?

2. The spreadsheet data has no associated data that will be going into the related tables in Access?

ken

 

[Jenny1]

Currently I am not uploading to the tables that do have relationships, I am updatting to a new unrelated table and then attempting to move the data across to where I want it.

 

[KenHigg]

Then why are you worried about bringing over the autonumber field?

 

[Jenny1]

Because currently I dont bring the autonumber across, I then copy the current data and paste it to the table I want it in.

When I do this I obviously dont paste anything to the auto number field.

This field should fill itself.

And it does but it fills itself with a number that begins with 4003548

for no apparent reason.

 

[KenHigg]

Hum... Somewhere, somehow I'm guessing somebody appended a real large number and it simply took off from there. I was thinking a compact and repair would reset this?

ken

 

[GaryPanic]

ahh. this is an access prob and not a prob with your raw data-

ok double check the autofield number (even delete it and make a new one -same name - compact repair - then should be ok

failing this do as i suggested
import from xls and number the column 1 -690 and import -append on to your table - this will work (its a bit sloppy) but will work

g

 

[Jenny1]

I cant delete or change is as the message says this is part of a relationship.

So I need to use your other method

BUT if I add the numbers 1-690 how will future records start at 691??

And seocndly what do you mean when you say append - do you just mean copy and paste??