Trouble updating Mysql database using Access 2003 as frontend

I have installed MysqlODBC 3.51 driver on my Win XP system. I am able
to connect to the database from within Access to open, link and export
to Mysql. This all works fine. I have a database of my movies and a
table called AudioFormats which keeps the Audio Format of the DVD. The
fields I use are AudioFormatID which is autoincrement and my Primary
Key, Audio Format and Audio Format Nick. Now when I export the table
to Mysql it has not set AudioFormatID as Autoincrement. Also when
using Mysql frontend it is not allowing me to change it to
Autoincrement. What am I doing wrong?

--
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: Trouble updating Mysql database using Access 2003 as frontend

I tried doing what you describe and it all worked OK for me. The table
exported OK but the previously autoincrement field was not autoinc any
more. I used MySQLAdmin to make it autoinc.

Do you get any error when you try to make the field autoinc after the
export?

John Bonnett

-----Original Message-----
From: Barry Stear [mailto:bstear@gmail.com]=20
Sent: Thursday, 28 April 2005 3:36 AM
To: myodbc@lists.mysql.com
Subject: Trouble updating Mysql database using Access 2003 as frontend

I have installed MysqlODBC 3.51 driver on my Win XP system. I am able
to connect to the database from within Access to open, link and export
to Mysql. This all works fine. I have a database of my movies and a
table called AudioFormats which keeps the Audio Format of the DVD. The
fields I use are AudioFormatID which is autoincrement and my Primary
Key, Audio Format and Audio Format Nick. Now when I export the table
to Mysql it has not set AudioFormatID as Autoincrement. Also when
using Mysql frontend it is not allowing me to change it to
Autoincrement. What am I doing wrong?

--
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: Trouble updating Mysql database using Access 2003 as frontend

Any perticular reason of using Access 2003 as a front
end. There are many MySQL GUI which works as great as
M$ Access.

My personal favorite is SQLyog (www.webyog.com) and
there is a FREE version too.

Regards,
Ritesh


--- jbonnett@sola.com.au wrote:
> I tried doing what you describe and it all worked OK
> for me. The table
> exported OK but the previously autoincrement field
> was not autoinc any
> more. I used MySQLAdmin to make it autoinc.
>
> Do you get any error when you try to make the field
> autoinc after the
> export?
>
> John Bonnett
>
> -----Original Message-----
> From: Barry Stear [mailto:bstear@gmail.com]
> Sent: Thursday, 28 April 2005 3:36 AM
> To: myodbc@lists.mysql.com
> Subject: Trouble updating Mysql database using
> Access 2003 as frontend
>
> I have installed MysqlODBC 3.51 driver on my Win XP
> system. I am able
> to connect to the database from within Access to
> open, link and export
> to Mysql. This all works fine. I have a database of
> my movies and a
> table called AudioFormats which keeps the Audio
> Format of the DVD. The
> fields I use are AudioFormatID which is
> autoincrement and my Primary
> Key, Audio Format and Audio Format Nick. Now when I
> export the table
> to Mysql it has not set AudioFormatID as
> Autoincrement. Also when
> using Mysql frontend it is not allowing me to change
> it to
> Autoincrement. What am I doing wrong?
>
> --
> MySQL ODBC Mailing List
> For list archives: http://lists.mysql.com/myodbc
> To unsubscribe:
>
http://lists.mysql.com/myodbc?unsub=karam_chand03@yahoo.com
>
>

__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.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: Trouble updating Mysql database using Access 2003 as frontend

--Boundary_(ID_ZXr/QQLSRL9YVCktEbUoTQ)
Content-type: text/plain; charset=us-ascii
Content-transfer-encoding: 7BIT

Gents;



I have found no MySQL GUI's that work as great as ACCESS or Excel for
presentation (on screen and in print) of various charts, graphs, forms, and
reports except perhaps Crystal Reports (this as a distinct activity from
schema development & DB Management). Distribution of such reports is also
relatively painless for systems capable of using the free Access report
viewer. I regularly use Excel to display live graphs of data in MySQL
systems by executing self refreshing SQL queries in Excel against data
housed in MySQL DBs.





