--TripWire--
asked on
Autonumber values being appended to after Import
Hello,
I have an MDB, in which I'm running a VB script (OnClick) that will import data from a csv.
But I've noticed, when I successfully import the data, my autonumber ID field has the number 3775 appended to the beginning of it.
Note: I'm not importing every field from the csv, just a few.
This is some rough code.
The autonumber field has changed like this....
2065, 2068, 37752069, 37752070, 37752071...
How do I get rid of this?
I have an MDB, in which I'm running a VB script (OnClick) that will import data from a csv.
But I've noticed, when I successfully import the data, my autonumber ID field has the number 3775 appended to the beginning of it.
Note: I'm not importing every field from the csv, just a few.
This is some rough code.
'Transfers data into temporary table in Access
DoCmd.TransferText transfertype:=acImportDelim, _
tablename:="tblMonthlyData", _
FileName:="D:\Documents\Examples\Monthly Data.csv", _
hasfieldnames:=True'Inserts data from temp table into "live" table
Currentdb.Execute "INSERT INTO MyLiveTable(Col1, Col2, Col3) SELECT Col1, Col2, Col3 FROM MyTemporaryTable"The autonumber field has changed like this....
2065, 2068, 37752069, 37752070, 37752071...
How do I get rid of this?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Is this the actual code you're using:
Currentdb.Execute "INSERT INTO MyLiveTable(Col1, Col2, Col3) SELECT Col1, Col2, Col3 FROM MyTemporaryTable"
Or is this just an example? When I suggested you use that in your other question, I also included instructions that you would need to change this to match the Tables and Fields in your project.
Unless you have a table named "MyLiveTable" with columns named Col1, Col2 and Col3, and a table named MyTemporaryTable with those same columns, this code won't work.
Currentdb.Execute "INSERT INTO MyLiveTable(Col1, Col2, Col3) SELECT Col1, Col2, Col3 FROM MyTemporaryTable"
Or is this just an example? When I suggested you use that in your other question, I also included instructions that you would need to change this to match the Tables and Fields in your project.
Unless you have a table named "MyLiveTable" with columns named Col1, Col2 and Col3, and a table named MyTemporaryTable with those same columns, this code won't work.
ASKER
Yes, I've changed the code to match the situation. The import works, because everything is transferring over fine, except for the autonumber field. But even then, the last four digits are accurate. Only the 3775 at the beginning comes from no where.
I can't really copy and paste the actual code I have, because I've been programming this from a terminal window on a server.
I can't really copy and paste the actual code I have, because I've been programming this from a terminal window on a server.
Are you inserting into the AutoNumber field? It's certainly possible to do that, and this could account for your anamoly.
Further: The value of the AN field should play no role in your data. It's nothing more than a surrogate key (at best) that points to a specific record in your table.
Further: The value of the AN field should play no role in your data. It's nothing more than a surrogate key (at best) that points to a specific record in your table.
In this commandline:
Currentdb.Execute "INSERT INTO MyLiveTable(Col1, Col2, Col3) SELECT Col1, Col2, Col3 FROM MyTemporaryTable"
if Col1 is your AN field, this can cause this problem. You don't have to include Autonumberfield in your SQL statement. So try this:
Currentdb.Execute "INSERT INTO MyLiveTable(Col2, Col3) SELECT Col2, Col3 FROM MyTemporaryTable"
Currentdb.Execute "INSERT INTO MyLiveTable(Col1, Col2, Col3) SELECT Col1, Col2, Col3 FROM MyTemporaryTable"
if Col1 is your AN field, this can cause this problem. You don't have to include Autonumberfield in your SQL statement. So try this:
Currentdb.Execute "INSERT INTO MyLiveTable(Col2, Col3) SELECT Col2, Col3 FROM MyTemporaryTable"
ASKER
No, I'm not inserting into my Autonumber field. I'm just using it as an arbitrary identifier. But I am using this number to identify the entries outside of this database. So I'm not too fond of it being that big (at least yet).
AN is auto-filling those numbers upon import.
AN is auto-filling those numbers upon import.
<So I'm not too fond of it being that big (at least yet).>
Can you explain what the trouble is with the size of the numbers? An AutoNumber field can contain any value between (appx) -2.15 BILLION and + 2.15 BILLION. Thats roughly 4.3 billion numbers available to you via the AN field in Access. You would reach the size limit of an Access database (2 gb) loooooooong before you "run out" of AutoNumber values.
And your AutoNumber field should have absolutely no "data value" - that is, it should not provide any functionality to that record other than providing a pointer to it. It should not be what defines the record as "unique" (although it can be a Unique index), and it should not be used for sequential ordering (since it's not guaranteed to be sequential, only unique).
What version of Access are you using? If you're using 2003 or below, make certain that you've fully updated the Jet engine. There were some issues with AutoNumber fields with earlier versions of Jet.
Jet Updates:
http://support.microsoft.com/kb/239114
Can you explain what the trouble is with the size of the numbers? An AutoNumber field can contain any value between (appx) -2.15 BILLION and + 2.15 BILLION. Thats roughly 4.3 billion numbers available to you via the AN field in Access. You would reach the size limit of an Access database (2 gb) loooooooong before you "run out" of AutoNumber values.
And your AutoNumber field should have absolutely no "data value" - that is, it should not provide any functionality to that record other than providing a pointer to it. It should not be what defines the record as "unique" (although it can be a Unique index), and it should not be used for sequential ordering (since it's not guaranteed to be sequential, only unique).
What version of Access are you using? If you're using 2003 or below, make certain that you've fully updated the Jet engine. There were some issues with AutoNumber fields with earlier versions of Jet.
Jet Updates:
http://support.microsoft.com/kb/239114
ASKER
I'm not using the AN to complete any operations, however, this database is not going to be used by me, the person that will use it uses that number as a reference outside of the computer for that record. (The paper record). Why do you not recommend the AN as a unique identifier?
The reason I don't want the numbers to jump that high yet, is because, when the database actually gets to the size of 3775xxx, I don't want my MDB to try and force the new records' AN to be the same as a previous record. I will ask for the Jet to be updated, and let you know if this helps the problem.
The reason I don't want the numbers to jump that high yet, is because, when the database actually gets to the size of 3775xxx, I don't want my MDB to try and force the new records' AN to be the same as a previous record. I will ask for the Jet to be updated, and let you know if this helps the problem.
ASKER
I just realized they are running MS Access 2002 on MS Server 2003 SP1...therefore this particular update won't apply. Do I have any other recourse?
ASKER
Is there a way to trim the leading 4 characters of AN?
ASKER
Nevermind. It was corruption after all. Started a brand new DB with the same fields and the info exports over properly now without affecting the AN field.
ASKER
Thank you!
<Why do you not recommend the AN as a unique identifier?>
An AutoNumber field can be a Unique index, but the ONLY function of an AutoNumber field should be as a surrogate key. In other words, it's nothing more than a quick and easy way to "point" at a specific record. AN fields should have no "data value" - that is, it should not matter whether the database system assigns a value of 100 or 324,575. Your system (i.e. your user interface) should not really expose this number, since it is meaningless to the end user.
You should use OTHER columns to insure that your record is unique - for example, if you are storing Customer records, you might use the CustomerName + TelephoneNumber + Country to insure that a specific Customer record is unique in your database. However, many people (including me) don't advocate the use of multi-column Primary Key indexes. So to insure data consistency while still give you a reliable and easy method to relate this record with others, you'd use an AutoNumber field as your "Primary Key", while still maintaining that 3 column index. YOu could then use the AN field to relate your Customer records to other data.
< the person that will use it uses that number as a reference outside of the computer for that record. (The paper record). >
In almost every case, this is a bad idea. Users (i.e. humans) expect values to be sequential, and an Access AN field is NOT guaranteed to be sequential - and it's quite common for gaps to be introduced into the AN field, due to the way Access manages the disposition of the AN fields.
<when the database actually gets to the size of 3775xxx, I don't want my MDB to try and force the new records' AN to be the same as a previous record>
This wouldn't happen anyway - Access would simply skip those values (assuming you're not dealing with a corrupt db, of course) and move to the next available number.
An AutoNumber field can be a Unique index, but the ONLY function of an AutoNumber field should be as a surrogate key. In other words, it's nothing more than a quick and easy way to "point" at a specific record. AN fields should have no "data value" - that is, it should not matter whether the database system assigns a value of 100 or 324,575. Your system (i.e. your user interface) should not really expose this number, since it is meaningless to the end user.
You should use OTHER columns to insure that your record is unique - for example, if you are storing Customer records, you might use the CustomerName + TelephoneNumber + Country to insure that a specific Customer record is unique in your database. However, many people (including me) don't advocate the use of multi-column Primary Key indexes. So to insure data consistency while still give you a reliable and easy method to relate this record with others, you'd use an AutoNumber field as your "Primary Key", while still maintaining that 3 column index. YOu could then use the AN field to relate your Customer records to other data.
< the person that will use it uses that number as a reference outside of the computer for that record. (The paper record). >
In almost every case, this is a bad idea. Users (i.e. humans) expect values to be sequential, and an Access AN field is NOT guaranteed to be sequential - and it's quite common for gaps to be introduced into the AN field, due to the way Access manages the disposition of the AN fields.
<when the database actually gets to the size of 3775xxx, I don't want my MDB to try and force the new records' AN to be the same as a previous record>
This wouldn't happen anyway - Access would simply skip those values (assuming you're not dealing with a corrupt db, of course) and move to the next available number.
ASKER