Decimal precision change - MySQL & MS-ACCESS

[MomentisMan]

I have an app with a MySQL backend and MS-Access frontend. I have a
table on the database (tblTime) with three Decimal columns. When I
connect to the table through ODBC in Access, the precision comes
through correct (13,2). I created a view of a subset of the data from
the tblTime table. However, when I connect to this view through
Access, the precision of the decimal columns keeps setting to (14,0),
which results in a data truncation error. Does anyone have an idea how

to correct this?

 

[Albert D. Kallal]

Hum, I not seen this.

You not getting any answers here, since no one here has experienced the
problem (20,000+ questions are answered in just the ms-access newsgroup
alone in one month..and a well worded question is answered in LESS THEN one
hour).

Since 1 hour has gone by...obviously no one has seen your problem....

I created a view of a subset of the data from
the tblTime table.

Do you mean a server side view, or a "select query" in the query builder on
the ms-access side?

If you are talking about linking to a view on the server side, then try
deleting the view...and re-creating it ANY TIME you make changes tot eh base
table on the server side....

If the original table as linked comes out ok..try building the "view" on the
ms-access side.

If the server side view don't work, then it sounds like a odbc issue. You
might try the MySql newsgroups, and see what they come up with....

It has been a few years since I used the odbc drivers that ship with MySql,
but since ms-access, vb6, vb.net, and all windows applications use the same
code base, then this problem would not be limited to ms-access. (thus my
suggestion to check the Mysql newsgroups..and see what they come up with).
Virtually any windows application should thus be experiencing this problem.

I would guess/suspect that you do have to keep your field sizes and
definitions within "equivalents" that ms-access supports. If you reduce the
precision size (say only 10 digits + 2 decimal...does that help??). (and,
remember when you do/test this, to delete the table and view links..and
re-link).

 

[David W. Fenton]

If you are talking about linking to a view on the server side,
then try deleting the view...and re-creating it ANY TIME you make
changes tot eh base table on the server side....

MySQL does not have views or stored procedures.

 

[David W. Fenton]

It has been a few years since I used the odbc drivers that ship
with MySql, but since ms-access, vb6, vb.net, and all windows
applications use the same code base, then this problem would not
be limited to ms-access.

Huh? Same codebase? What does that mean? Yes, the parent ODBC system
is all the same, but each ODBC driver is internally completely
different, designed to serve a particular data source.

My suggestion would be to delete the table link and recreate it. My
limited experience with Access/MySQL is that table links are
invalidated by any structural change to the source table, but that
could be only when adding columns, not altering their data types.
You might have luck editing the import spec that was created when
you created the table link. Unfortunately, there is no UI for doing
this, except to trick Access into it, and this requires pretending
that you're going to import or export data. It's more obvious with
an export, but in an import, the subform with the field list just
has the columns hidden. However, it does occur to me that I don't
know that there's any place to edit decimal precision -- I've been
doing a lot of messing around with import specs the last week, but
didn't have any decimal fields, so maybe just didn't notice.

But that's where I'd start, with deleting and recreating the link,
then editing the import spec. BTW, each time you relink, the import
spec is recreated and given a numerically incremented number, so you
might want to delete all the import specs for this table before you
relink so that you can be sure you're editing the right import spec.

You might want to just manually examine the import spec. You can
view it by looking at two system tables, MSysIMEXSpecs and
SysIMEXColumns. The structural relationship between the two should
be obvious.

 

[Albert D. Kallal]

MySQL does not have views or stored procedures.

Actually, it does now.....

http://dev.mysql.com/doc/refman/5.0/en/views.html

 

[David W. Fenton]

Actually, it does now.....

http://dev.mysql.com/doc/refman/5.0/en/views.html

You're right -- I'm stuck working with MySQL 4.x installations.

I never understood how MySQL became so widely used when it lacked so
many basic features that server databases have had for a very, very
long time (and that Access had way back in version 1!).

 

[Albert D. Kallal]

I never understood how MySQL became so widely used when it lacked so

many basic features that server databases have had for a very, very
long time (and that Access had way back in version 1!).

well, dbaseII, dbaseIII was really popular on a pc, and for basic
tables...etc., it was fast and easy...

I think MySql is much the same...fast, easy to install, easy to use. For so
many web sites etc, when you
just need a table, and not a lot of relational stuff...it really is the
ticket. Cheap and simple wins the day.

I not followed mysql for the last few years, but I believe that the innoDb
extensions were incorporated into the basic install. Thus the
expected features such as referential enforced and views are now
available....

 

[David W. Fenton]

well, dbaseII, dbaseIII was really popular on a pc, and for basic
tables...etc., it was fast and easy...

I think MySql is much the same...fast, easy to install, easy to
use. For so many web sites etc, when you
just need a table, and not a lot of relational stuff...it really
is the ticket. Cheap and simple wins the day.

But PostgreSQL has provided more functionality and equal performance
at the same price for many years.

I not followed mysql for the last few years, but I believe that
the innoDb extensions were incorporated into the basic install.
Thus the expected features such as referential enforced and views
are now available....

Yes, they are in the basic install, but you've got compromises. For
instance, you can have RI (InnoDB only) or you can have full-text
searching (MyISAM only). It's still a ridiculously stupid toy-like
database that is only now getting features that no modern database
should have lacked in its alpha version.