I suspect these capabilities may be one reason there is so much activity in
this area and so much pain when MyODBC screws the pooch.



If any of you know of a similarly capable package in open source land please
advise.



Of course, like many, I have already paid for the big MS package which
includes Office everything, Excel, & Access and thus have no desire to spend
again for Crystal Reports.





Regards;

- Peter Squillante



-----Original Message-----
From: Karam Chand [mailto:karam_chand03@yahoo.com]
Sent: Sunday, May 01, 2005 10:24 PM
To: jbonnett@sola.com.au; bstear@gmail.com; myodbc@lists.mysql.com
Subject: RE: Trouble updating Mysql database using Access 2003 as frontend



Any perticular reason of using Access 2003 as a front

end. There are many MySQL GUI which works as great as

M$ Access.



My personal favorite is SQLyog (www.webyog.com) and

there is a FREE version too.



Regards,

Ritesh





--- jbonnett@sola.com.au wrote:

> I tried doing what you describe and it all worked OK

> for me. The table

> exported OK but the previously autoincrement field

> was not autoinc any

> more. I used MySQLAdmin to make it autoinc.

>

> Do you get any error when you try to make the field

> autoinc after the

> export?

>

> John Bonnett

>

> -----Original Message-----

> From: Barry Stear [mailto:bstear@gmail.com]

> Sent: Thursday, 28 April 2005 3:36 AM

> To: myodbc@lists.mysql.com

> Subject: Trouble updating Mysql database using

> Access 2003 as frontend

>

> I have installed MysqlODBC 3.51 driver on my Win XP

> system. I am able

> to connect to the database from within Access to

> open, link and export

> to Mysql. This all works fine. I have a database of

> my movies and a

> table called AudioFormats which keeps the Audio

> Format of the DVD. The

> fields I use are AudioFormatID which is

> autoincrement and my Primary

> Key, Audio Format and Audio Format Nick. Now when I

> export the table

> to Mysql it has not set AudioFormatID as

> Autoincrement. Also when

> using Mysql frontend it is not allowing me to change

> it to

> Autoincrement. What am I doing wrong?

>

> --

> MySQL ODBC Mailing List

> For list archives: http://lists.mysql.com/myodbc

> To unsubscribe:

>

http://lists.mysql.com/myodbc?unsub=karam_chand03@yahoo.com

>

>



__________________________________________________

Do You Yahoo!?

Tired of spam? Yahoo! Mail has the best spam protection around

http://mail.yahoo.com



--

MySQL ODBC Mailing List

For list archives: http://lists.mysql.com/myodbc

To unsubscribe:
http://lists.mysql.com/myodbc?unsub=Peter.Squillante@Whitewa terMobile.com






--Boundary_(ID_ZXr/QQLSRL9YVCktEbUoTQ)--

Re: Trouble updating Mysql database using Access 2003 as frontend

Peter L. Squillante wrote:

>If any of you know of a similarly capable package in open source land please
>advise.
>
>
>
>Of course, like many, I have already paid for the big MS package which
>includes Office everything, Excel, & Access and thus have no desire to spend
>again for Crystal Reports.
>
>
I've got an open-source toolchain working in Perl.

For forms for data entry / viewing, I'm using gtk2-perl, along with
Gtk2::GladeXML and Gtk2::Ex::DBI ... that last package is mine,
available on http://entropy.homelinux.org or from cpan. Unfortunately my
website is currently down ( issues getting apache starting after a
mod_php upgrade ), so if you want to take a look now, cpan is it.

Anyway, you build your GUI in Glade2, which is quite painless, and has
far more widgets than MS Access ( looks better too ). You then use
Gtk2::Ex::DBI to bind the widgets on your form to a database query, much
the same as setting the recordset property in MS Access. Gtk2::Ex::DBI
has methods for moving between records, inserting and deleting data and
the usual things you need. I'm yet to do a 'datasheet' widget, but there
are others talking about doing it in the Gtk2-Perl mailing lists ... if
they don't do it then I will soon.

