Access with MySQL resulting Autonumber NOT auto

[Guest]

Hi,
I migrated access to MySQL and link them with odbc.
Migration successfull but all my forms that has autonumber field became Not
AUTO when I add new data to it, although I set auto increament in table at
MySQL to yes.

Is there anyway to resolve this without code that can make writing error or
conflict in multi user enviroment?

Thank you so much.

 

[Albert D. Kallal]

I don't know why it would not work.

The main change is that the autonumber in a server based system like
Sql-server, or mysql is that the autonumber id is NOT generated until the
actually record is saved (contrast that to ms-access "jet" based back ends,
and the autonumber id is generated the INSTANT you type any characters in
the form, and dirty the record).

So, there is a slight difference in behaviours that is normally not a
problem.

does the autonumber filed in the table work if you use a inset query at the
command prompt level for mysql?

With more then one free sql database offering from MS, I not had/used mysql
for some time.
So, it been a number of years, but last time I used MySql, the auotnumber
fields worked just fine.
However, the other thing to check is to ALWAYS have a auotnumber + timestamp
fields in the table, and FURTHER YOU MUST EXPOSE those fields to the given
form (so, if a form is based on a query, then make sure to add the
additional timestamp field).

So, FIRST CHECK if the auotnumber field works when you don't even use
ms-access. If it works, then try adding timestamp field to the table. I
would then delete the linked table, and then re-link. At this point, things
should work just fine. I found even my sub-forms worked just fine in this
case...

So, is the autonumber not working at all, or not being displayed until AFTER
the record is saved?

Since the auotnumber field is just another internal computing number (like
the 100's of memory segment numbers generated), your users will not see, nor
use these numbers anyway. If your existing code needs the autonubmer BEFORE
the record has been saved, then you just have to force a disk write in code,
or move that code to the after update event when at such time the autonumber
id is available..

 

[Guest]

Thank you so much. Resolved the problem as you explained. I only set default
value for the autonumber field in the form to 0 and got the autonumber field
automatically set after data inserted.
So happy for that... =))))

Cheers

 

[Guest]

Hello Albert,
I am very new to MS Access and was given a project with a MySQL backend. I
am having the same issue, but am confused on how adding the time stamp field
will alleviate the problem. I tried this and I am getting an error message
that the time stamp field is not editable, and before I added this field, I
was getting a runtime error 3155 that states an ODBC error on a linked table.
I originally created all my forms based on the AutoNumber being populated
right away, and now that this is no longer the case, I cannot get any of my
sub forms to work. Any help for this would be greatly appreciated. Thanks!
-gary

 

[Albert D. Kallal]

am confused on how adding the time stamp field
will alleviate the problem.

Because that is how ms-access knows to update the record, or in fact if the
record is updated. This problem is especially visible when you have a
sub-form, -- you often see <error>, or some other display for the sub-from.
However, by including BOTH A PRIMARY KEY id, and ALSO A TIME STAMP field,
then ms-access is able to figure out when, or if, or not if a record needs
updating. If no time stamp field is available, then field by field
comparison occurs, and for sub-forms, it creates a mess. (eg: it don't work
well, or often not at all).

I tried this and I am getting an error message
that the time stamp field is not editable

Well, you don't need to edit the timestamp field, nor actually place it on
the form, but you MUST include it as part the query for the any form (thus
both the sub-form, and main form - ditto for the primary key id that EACH
TABLE also must have in addition to the timestamp field).

I
was getting a runtime error 3155 that states an ODBC error on a linked
table.

You mean your linked tables never worked? (then, you inherited something
that don't work at all then????). When you make the changes to the mysql
table(s) to have timestamp field, I am also suggesting that you DELETE the
table link on the ms-access side, and re-link. FURTHER, DO CHECK IF THE FORM
OR SUB FORM is based on a query, and ALSO INCLUDES BOTH THE PRIMARY KEY
FIELD AND ALSO THE TIMESTAMP field.

I originally created all my forms based on the AutoNumber being populated
right away, and now that this is no longer the case

Yes, but if you as per above make sure that both the main form has a primary
key field, and ALSO a timestamp field and, also the sub-form has these two
fields + of course the field used to link back to the main form. ms-access
has ALWAYS automatic forced a disk write when the focus changes from the
main form to the sub-form (and, I think it is more clear as to why ms-access
does this!!). Anyway, you generally need both pk + timestamp exposed - so,
once again, I stress to check the data source of both form and sub-form.
And, if you add a pk or timestamp field, then delete the table link on the
ma-access side, and re-link. Again, this needs to be done/checked for both
parent, and child table. Again, check if either form is based on a query,
and thus *might* be missing the pk, or timestamp fields that you added to
fix these issues.

At this point, it not clear what you inherited, or if you *ever* succeeded
in have any table work with ms-access. Regardless, try the above, and as
mentioned, the timestamp field is used by ms-access to determine if/when/why
to update the record.

Of couse, if you do add the timestmap field, you then delete teh link, and
re-link. You also obivliery test/try editign the table direct from the table
view in ms-access. Once that works, then you can start to test/play with the
main form. Once you can edit the main form, then go after the sub-form...(do
one step at a time)...

 

[Guest]

Thank you so much for all the information! Unfortunately, and I am sorry
about this, but I am still not able to get this to work.

The original set of forms was connected to Access tables and worked
perfectly. However, after learning of the record limitations of Access
tables (32000 or so records per table), it was decided to move everything
over to MySQL. My migration went well, no errors, and I set up an ODBC link
(User DSN) to the MySQL tables, so I could link them to my forms. I was able
to successfully link all the tables, however, it did ask upon linking of each
table to select a "unique record identifier" to allow for updating and
editing my tables as well as ensure data integrity. I did this for every
table.

When I originally launched my main form, I noticed right away that my
"Order_ID" field did not populate when I made my first change/entry into the
record, as it normally did when I was linked to Access tables. Then, I
noticed when I tried to either close this form or open any connecting forms,
I would get the following error:
Run-time error '3155'
ODBC--insert on a linked table 'Orders' failed

I noticed that the table name does change when I try to run different forms,
but the run-time error is the same.

I went through my MySQL tables, and for all the data tables that had an
AutoIncrement field in them, I also added Time Stamp fields. After saving on
the SQL end, I went back into my forms, deleted all my tables, and relinked
them. I am still getting the same errors and when going into the tables, I
am not getting any data to populate. Is there something I need to set in the
field properties?

My forms have a control source of the actual table, so the TimeStamp field
is also included. The tables are not controlled by queries.

Here is an example. I have a form called Recieving, which is controlled by
the Orders table. There is a button on it to add an item to the order, that
when clicked opens an Items form (controlled by the Items table, which also
has the Order_ID field in it) - when I was using the Access tables, I could
get the Order_ID to populate immediately on the Receiving form, as it was
needed to pass to the Items form and be inserted into the items record.

Sorry again, I am new to this...
thanks!
-gary