Connecting to an ODBC (MySQL) table through code

[siftach]

I am tryeing to write a code that creates a temporary table in a MySQL database, and then creates a linked table. I use a TableDef object to link to the new table, but the resulting table is read-only. How can I make it possible to write to it?

Here is my code:

Dim dbs As Database
Dim tdf As TableDef
Set dbs = CurrentDb
Set tdf = dbs.CreateTableDef("Temp")
tdf.SourceTableName = "##mytable"
tdf.Connect = "ODBC;" & _
"DSN=my_DSN;" & _
"DESC=MySQL ODBC 3.51 Driver DSN;" & _
"DATABASE=my_db;" & _
"SERVER=127.0.0.1;" & _
"PASSWORD=pass;" & _
"PORT=3306;" & _
"OPTION=22563;" & _
"STMT=;"
dbs.TableDefs.Append tdf
dbs.TableDefs.Refresh

 

[OnTheFly]

I am not real familiar with MySQL so this may only be a shot in the dark. When I have run into this linking to SQL it has been a problem with permissions on the SQL side not the Access side.

Thought I would through it out just in case.

Hope this helps.

OnTheFly