For reports, I've just released PDF::ReportWriter, also at
http://entropy.homelinux.org ... which again is offline at the moment
.... but also available via cpan. It's not *quite* as easy to use as
Access reports, but when I stabalise how the thing actually works, I'll
look at moving to using an XML report definition. It uses PDF::API2 to
render directly to PDF, and intelligently handles new pages, group
headers & footers etc.

Also, if you're wondering, the whole system works remarkably well under
Windows, though of course Gtk2 runs better under Linux.

--
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: Trouble updating Mysql database using Access 2003 as frontend

I exported the information again and it brought the Autoincrement
field over and made it Autoincrement. But at first it did work like
you said and didn't allow me to change the field to Autoinc from
within MysqlFrontend. I do however have another problem/issue. When I
exported my table over Mysql made Date/Time field within Access an
Timestamp field. I changed it over to Date within Mysql, my question
is some of the dates .. < 1970's would be all 0's .. while other dates
came over. Is there an fix for this? Also I am unable to input dates
into the linked table within Access. I get error 'SQL Data Type out of
range #0' . I am assuming it is because the date is in the format
yyyy/mm/dd in Mysql but the table that I exported the data and Fields
from it was set as mm/dd/yy and Short Date as the view. You know what
I can do to fix this?

On 5/1/05, jbonnett@sola.com.au wrote:
> I tried doing what you describe and it all worked OK for me. The table
> exported OK but the previously autoincrement field was not autoinc any
> more. I used MySQLAdmin to make it autoinc.
>=20
> Do you get any error when you try to make the field autoinc after the
> export?
>=20
> John Bonnett
>=20
> -----Original Message-----
> From: Barry Stear [mailto:bstear@gmail.com]
> Sent: Thursday, 28 April 2005 3:36 AM
> To: myodbc@lists.mysql.com
> Subject: Trouble updating Mysql database using Access 2003 as frontend
>=20
> I have installed MysqlODBC 3.51 driver on my Win XP system. I am able
> to connect to the database from within Access to open, link and export
> to Mysql. This all works fine. I have a database of my movies and a
> table called AudioFormats which keeps the Audio Format of the DVD. The
> fields I use are AudioFormatID which is autoincrement and my Primary
> Key, Audio Format and Audio Format Nick. Now when I export the table
> to Mysql it has not set AudioFormatID as Autoincrement. Also when
> using Mysql frontend it is not allowing me to change it to
> Autoincrement. What am I doing wrong?
>

--
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: Trouble updating Mysql database using Access 2003 as frontend

Barry Stear wrote:

>I exported the information again and it brought the Autoincrement
>field over and made it Autoincrement. But at first it did work like
>you said and didn't allow me to change the field to Autoinc from
>within MysqlFrontend. I do however have another problem/issue. When I
>exported my table over Mysql made Date/Time field within Access an
>Timestamp field. I changed it over to Date within Mysql, my question
>is some of the dates .. < 1970's would be all 0's .. while other dates
>came over. Is there an fix for this?
>
It's difficult to say what's actually happened to your data now. If your
date field was changed to a timestamp, you can't really rely on your
data, as a timestamp field gets updated whenever you update a record. If
you haven't edited anything, your data *may* be OK, but as you say, some
of the dates ( before 1970 ) didn't come over anyway.

If I were you, I'd stop using MysqlFrontend or whatever it was that did
the conversion. Automatic migration tools are all very well when they
work, but as your example shows, they don't always work. You are far
better off using something like MySQL Administrator ( from the MySQL
website ) to set up your tables and then use Access to copy the data
across ( append queries ). If you've decided to use MySQL, you should
make yourself familiar with tools like Administrator and Query Browser,
and the 'mysql' console client. It's no good having your data in MySQL
but not knowing how to use these tools. And of course if something goes
wrong, you've only got yourself to blame, rather than some third-party tool.

