First, the MS Access to MySql program is impressive. I was up and running in under five minutes! I converted a small database that uses a autocounter field as the primary key. After entering the record, all fields change to #Deleted. Closing and reopening the table works OK.
I've read a little about MS Access and the problem it has with just about every SQL database, including MSSql, and understand why this occurs. So my question is, how have others solved this problem?
I've also read about using the TIMESTAMP field as a substitute for the autocounter, but can't get this to work. It seems to fail for the same reason, Access doesn't know what the value is when SQL sets it.
One workaround I've had sucess with is changing the autocounter field to a text field and using the default=rnd() in the form. This function creates a random number. Using this method does not allow data entry in the table, but that is not a problem.
A related question is; how to create incrementing numbers for tables used for PurchaseOrder or Invoice systems.
#Deleted when entering new records.
Moderator: jr
-
DougLourey
- Posts: 2
- Joined: Mon Jan 08, 2007 5:41 pm
Using MySql autonumber fields with MS Access
autonumber in MySql and MS Access tables.
I found one solution for autonumber fields.
The trick is to NOT use the autonumber field in the PRIMARY key and to add a record save command to the after-update event property of a field on the form. I placed this on the first data entry field.
You can use the wizard to create a button that saves the record, then cut/paste the command to the event.
1. Create the MySql table fields and PRIMARY key.
2. Create the MySql field [RecordID] as DOUBLE and check autonumber.
3. Create an index for [RecordID]
4. Verify that the field [RecordID] is not part of the PRIMARY key.
5. Tools - Database - Linked Table Manager (refresh link)
I found one solution for autonumber fields.
The trick is to NOT use the autonumber field in the PRIMARY key and to add a record save command to the after-update event property of a field on the form. I placed this on the first data entry field.
You can use the wizard to create a button that saves the record, then cut/paste the command to the event.
1. Create the MySql table fields and PRIMARY key.
2. Create the MySql field [RecordID] as DOUBLE and check autonumber.
3. Create an index for [RecordID]
4. Verify that the field [RecordID] is not part of the PRIMARY key.
5. Tools - Database - Linked Table Manager (refresh link)
Simple solution to the delete problem
if your autonumberfield is called "alan"
in the form before insert property use this event procedure
[alan]=dmax("[alan]",me.recordsource)+1
Thats it , job done..
the form must be based on a table or query though, not a select sttement..
in the form before insert property use this event procedure
[alan]=dmax("[alan]",me.recordsource)+1
Thats it , job done..
the form must be based on a table or query though, not a select sttement..