Link to home
Start Free TrialLog in
Avatar of jmac001
jmac001

asked on

AutoNumber ID to Generated ID

I modified an existing Access Web DB, and I just realized that I needed to generate the ID number instead of having it auto-number.  When I go to the Table Tools the only option I have in Data Type is Auto Number and in Field Validation - Indexed is grayed out is there any way to change?
Avatar of Jim Dettman (EE MVE)
Jim Dettman (EE MVE)
Flag of United States of America image

You can create another field with a number data type and index that.

Why the need to generate your own ID?

Jim.
and FYI, as you will find out, your very limited with what you can do with web databases.

That's both good and bad, but you'll find that you simply can't do a lot of things you used to do with a desktop DB.

Jim.
Avatar of jmac001
jmac001

ASKER

That is probably the way that I will have to go.  Need to generate my own so that I can make updates to the data I'm importing, what is being imported does not have any unique criteria outside of me generating an ID.  

I am finding out that there are limitation, I only used it because I am not that familar with creating events and this one had the events already in it and it was easy for me to modify for my use.  If I could've figure out how to do the events in the regular DB I would use that.
<<That is probably the way that I will have to go.  Need to generate my own so that I can make updates to the data I'm importing, what is being imported does not have any unique criteria outside of me generating an ID.  >>

 Not sure I understand that; the imported data itself already has an ID and you need to reference that?

 If not, then I don't see the difference between an autonumber and a ID you create because they both would be generated at time of import.  

 As far as the web DB, unless you really are going to put it on the web at some point, I would not use one.

 With web DB's, it's very fixed in the way a DB can be created and used.  It's only usable for the most basic of applications.

Jim.
Avatar of jmac001

ASKER

I just recently placed an ID on the imported data because I had no way of updating the data in the DB once changes occured in the spreadsheet that was being used.
Just add a number field then, index it, make it a unique index, and use that for lookup to find the row you need.

Jim.
Avatar of jmac001

ASKER

Tried that and now it I can't import into the tmp table that I have setup.  I tried it with out the numbers populated in the field that I generated the ID in and it works.  Any ideas why it wouldn't work?

The error I received was:
Microsoft Access was unable to append all the data to the table...
*if records, we lost either the record you pasted contained primary key values that already exist in the destination table. or they violate referential integrity rules for a relationship defined between tables
<<Tried that and now it I can't import into the tmp table that I have setup.  I tried it with out the numbers populated in the field that I generated the ID in and it works.  Any ideas why it wouldn't work?>>

 If you marked the index as unique, it won't allow dups.

 If you have child tables, you still need those related on the autonumber field, not your ID field.

Jim.
Avatar of jmac001

ASKER

I don't have any dups, I have tried with the table indexed  and not indexed.  Indexed the data does not go into the table at all, not indexed the data is in the table but the number is stripped from the field.
How/when are you filling the field with your ID #?

Jim.
Avatar of jmac001

ASKER

ID# generated in excel by macro, then file is imported into the tmpShipping table in Access which should capture the number in the Tracker No field.  Including sample DB and Excel
Test-WB-DB-06.04.accdb
Test-File-2013.07.03.xlsx
ASKER CERTIFIED SOLUTION
Avatar of Jim Dettman (EE MVE)
Jim Dettman (EE MVE)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of jmac001

ASKER

Jim,

Thanks so much something so obivious, working now.
Devil is always in the details as they say; can't tell you how many times I've gotten tripped up like that.

Have a great day!

Jim.