Access does not like Mysql

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Stuart McGraw - 02 Dec 2005 15:57 GMT

I am having a problem creating Access tables linked to a
Mysql database. I get the widow to select a Data Source,
click on the (system) DSN I created (and tested ok), and
click ok. The Database Source selection window disappears,
but no window showing the Mysql tables ever appears nor
does any kind of error message (typical Microsoft.)
I can read the Mysql table using ODBC from a Python
program, so Mysql and ODBC seem to be working, looks
like Access is the one refusing to cooperate. Also, Access
seems screwed up after this, often dieing with an access
violation later on.

Any sugesstion about how to trouble shoot this? I generated
a odbc trace file, but I don't recognise anything in there as
being a blatant error.

Windows-2000-sp2 / Access-2000-sp3 / Mysql-5.0 /
mysql-connector-odbc-3.51.12

Reply to this Message

Ron Hinds - 02 Dec 2005 18:13 GMT

I've connected to MySQL from Access 97 successfully. Usually what happens
after selecting the DSN is you are prompted for login information. I would
suggest upgrading your Windows 2000 to SP4 - I seem to recall there was an
issue with the MyODBC connector that required SP4.

> I am having a problem creating Access tables linked to a
> Mysql database. I get the widow to select a Data Source,
[quoted text clipped - 14 lines]
> Windows-2000-sp2 / Access-2000-sp3 / Mysql-5.0 /
> mysql-connector-odbc-3.51.12

Reply to this Message

Stuart McGraw - 02 Dec 2005 18:50 GMT

Sorry, the "sp2" was a brain-fart. I am at sp4.

I looked again more carefully at the ODBC trace log. Just before
it checks for MSysConf, there is a BADMEM error. Is this significant?
I doubt it because the log continues with a lot more normal
looking stuff before it finally ends. But I have no idea what it
is saying, so maybe someone can interpret this?

--snip--
db1 270-5a4 EXIT SQLGetInfoW with return code 0 (SQL_SUCCESS)
HDBC 096B15E8
UWORD 1 <SQL_ACTIVE_STATEMENTS>
PTR 0x0012C738 (0)
SWORD 2
SWORD * 0x0012C71E (2)

db1 270-5a4 ENTER SQLAllocStmt
HDBC 096B15E8
HSTMT * 0012C2FC

db1 270-5a4 EXIT SQLAllocStmt with return code 0 (SQL_SUCCESS)
HDBC 096B15E8
HSTMT * 0x0012C2FC ( 0x096b18c0)

db1 270-5a4 ENTER SQLGetStmtOption
HSTMT 096B18C0
UWORD 0
PTR 0x0012C2A8

db1 270-5a4 EXIT SQLGetStmtOption with return code 0 (SQL_SUCCESS)
HSTMT 096B18C0
UWORD 0
PTR 0x0012C2A8

db1 270-5a4 ENTER SQLSetStmtOption
HSTMT 096B18C0
UWORD 0 <SQL_QUERY_TIMEOUT>
SQLPOINTER 0x0000003C

db1 270-5a4 EXIT SQLSetStmtOption with return code 0 (SQL_SUCCESS)
HSTMT 096B18C0
UWORD 0 <SQL_QUERY_TIMEOUT>
SQLPOINTER 0x0000003C (BADMEM)

db1 270-5a4 ENTER SQLExecDirectW
HSTMT 096B18C0
WCHAR * 0x1B074AA0 [ -3] "SELECT Config, nValue FROM MSysConf\ 0"
SDWORD -3

db1 270-5a4 EXIT SQLExecDirectW with return code -1 (SQL_ERROR)
HSTMT 096B18C0
WCHAR * 0x1B074AA0 [ -3] "SELECT Config, nValue FROM MSysConf\ 0"
SDWORD -3

DIAG [S0002] [MySQL][ODBC 3.51 Driver][mysqld-5.0.16-nt]Table 'jdb.msysconf' doesn't exist (1146)
--snip--

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

The log ends with:

--snip--
db1 270-5a4 EXIT SQLGetInfoW with return code 0 (SQL_SUCCESS)
HDBC 096B15E8
UWORD 24 <SQL_CURSOR_ROLLBACK_BEHAVIOR>
PTR 0x0012C842 (2)
SWORD 2
SWORD * 0x0012C814 (2)

db1 270-5a4 ENTER SQLGetInfoW
HDBC 096B15E8
UWORD 29 <SQL_IDENTIFIER_QUOTE_CHAR>
PTR 0x0012C82C
SWORD 20
SWORD * 0x0012C854

db1 270-5a4 EXIT SQLGetInfoW with return code 0 (SQL_SUCCESS)
HDBC 096B15E8
UWORD 29 <SQL_IDENTIFIER_QUOTE_CHAR>
PTR 0x0012C82C [ 2] "`"
SWORD 20
SWORD * 0x0012C854 (2)

Is this a normal ending?
Does any of this give any information about what Access is up to?

> I've connected to MySQL from Access 97 successfully. Usually what happens
> after selecting the DSN is you are prompted for login information. I would
[quoted text clipped - 19 lines]
> > Windows-2000-sp2 / Access-2000-sp3 / Mysql-5.0 /
> > mysql-connector-odbc-3.51.12

Reply to this Message

Terry Kreft - 02 Dec 2005 20:47 GMT

Ahh, that's where you're going wrong; the spouse has to be live for this to
work.

Signature

Terry Kreft

>I am having a problem creating Access tables linked to a
> Mysql database. I get the widow to select a Data Source,

<SNIP>

Reply to this Message