About Data types

am 19.03.2006 20:45:40 von C K

------=_NextPart_000_0049_01C64BBB.CDA83BC0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

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
------=_NextPart_000_0049_01C64BBB.CDA83BC0--

RE: About Data types

am 20.03.2006 00:07:13 von jbonnett

There is no specific autonumber field type in MySQL so 'Int' is what you
want with the autoinc property set. Also MySQL has no Boolean type so
tinyint is probably the best alternative.

The problem with #deleted is usually solved by including a timestamp
column in each table, although I have never needed to do that. Make sure
all tables have a primary key defined.

John B.

-----Original Message-----
From: shreeseva [mailto:shreeseva.it@gmail.com]=20
Sent: Monday, 20 March 2006 6:16 AM
To: win32@lists.mysql.com
Cc: myodbc@lists.mysql.com
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 ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe: http://lists.mysql.com/myodbc?unsub=3Dgcdmo-myodbc@m.gmane.o rg

RE: About Data types

am 20.03.2006 02:40:12 von Lawson Cronlund

Depending on how many records you anticipate in your table, you might want
to use "BIGINT" for the field type for your primary key. Also, "BIT" is
defined as a single bit field. I think I saw that it's implemented as a
byte so you wouldn't save any storage in this implementation of MySQL.
Also, by leaving it as TINYINT, you'll eliminate a manual edit of the
resulting imported table.

Regards.

Lawson Cronlund
lawson@vrtinc.com
+1(480)308-0641 (Voice)
+1(602)996-0376 (Fax)

-----Original Message-----
From: jbonnett@sola.com.au [mailto:jbonnett@sola.com.au]
Sent: Sunday, March 19, 2006 4:07 PM
To: shreeseva.it@gmail.com; win32@lists.mysql.com
Cc: myodbc@lists.mysql.com
Subject: RE: About Data types

There is no specific autonumber field type in MySQL so 'Int' is what you
want with the autoinc property set. Also MySQL has no Boolean type so
tinyint is probably the best alternative.

The problem with #deleted is usually solved by including a timestamp
column in each table, although I have never needed to do that. Make sure
all tables have a primary key defined.

John B.

-----Original Message-----
From: shreeseva [mailto:shreeseva.it@gmail.com]
Sent: Monday, 20 March 2006 6:16 AM
To: win32@lists.mysql.com
Cc: myodbc@lists.mysql.com
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 ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe: http://lists.mysql.com/myodbc?unsub=lawson@vrtinc.com

--
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe: http://lists.mysql.com/myodbc?unsub=gcdmo-myodbc@m.gmane.org

RE: About Data types

am 20.03.2006 02:40:12 von Lawson Cronlund

Re: About Data types

am 20.03.2006 02:55:21 von Daniel Kasak

Lawson Cronlund wrote:
> Depending on how many records you anticipate in your table, you might want
> to use "BIGINT" for the field type for your primary key.

You can only do this if you're not planning on using Access as the
front-end. Access can't handle BIGINT fields.

--
Daniel Kasak
IT Developer
NUS Consulting Group
Level 5, 77 Pacific Highway
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
email: dkasak@nusconsulting.com.au
website: http://www.nusconsulting.com.au

--
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe: http://lists.mysql.com/myodbc?unsub=gcdmo-myodbc@m.gmane.org

Re: About Data types

am 20.03.2006 02:55:21 von Daniel Kasak

RE: About Data types

am 20.03.2006 17:14:55 von John Theroux

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-incremen t.html

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

I use the following ...

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

'sample use: MyIdField =3D 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 =3D fnGetUserProperty("prpBackEndPath") 'get updated path =
from Access property
=20
Set wsp =3D DBEngine.Workspaces(0)
Set db =3D wsp.OpenDatabase(sPath)
Set rs =3D db.OpenRecordset(TableName)

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

ExitHere:
rs.Close
db.Close
Set rs =3D Nothing
Set db =3D 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=20
Resume
Case 3034 'cant' commit, transaction cancelled
Resume ExitHere
Case -2147217887 'can't lock, try twice then return error
intCounter =3D intCounter + 1
If intCounter > 2 Then
intChoice =3D MsgBox(Err.Description, _
vbRetryCancel + vbCritical)
Select Case intChoice
Case vbRetry
intCounter =3D 1
Case vbCancel
Resume CantLock
End Select
End If
DoEvents
For intTry =3D 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:shreeseva.it@gmail.com]
Sent: Sunday, March 19, 2006 11:46 AM
To: win32@lists.mysql.com
Cc: myodbc@lists.mysql.com
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/win32?unsub=3Dgcdmw-win32@m.gmane.org