> Also I am unable to input dates
>into the linked table within Access. I get error 'SQL Data Type out of
>range #0' . I am assuming it is because the date is in the format
>yyyy/mm/dd in Mysql but the table that I exported the data and Fields
>from it was set as mm/dd/yy and Short Date as the view.
>
No. The ODBC driver will handle converting the date format for you, and
will use Windows 'regional settings' to determine what format to use.
This error is most likely because you're trying to edit a field of the
wrong type. Did you relink the table after changing the timestamp field
to a date field? You have to relink tables after each change to the table.

--
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: Trouble updating Mysql database using Access 2003 as frontend

I could add to that. I use Access for "quick and dirty" stuff. It is
very good for that, but for more serious stuff in Windows I use VB or
C#. I use VBMySQLDirect with VB and the .NET Connector with C#. I used
to be quite good with Access but I have found real development languages
much more flexible and no more difficult, once you get used to it.

John Bonnett

-----Original Message-----
From: Daniel Kasak [mailto:dkasak@nusconsulting.com.au]=20
Sent: Monday, 2 May 2005 1:47 PM
To: Peter.Squillante@WhitewaterMobile.com; myodbc@lists.mysql.com
Subject: Re: Trouble updating Mysql database using Access 2003 as
frontend

Peter L. Squillante wrote:

>If any of you know of a similarly capable package in open source land
please
>advise.
>
>=20
>
>Of course, like many, I have already paid for the big MS package which
>includes Office everything, Excel, & Access and thus have no desire to
spend
>again for Crystal Reports.
> =20
>
I've got an open-source toolchain working in Perl.

For forms for data entry / viewing, I'm using gtk2-perl, along with
Gtk2::GladeXML and Gtk2::Ex::DBI ... that last package is mine,
available on http://entropy.homelinux.org or from cpan. Unfortunately my
website is currently down ( issues getting apache starting after a
mod_php upgrade ), so if you want to take a look now, cpan is it.

Anyway, you build your GUI in Glade2, which is quite painless, and has
far more widgets than MS Access ( looks better too ). You then use
Gtk2::Ex::DBI to bind the widgets on your form to a database query, much
the same as setting the recordset property in MS Access. Gtk2::Ex::DBI
has methods for moving between records, inserting and deleting data and
the usual things you need. I'm yet to do a 'datasheet' widget, but there
are others talking about doing it in the Gtk2-Perl mailing lists ... if
they don't do it then I will soon.

For reports, I've just released PDF::ReportWriter, also at
http://entropy.homelinux.org ... which again is offline at the moment
.... but also available via cpan. It's not *quite* as easy to use as
Access reports, but when I stabalise how the thing actually works, I'll
look at moving to using an XML report definition. It uses PDF::API2 to
render directly to PDF, and intelligently handles new pages, group
headers & footers etc.

Also, if you're wondering, the whole system works remarkably well under
Windows, though of course Gtk2 runs better under Linux.

--=20
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=3Dgcdmo-myodbc@m.gmane.o rg

Re: Trouble updating Mysql database using Access 2003 as frontend

I figured out how to get around the error. But I am not sure how you
got that formatting for Mysql. My Mysql database puts the date in
YYYY/MM/DD, not DD/MM/YYYY . Well the fix was to make the field
'Date'. I believe it may have to do with my Input Mask from Access.
But if I enter an date in Access as 04/05/05 . It will be 2004/05/05
in Mysql which works for me. I just have to remember that it will be
input into Mysql as YY/MM/DD. Now if only I could find a way to be
able to put it in format MM/DD/YY and Mysql put it in correctly. Oh
well at least I can use it now :-).

Barry

