Auto Number mysql

[S.Dickson]

I had an access database that i use as an ordering system. I have a
form for entering customer details. When i add a new customer on the
form the customer number is an auto number that appears when i type in
the details.

I have just moved over to mysql server with access as the front end. I
have setup the sql tables with the customer number as autonumber.
When i go into the form and add a new customer it does not generate the

customer Number automaticaly on the form like it did before. once i
have entered all the data and saved it i went into the customer table
to see if the data was saved the auto number was in there .
Is there any way for on the form for the autonumber to be displayed
when i start entering the data. Like how it was when i used access as
the back end before i moved to mysql as the back end
Any help would be great
Simon

[Skarjune]

With Client/Server this is expected behavior. A reply to your other
post suggested a Stored Procedure, and that would be a good path from
an engineering perspective. Sounds like you want a quick fix in Access:

1. Create new macro with the following actions, saving to some name
like RecRequery:
Requery (NO control parameter)
GoToRecord with Record parameter set to Last

2. On the form in design view add a Command Button using the Toolbox.
BE SURE to have the Control Wizards enabled (magic wand icon
depressed)
In the wizard select Miscellaneous--Run Macro for category and
action
Click Next and select your macro, such as RecRequery.
Click Next and choose a Picture or type Text such as "Save Record"
Click Next and enter a name like cmdRecRequery
Click Finish

Now, when you enter data, and then click the button, Access will:
A. Save the record
B. Requery the table
C. Jump to the last record, which with an AutoNumber Key is your new
record

The drawbacks are that this won't work well if you have a lot of
records, or have a performance problem with the backend MySQL database,
or are in a busy multi-user environment. If that's the case, then some
coding or other workaround is needed.

-DHS-


S.*******@shos.co.uk wrote:

I had an access database that i use as an ordering system. I have a
form for entering customer details. When i add a new customer on the
form the customer number is an auto number that appears when i type in
the details.

I have just moved over to mysql server with access as the front end. I
have setup the sql tables with the customer number as autonumber.

When i go into the form and add a new customer it does not generate the
customer Number automaticaly on the form like it did before. once i
have entered all the data and saved it i went into the customer table
to see if the data was saved the auto number was in there .

Is there any way for on the form for the autonumber to be displayed
when i start entering the data. Like how it was when i used access as
the back end before i moved to mysql as the back end

[Jody]

After the insert you could also issue a query such as 'select @@identity
as id' to get the actual autonumber just created for your new record and
open the record with that id, or even nest it - "select * from my_table
where key_field=(select @@identity)"

Another option which we have used before with old Paradox tables was to
have a table of key id's. Each row in the table stores the next ID for
a particular table. When you want to get an ID for that table, you just
read that particular row to get the ID to use and then increment the ID
and write it back. In a busy environment you would want to lock the
table during the two operations to ensure another client doesn't get the
same ID. Then you can safely display that ID on your form and use it as
the primary key when the user saves the record. This saves inserting a
new record which you have to later delete if they cancel out of the
form.

I prefer to use auto-increment fields and I just show the ID as 'TBA'
and after doing the insert use the select @@identity query to get the ID
and display it on the form (if required)

Jody
"Skarjune" <dh*@wordimage.comwrote in
news:11**********************@k70g2000cwa.googlegr oups.com:

With Client/Server this is expected behavior. A reply to your other
post suggested a Stored Procedure, and that would be a good path from
an engineering perspective. Sounds like you want a quick fix in
Access:

1. Create new macro with the following actions, saving to some name
like RecRequery:
Requery (NO control parameter)
GoToRecord with Record parameter set to Last

2. On the form in design view add a Command Button using the Toolbox.
BE SURE to have the Control Wizards enabled (magic wand icon
depressed)
In the wizard select Miscellaneous--Run Macro for category and
action
Click Next and select your macro, such as RecRequery.
Click Next and choose a Picture or type Text such as "Save Record"
Click Next and enter a name like cmdRecRequery
Click Finish

Now, when you enter data, and then click the button, Access will:
A. Save the record
B. Requery the table
C. Jump to the last record, which with an AutoNumber Key is your new
record

The drawbacks are that this won't work well if you have a lot of
records, or have a performance problem with the backend MySQL
database, or are in a busy multi-user environment. If that's the case,
then some coding or other workaround is needed.

-DHS-

>

[Skarjune]

@@identity is a SQL Server technique that can be used with an Access
Data Project, BUT that's not what the user is doing. The problem was
using an Access frontend with a MySQL backend, and being accustomed to
seeing Access provide an Autonumber in the form--even before saving the
record.

Using a bound Access form with MySQL is not the best idea, but it will
work for the most part in a LAN workgroup situation. For example, the
form won't refresh automatically with a Save; a requery has to be
forced.

WIth MySQL, LAST_INSERT_ID() is a SQL function that will return the
value for a the most recent AUTO_INCREMENT value for a specific
connection. HOWEVER, most situations in Windows do not maintain a
connection (ODBC pooling?) so you only get back zero. To effectively
use LAST_INSERT_ID() you need to run a batch script that can maintain
the connection while using the ID for child records.

In short, simple Access forms bound to MySQL cannot be fixed in a
multi-user environment to display a new auto ID--instead a solution
needs to be coded to handle the request.

-David Hedrick Skarjune

Jody wrote:

After the insert you could also issue a query such as 'select @@identity
as id' to get the actual autonumber just created for your new record and
open the record with that id, or even nest it - "select * from my_table
where key_field=(select @@identity)"

I prefer to use auto-increment fields and I just show the ID as 'TBA'
and after doing the insert use the select @@identity query to get the ID
and display it on the form (if required)

Jody