Read/only problem with linked MySQL tables

[mihabaki]

Hello!

I'm using Access 2003 and MyODBC 5.1 driver to connect to a remote MySQL server.

I have to connect from code, because I have timeout issues (which can not be resolved from MyODBC 5.1 driver interface; no more "Initial statement" option).

Anyway after a painful process of getting this to work from code I have a problem that I seem to be opening the tables in read-only mode.

Where is the problem? I want to have read/write acces.

Here's the code:

    Dim wsODBC As Workspace
    Dim db As Database
    Dim rs As DAO.Recordset
    Dim conODBC As Connection
    Dim qdef As QueryDef
    
    Dim baza As String
    Dim user As String
    Dim pass As String
    Dim connect As String
    Dim dsn As String
    
    baza = "baza"
    user = "user"
    pass = "pass"
    dsn = "PD"
    
    connect = "ODBC;" & "DATABASE=" & baza & ";DSN=" & dsn & ";UID=" & user & ";
                  _PWD=" & pass & ";STMT=180"
               
    Set wsODBC = CreateWorkspace("NewWorkspace", "admin", "", dbUseODBC)
    Set db = CurrentDb
       
    ' še s povezavo
    Set conODBC = wsODBC.OpenConnection("con1", dbDriverNoPrompt, False, connect)
       
    Set rs = conODBC.OpenRecordset("SELECT * FROM cscart_users;", dbOpenDynamic)
    
    With rs
        .MoveFirst
        Do Until rs.EOF
            If rs![user_id] = 23 Then
                .Edit
                ![firstname] = "Aljosa"
                .Update
            End If
            .MoveNext
        Loop
    End With
    
    rs.Close
    Set rs = Nothing
    qdef.Close
    Set qdef = Nothing
    db.Close
    Set db = Nothing
    conODBC.Close
    Set conODBC = Nothing
    wsODBC.Close
    Set wsODBC = Nothing

I'm getting Run-time ERROR 3027 - "Cannot update. Database or object is read-only."

Thanks in advance!

Miha

 

[ByteMyzer]

Has the specified User/Login been given Read AND Write access on the Server-Side?

 

[mihabaki]

Has the specified User/Login been given Read AND Write access on the Server-Side?

Yes the user has read/write access. I tested it using "normal" table linking (File/External data/link tables).

M

 

[Banana]

I must caution I've not really worked with ODBCDirect (never really saw the use of them) but anyway-

1) What happen if you comment out the STMT part? My experience has been that it causes more problem than providing functionality.

2) What if you specify options? List of options. As noted on the documentation, 35 is the suggested option, though in my last project I explicitly enabled several of option listed.

3) What happen if you just link the table via Access UI then try to add a row right there on the table? (This is mainly to verify there isn't something else that may be external to Access that's interfering with the process)

 

[mihabaki]

I must caution I've not really worked with ODBCDirect (never really saw the use of them) but anyway-

1) What happen if you comment out the STMT part? My experience has been that it causes more problem than providing functionality.

2) What if you specify options? List of options. As noted on the documentation, 35 is the suggested option, though in my last project I explicitly enabled several of option listed.

3) What happen if you just link the table via Access UI then try to add a row right there on the table? (This is mainly to verify there isn't something else that may be external to Access that's interfering with the process)

1) Still the same problem
2) Still the same problem
3) This works without any problems. But I can only use this using MyODBC 3.51 which has no UTF8 support (which is essential in my case). Using MyODBC i5.1 driver is a problem, because of the timeout issue.

Is there anything else I could try?

M

 

[Banana]

Just out of curiosity, can you use Jet workspace instead of ODBCDirect workspace? As I said before, I never really used it; I did play with it a bit but didn't see how it'd have had helped me economize on my development time. Failing that, what about ADO?

Next, if you went to your DSN file and opened it with text editor, what does it says in there? (Comment out the sensitive information, of course) Compare it with the linked table you created with no problem; look at its Connect property (either by via code or by opening it in design view and looking in the Properties windows)

via code in the immediate window:

?CurrentDb.TableDefs("MyLinkedTable").Connect

Do you see anything different?