2

I asked a similar question here.

The second block of code worked perfectly before. But now I'm getting the read-only error on it for some reason. I pasted it again below.

Dim oConn As New ADODB.Connection
Dim cmd As New ADODB.Command

Set oConn = New ADODB.Connection
oConn.Open "DRIVER={MySQL ODBC 5.1 Driver};" & _
    "SERVER=server;" & _
    "DATABASE=database;" & _
    "USER=user;" & _
    "PASSWORD=password;" & _
    "Option=3"


Set cmd = New ADODB.Command
cmd.ActiveConnection = oConn

cmd.CommandText = strSQL
cmd.Execute

oConn.Close
MsgBox ("Query Successful")

I tried looking through the documentation and didn't see anything too helpful. I also mainly did an insert on the database and it still ran fine so the database isn't read-only.

What's wrong? Is there a way to force a read/write property?

Rick James
  • 135,179
  • 13
  • 127
  • 222
JesusMonroe
  • 1,421
  • 3
  • 13
  • 20
  • 1
    Unrelated to your question, you might want to remove `New` from your `Dim` statements -- since you use `Set` syntax later in the code. – chillin Sep 21 '18 at 19:42
  • Just to be extra sure, replace `cmd.ActiveConnection = oConn` with `Set cmd.ActiveConnection = oConn`. – GSerg Sep 23 '18 at 22:37
  • Didn't work, same error – JesusMonroe Sep 23 '18 at 23:11
  • Have you tried fiddling with the connection string? Per [this question](https://stackoverflow.com/questions/4407841/whats-the-option-n-in-the-mysql-odbc-connection-string), you can replace the `Option=3` part with `FLAG_FIELD_LENGTH=1;FLAG_FOUND_ROWS=2`. Have you looked over the [MySQL ODBC parameters page](https://dev.mysql.com/doc/connector-odbc/en/connector-odbc-configuration-connection-parameters.html#codbc-dsn-option-combos)? If you have Microsoft Access, can you try creating a dummy database, adding the table as a linked table, and updating that? – Zack Sep 24 '18 at 17:10
  • I ended up making a different server endpoint which seemed to fix the issue. Don't know why this one 'used to work', nor do I know how to award the bounty but this is resolved. – JesusMonroe Sep 24 '18 at 18:19
  • What line causes an error? – Maciej Los Sep 24 '18 at 19:11
  • I just set server= to something else. – JesusMonroe Sep 24 '18 at 19:31

2 Answers2

2

Ok I essentially just created a new endpoint that was a copy of my old server, set server= to something else, and it worked. Both servers have the same permissions so I'm not exactly sure why but that's my fix for anyone who encounters this in the future.

JesusMonroe
  • 1,421
  • 3
  • 13
  • 20
0

I've got lot of problem with the ODBC 5.1 Drivers for MySQL (provided by default). Same case than you, worked fine and one day, many problems without any modification... But was solved with an update to the last version of MySQL Connector, the 8.0.

You can go into "ODBC Data Source Adminsitrator" from the Start Menu, and check into your "Drivers" panel if you have the last one. If not, you can download it on the official MySQL website here. Then replace DRIVER={MySQL ODBC 5.1 Driver} by DRIVER={MySQL ODBC 8.0 Driver}.

Hope will help on your problem.

BenderIO
  • 411
  • 4
  • 11