On 5/17/05, jbonnett@sola.com.au wrote:
>=20
>=20
> I ran the script using MySQLQuery and the table got created OK. I linked =
to
> it from Access. It lets me update the birthdate OK too; I changed 1937 in
> actor 9 to 1938 and back again. I tried inserting a record (10) and when =
I
> moved off the row all fields changed to "#Deleted". I then went to design
> view to check the field definitions. When I changed back to data view,
> record 10 was there as I had entered it and the ID had been incremented.
>=20
> =20
>=20
>=20
> actors
>=20
>=20
> ActorID
>=20
> LastName
>=20
> FirstName
>=20
> Birthdate
>=20
>=20
> 1
>=20
> Judd
>=20
> Ashley
>=20
> 19/04/1968
>=20
>=20
> 2
>=20
> Garner
>=20
> Jennifer
>=20
> 17/04/1972
>=20
>=20
> 3
>=20
> Jones
>=20
> Tommy Lee
>=20
> 15/09/1946
>=20
>=20
> 4
>=20
> Carter
>=20
> Linda
>=20
> 24/07/1951
>=20
>=20
> 5
>=20
> Jackson
>=20
> Samuel L.
>=20
> 21/12/1948
>=20
>=20
> 6
>=20
> Paxton
>=20
> Bill
>=20
> 17/05/1955
>=20
>=20
> 7
>=20
> Barrymore
>=20
> Drew
>=20
> 22/02/1975
>=20
>=20
> 8
>=20
> Vartan
>=20
> Michael
>=20
> 27/11/1968
>=20
>=20
> 9
>=20
> Freeman
>=20
> Morgan
>=20
> 1/06/1937
>=20
>=20
> 10
>=20
> Nurk
>=20
> Fred
>=20
> 18/09/1946
>=20
> =20
>=20
> I tried entering another record and the same thing happened. I tried
> Records/Refresh but that did not fix it. I closed the table and opened it
> again and that showed the new record correctly. I rarely enter records th=
at
> way so I have not seen that "#Deleted" thing much. It has been discussed =
to
> death on the mailing list in the past. I think the solution to that
> behaviour is to add a TimeStamp column to the table. I have never done th=
at
> because it has never bothered me.
>=20
> =20
>=20
> I don't know what your error means except that some value that Access is
> passing to ODBC is probably out of range. The "(#0)" may mean that the va=
lue
> being passed is zero (perhaps as the ActorID?) or perhaps it means the fi=
rst
> parameter (the data values are passed as parameters in the ODBC interface=
I
> believe).
>=20
> =20
>=20
> From my own experience I have found that relying on Microsoft software to
> work nicely with non-Microsoft software is rather futile. It may work to
> some extent but is sure to have "quirks". Talking pure SQL to the server =
and
> managing everything else yourself seems to work best. That's mainly why I
> stopped writing applications in Access and went to VB. Access only works
> nicely with Jet databases and SQL Server.
>=20
> =20
>=20
> John
>=20
> =20
>=20
> -----Original Message-----
> From: Barry Stear [mailto:bstear@gmail.com]=20
> Sent: Wednesday, 18 May 2005 2:51 AM
> To: John Bonnett, R&D Australia
> Subject: Re: Trouble updating Mysql database using Access 2003 as fronten=
d
> Sent: Wednesday, 18 May 2005 2:51 AM
> To: John Bonnett, R&D Australia
> Subject: Re: Trouble updating Mysql database using Access 2003 as fronten=
d
>=20
>=20
>=20
> =20
>=20
> The error I get is "[Microsoft][ODBC Driver Manager] SQL Data Type out
>=20
> of range (#0)"
>=20
> =20
>=20
> I have attached the table in .SQL . Try linking to it from Access and
>=20
> updating/adding an record. One thing I did notice is that it doesn't
>=20
> Autoincrement the ActorID field. If I go into the database in Mysql
>=20
> the ActorID gets updated once I am done with the record it is added.
>=20
> =20
>=20
> Barry
>=20
> =20
>=20
> On 5/16/05, jbonnett@sola.com.au wrote:
>=20
> > What is the trouble you are having? What is the error message you are
>=20
> > getting? I just tried updating a date via ODBC here and it worked fine.
>=20
> > I used MS Access 2003 and I linked to an existing table in a MySQL
>=20
> > (5.0.3-beta-nt) database on my local machine. The table definition is a=
s
>=20
> > follows (SHOW CREATE TABLE)
>=20
> >=20
>=20
> > CREATE TABLE `casts` (
>=20
> > `ID` int(11) NOT NULL auto_increment,
>=20
> > `MouldID` int(11) NOT NULL default '0',
>=20
> > `CastingNumber` smallint(6) NOT NULL default '0',
>=20
> > `CastDate` datetime default NULL,
>=20
> > `CastMeasureDate` datetime default NULL,
>=20
> > `Coated` tinyint(4) default NULL,
>=20
> > `UserID` int(11) default NULL,
>=20
> > `TestBatchID` int(10) unsigned default NULL,
>=20
> > PRIMARY KEY (`ID`),
>=20
> > KEY `MouldIDX` (`MouldID`)
>=20
> > ) ENGINE=3DMyISAM DEFAULT CHARSET=3Dlatin1
>=20
> >=20
>=20
> > I was able to alter the date fields shown above from a datasheet view o=
f
>=20
> > the table. I am using MyODBC-3.51.11-2. I know it is important for the
>=20
> > table to have a primary key defined.
>=20
> >=20
>=20
> > You can definitely interface to the database from VB. That's how the
>=20
> > data got into the above table in the first place.
>=20
> >=20
>=20
> > John
>=20
> >=20
>=20
> > -----Original Message-----
>=20
> > From: Barry Stear [mailto:bstear@gmail.com]
>=20
> > Sent: Tuesday, 17 May 2005 8:34 AM
>=20
> > To: John Bonnett, R&D Australia
>=20
> > Subject: Re: Trouble updating Mysql database using Access 2003 as
>=20
> > frontend
>=20
> >=20
>=20
> > Still having problems with the Date field. I have borrowed an book for
>=20
> > Visual Basic from the library. I see how you can make an interface for
>=20
> > an database using VB. You have any ideas how I could get around this
>=20
> > problem. Maybe I should submit my question to another forum ? ?
>=20
> >=20
>=20
> > On 5/4/05, Barry Stear wrote:
>=20
> > > Well I am new to Mysql so I don't know what the best column types are=
..
>=20
> > > I am just keeping an database of my DVDs and VHS tapes. The database
>=20
> > > is based on an database from Microsofts site that you can download. =
I
>=20
> > > do have some other databases that I am going to move over to Mysql so
>=20
> > > I can interface with them through an browser. But I would like to try
>=20
> > > and figure out why I can't update the date. If I manually go into
>=20
> > > Mysql and input it there is no problem. However from Access to Mysql
>=20
> > > is where the problem happens.
>=20
> > >
>=20
> > > Thanks for taking time to help me out. I really appreciate it . I am
>=20
> > > in the learning curve here :). But like anything, you have to start
>=20
> > > somewhere.
>=20
> > >
>=20
> > > Barry
>=20
> > >
>=20
> > > On 5/4/05, jbonnett@sola.com.au wrote:
>=20
> > > > Just a few comments on the SQL you sent me.
>=20
> > > >
>=20
> > > > I mostly use long integers for my primary keys but what you have is
>=20
> > > > fine. You are obviously not expecting many actors in your database.
>=20
> > > >
>=20
> > > > You have made your LastName and FirstName fields Char, which makes
>=20
> > them
>=20
> > > > fixed size fields. Unless you are expecting that they will mostly b=
e
>=20
> > > > that big you will save space by making them VarChar. You saved some
>=20
> > > > space by having a small primary key but probably wasted a lot more
>=20
> > by
>=20
> > > > using fixed character fields.
>=20
> > > >
>=20
> > > > Having "default NULL" on Birthdate is redundant. NULL is the defaul=
t
>=20
> > you
>=20
> > > > get if you do not specify a default.
>=20
> > > >
>=20
> > > > Nothing is wrong here of course; it's just not the way I would do
>=20
> > it.
>=20
> > > >
>=20
> > > > John
>=20
> > > >
>=20
> > > > -----Original Message-----
>=20
> > > > From: Barry Stear [mailto:bstear@gmail.com]
>=20
> > > > Sent: Thursday, 5 May 2005 9:12 AM
>=20
> > > > To: John Bonnett, R&D Australia
>=20
> > > > Subject: Re: Trouble updating Mysql database using Access 2003 as
>=20
> > > > frontend
>=20
> > > >
>=20
> > > > I attached the .sql file containing structure I created within
>=20
> > Mysql.
>=20
> > > > The data structure within Access is : ActorID (Primary Key and
>=20
> > > > Autoinc), Lastname (text/indexed), Firstname (text), and Birthdate
>=20
> > > > (Date/Time). I can include the database structure that comes over
>=20
> > when
>=20
> > > > I export the table to Mysql if needed. I couldn't find a way to fin=
d
>=20
> > > > the commands that were coming over through Myodbc when I try
>=20
> > updating
>=20
> > > > the date within the database through Access.
>=20
> > > >
>=20
> > > > Barry
>=20
> > > >
>=20
> > > >
>=20
> > >
>=20
> >

