RE: About Data types

shreeseva;

The migration toolkit made no mistakes. Boolean and Autoincrement fields don't exist
is MySQL. But the following link should help;

http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html

You should leave your autoincrement to no, and then create a function (or query)
that returns the next autoincrement value when adding records. 

I use the following ...


Public Function fnNextID(TableName As String, IncrementFieldName As String) As Long
On Error GoTo LocalErr

'sample use:   MyIdField = fnNextID("NextOrderID","OrderID")

Dim sPath As String
Dim wsp As DAO.Workspace
Dim db As DAO.Database
Dim rs As DAO.Recordset

Dim CurrValue As Long
Dim NextValue As Long

Dim intCounter As Integer
Dim intTry As Integer
Dim intChoice As Integer

    sPath = fnGetUserProperty("prpBackEndPath")  'get updated path from Access property
    
    Set wsp = DBEngine.Workspaces(0)
    Set db = wsp.OpenDatabase(sPath)
    Set rs = db.OpenRecordset(TableName)

    wsp.BeginTrans  'locks table
        With rs
             .Edit
                CurrValue = rs(IncrementFieldName)
                NextValue = CurrValue + 1
                rs(IncrementFieldName) = NextValue
             rs.Update
        End With
    wsp.CommitTrans
    
    fnNextID = NextValue


ExitHere:
        rs.Close
        db.Close
        Set rs = Nothing
        Set db = Nothing
    Exit Function

LocalErr:
   Select Case Err.number
       Case 0
           GoTo ExitHere
        Case 3197 'other user attempting to change data at same time
            rs.Move 0  'refresh recordset 
            Resume
         Case 3034  'cant' commit, transaction cancelled
            Resume ExitHere
        Case -2147217887 'can't lock, try twice then return error
            intCounter = intCounter + 1
            If intCounter > 2 Then
                intChoice = MsgBox(Err.Description, _
                  vbRetryCancel + vbCritical)
                Select Case intChoice
                    Case vbRetry
                        intCounter = 1
                    Case vbCancel
                        Resume CantLock
                End Select
            End If
            DoEvents
            For intTry = 1 To 100: Next intTry  'wait a bit and try again
            Resume
       Case Else
            MsgBox "Location:   basOrders.fnNextID" & vbCrLf & _
            vbCrLf & "Reason:  " & Err.Description & vbCrLf & vbCrLf & Now, _
           vbOKOnly + vbInformation, "Exception # " & Err.number
   End Select

CantLock:
    wsp.Rollback
    Exit Function


End Function


Hope this helps.

john


-----Original Message-----
From: shreeseva [mailto:[email protected]]
Sent: Sunday, March 19, 2006 11:46 AM
To: [email protected]
Cc: [email protected]
Subject: About Data types


Dear friends,
I am using MySQL 5.0.17 on Win XP Prof. I have created a prototype database design in Ms Access 2003 and using MySQL Migration tool uploaded this to MySQL server. I have encounterred few problems with it. 
1) Migration toolkit successfully transfered all tabels and indexes but all Autonumber fields are transfered as 'int'. Also all boolean fields(In Access 'Yes/No' ) are transfered as 'tinyint'. Is there a bug the toolkit?
2) When I go for data entry through Access form It displays as '#deleted' in all fields. Why?
I am entering data from Master form and when control goes into the Subform linked with the master form it makes all entered fileds as '#deleted'. The master form is related with documents table having one field as AutoIncrement and subform is based on Transactions for that document. This Transactions table also contains one Autoincrement filed. Both such fileds are PK's also.
Now when I changed the AutoIncrement to 'No' for Documents table it goes correctly and not displayed '#deleted'. But when I entered data into Transactions table having AutoIncrement still to 'Yes' then it displayed as '#deleted'. Why?
Is this a bug or my mistake or ODBC driver problem or Access problem?

Please help
Thanks and regards.

CPK

-- 
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe:    http://lists.mysql.com/[email protected]