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?
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.
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.
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.
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.
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.
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.
Jim.
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
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.
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.
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.
Jim.
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
Test-WB-DB-06.04.accdb
Test-File-2013.07.03.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Jim,
Thanks so much something so obivious, working now.
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.
Have a great day!
Jim.
Why the need to generate your own ID?
Jim.