--
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: Trouble updating Mysql database using Access 2003 as frontend

Barry Stear wrote:

>I figured out how to get around the error. But I am not sure how you
>got that formatting for Mysql. My Mysql database puts the date in
>YYYY/MM/DD, not DD/MM/YYYY . Well the fix was to make the field
>'Date'. I believe it may have to do with my Input Mask from Access.
>But if I enter an date in Access as 04/05/05 . It will be 2004/05/05
>in Mysql which works for me. I just have to remember that it will be
>input into Mysql as YY/MM/DD. Now if only I could find a way to be
>able to put it in format MM/DD/YY and Mysql put it in correctly. Oh
>well at least I can use it now :-).
>
>Barry
>
>
Use the 'Regional Settings' tool in the Windows control panel to set up
your default date formatting. That's where Access takes it's formatting
from.

--
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: Trouble updating Mysql database using Access 2003 as frontend

Daniel my settings there are fine. I am saying that once I enter the
date using Access to Mysql after Mysql changes it over, if I open up
the table again the format is YYYY/MM/DD . There is no way for me to
change that over. IF I just work in Access without exporting or
linking to Mysql the dates are fine.

Latez

On 5/18/05, Daniel Kasak wrote:
> Barry Stear wrote:
>=20
> >I figured out how to get around the error. But I am not sure how you
> >got that formatting for Mysql. My Mysql database puts the date in
> >YYYY/MM/DD, not DD/MM/YYYY . Well the fix was to make the field
> >'Date'. I believe it may have to do with my Input Mask from Access.
> >But if I enter an date in Access as 04/05/05 . It will be 2004/05/05
> >in Mysql which works for me. I just have to remember that it will be
> >input into Mysql as YY/MM/DD. Now if only I could find a way to be
> >able to put it in format MM/DD/YY and Mysql put it in correctly. Oh
> >well at least I can use it now :-).
> >
> >Barry
> >
> >
> Use the 'Regional Settings' tool in the Windows control panel to set up
> your default date formatting. That's where Access takes it's formatting
> from.
>=20
> --
> 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=3Dgcdmo-myodbc@m.gmane.o rg

