By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,573 Members | 3,008 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,573 IT Pros & Developers. It's quick & easy.

How to create and retrieve an autonumber field

bartonc
Expert 5K+
P: 6,596
I just connected to the JET engine for the first time today using the Access ODBC connector and need some basics.
I'm not even sure that Access (front end) experts are versed in SQL, but here goes:

On a MySQL or SQLite3 DB I'd use:
Expand|Select|Wrap|Line Numbers
  1. CREATE TABLE `agencies` (
  2.   `agencyID` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT);
Then, after an INSERT, I'd get the value assigned by the DB as:
Expand|Select|Wrap|Line Numbers
  1. SELECT last_insert_id()
I haven't been able to get the syntax right on the AUTONUMBER column (which is also a primary key) and have no clue as to retrieving the most recent insert ID reliably.

I'm also looking for a better on-line reference than Microsoft Jet SQL Reference.

Thanks.
Oct 14 '07 #1
Share this Question
Share on Google+
12 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
OK for the first part. The syntax to create a table with an autonumber primary key is as follows:

Expand|Select|Wrap|Line Numbers
  1. CREATE TABLE agencies (agencyID COUNTER PRIMARY KEY)
  2.  
You don't need the single quotes and COUNTER will denote an autonumber (and yes I know it doesn't make any sense). Because the autonumber is automatically not null you don't need that.

Mary
Oct 14 '07 #2

MMcCarthy
Expert Mod 10K+
P: 14,534
For reference purposes you might find this a little better.

http://support.microsoft.com/ph/2509

We're always here to help though.

One of the things you need to know is the JET Engine will impose its own sort order on storing records so the only way to retrieve the latest record is by selecting the MAX of the autonumber primary key.

Expand|Select|Wrap|Line Numbers
  1. SELECT Max(agencyid) FROM agencies
This should retreive the appropriate record.
Oct 14 '07 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
I'm not even sure that Access (front end) experts are versed in SQL, but here goes:
We have to be generally as the Access Query creator is limited. Actually the syntax it uses for create table is very different. Something like ...

SELECT Column1, Column2 INTO NewTable;

However as you can see this is very limiting syntax wise. The Jet engine will support a SQL Create Table query though. It defines it as a "Data Definition Query". The syntax will vary slightly from what you are used to though.
Oct 14 '07 #4

MMcCarthy
Expert Mod 10K+
P: 14,534
One more thing.

In Access you can actually bypass the JET Engine by using Pass Through queries.
Oct 14 '07 #5

bartonc
Expert 5K+
P: 6,596
Sheesh... Now I see how your get you post count so high

One more thing.

In Access you can actually bypass the JET Engine by using Pass Through queries.
Except that I'm not using Access. I create the front end from scratch.
We have to be generally as the Access Query creator is limited. Actually the syntax it uses for create table is very different. Something like ...

SELECT Column1, Column2 INTO NewTable;

However as you can see this is very limiting syntax wise
Good to know...
Yep. Saw that (what a mess). I've got to stay compatible with the original implementation AND SQLite (what fun - see my post in Software Dev Forum).
Expand|Select|Wrap|Line Numbers
  1. SELECT Max(agencyid) FROM agencies
This should retreive the appropriate record.
That is, unless (COUNTER implies not) empty slots get reused when the limit of the counter is reached (as in real RBDMS).
Expand|Select|Wrap|Line Numbers
  1. CREATE TABLE agencies (agencyID COUNTER PRIMARY KEY)
  2.  
That's what I was looking for! Thank you.
You don't need the single quotes and COUNTER will denote an autonumber (and yes I know it doesn't make any sense). Because the autonumber is automatically not null you don't need that.
Yes, it makes no sense.
I don't actually write any SQL (per se). I'll need to run over the Query Browser generated code with a converter unless there's a nifty MySQL data importer like SQLite3 has.

That was hard. Thank you very much, Mary. I'll be in touch, I'm sure.
Oct 14 '07 #6

MMcCarthy
Expert Mod 10K+
P: 14,534
Sheesh... call me a posthog, why don't you. :)

There are no built in tools for MySQL only MS SQL. However, depending on what you are doing you may want to look at investing in this tool MySQL-to-Access. However, I've never used it and not sure how or if it works with queries.
Oct 14 '07 #7

MMcCarthy
Expert Mod 10K+
P: 14,534
That is, unless (COUNTER implies not) empty slots get reused when the limit of the counter is reached (as in real RBDMS).
No Autonumber doesn't allow you to reuse empty slots. Access will reach its limit before the counter does.

Make any more snide comments about real RDBMS and I won't be responsible for my actions. :D
Oct 14 '07 #8

bartonc
Expert 5K+
P: 6,596
Sheesh... call me a posthog, why don't you. :)

There are no built in tools for MySQL only MS SQL. However, depending on what you are doing you may want to look at investing in this tool MySQL-to-Access. However, I've never used it and not sure how or if it works with queries.
Thanks, Mary. That looks like just the ticket.

So, you're saying that Access is a real RBDMS???
Oct 14 '07 #9

MMcCarthy
Expert Mod 10K+
P: 14,534
Thanks, Mary. That looks like just the ticket.

So, you're saying that Access is a real RBDMS???
OK so they adapted the rules a little but you can still apply Normal Form. And in the end of the day that's what it's all about.
Oct 14 '07 #10

bartonc
Expert 5K+
P: 6,596
OK so they adapted the rules a little but you can still apply Normal Form. And in the end of the day that's what it's all about.
True enough! I guess that I get caught-up in all that multi-user, network/DB admin stuff that comes along with an enterprise-style installation.

I sure do appreciate your input on these issues.
Thank you,
Barton
Oct 14 '07 #11

NeoPa
Expert Mod 15k+
P: 31,494
I saw this was you Barton, so I jumped in and... Mary had already answered your question AND had a discussion with you about the various aspects.
Come back any time you need more help.
Oct 14 '07 #12

bartonc
Expert 5K+
P: 6,596
I saw this was you Barton, so I jumped in and... Mary had already answered your question AND had a discussion with you about the various aspects.
Come back any time you need more help.
Thanks, NeoPa. I'll do that.
Oct 14 '07 #13

Post your reply

Sign in to post your reply or Sign up for a free account.