I'm developing some code that will eventually talk to an Oracle 10g database. While doing this, I am also redesigning the relevant database tables. I'm not a database pro, and I don't have sufficient privileges on our Oracle server (even in the development database) to create and alter tables at will. Rather than constantly harass our DBA every time I discover my design is broken, I've just created an MS Access database that I can alter as I please. I'll use the access database while I develop my code, and then we will install the updated code and create the new tables at the same time.

One of the fields in my tables is an ID number that needs to be generated when a row is inserted to the table. In Oracle I'd make a trigger for this, but I have not been able to find this functionality in Access. Is it possible to achieve the same effect in any way?

  • 5
    Having different RDBMSs in dev and prod is a recipe for pain. Consider Oracle Express Edition for dev? Jan 6 '12 at 19:14
  • In general I would definitely agree, but this is a very simple little thing I'm writing. Just a piece of data reduction code in Matlab. It's not a major piece of software, and there are only two tables without much sophistication. I plan to be done with it within the afternoon. In this case, the fact that MS Access is already installed on my computer is a significant factor.
    – Colin K
    Jan 6 '12 at 19:20
  • This topic has been covered on SO, here and here.
    – onedaywhen
    Jan 9 '12 at 8:37

If the ID is simply a number look into the autonumber feature in Access.

To elaborate on this slightly, when designing the table in Access, just set the field's Data Type to AutoNumber. There are a few options you can tweak down in the Field Properties pane, but generally you can go with the defaults.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.