Re: Trouble updating Mysql database using Access 2003 as frontend

Yes once I export the table into MySQL and then open the table in
Access the date is YYYY/MM/DD just like how Mysql stores it. I
updated MyODBC to 3.51.11-2 and WaLA!!!! The dates show up in Access
as MM/DD/YYYY, JUST LIKE I WANT, and they MySql updates them fine. You
the man daniel. I think someone suggested doing that before but there
wasn't an 3.51.11-2 version of MyODBC for Windows or something. I
don't know. Its working now and thats what I want. Thanks Daniel for
the suggestion. Now I will try running Queries on it and make sure
that works.

Thanks again...

Barry

On 5/18/05, Daniel Kasak wrote:
> Barry Stear wrote:
>=20
> >Daniel my settings there are fine. I am saying that once I enter the
> >date using Access to Mysql after Mysql changes it over, if I open up
> >the table again the format is YYYY/MM/DD .
> >
> ?
> You mean you open the table in *Access* and the dates are yyyy/mm/dd?
> That shouldn't happen. That means that Access doesn't recognise the
> fields as date fields and is instead treating them as text fields.
> Queries will *NOT* work properly in this case. If this *is* the case,
> upgrade to MyODBC-3.51.11-2 and relink your tables in Access.
>=20
> --
> 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=3Dgcdmo-myodbc@m.gmane.o rg