VBA-出现"mysql服务器为--read-only"错误，但仅在使用RecordSet时 [英] VBA - getting 'mysql server is --read-only' error but only when using RecordSet

问题描述

我有以下内容:

Dim oConn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Set rst = New ADODB.Recordset

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


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

table_name = Sheets("Master").Range("S2").Value

SQLstr = "Select * From " & table_name
rst.Open SQLstr, oConn, adOpenDynamic, adLockOptimistic

lRow = Cells(Rows.Count, 1).End(xlUp).Row
lCol = Cells(1, Columns.Count).End(xlToLeft).Column

For rowindx = 2 To lRow
    rst.AddNew
        For fieldIndx = 1 To lCol
            rst.Fields(Cells(1, fieldIndx).Value) = Cells(rowindx, fieldIndx).Value
        Next fieldIndx
Next rowindx
rst.UpdateBatch

rst.Close
oConn.Close

Set rst = Nothing
Set oConn = Nothing

MsgBox ("Query Successful")

这使我在rst.AddNew步骤中"MySQL服务器正在使用--read-only选项运行，因此它无法执行该语句".

This gives me the "The MySQL server is running with the --read-only option so it cannot execute the statement" at the rst.AddNew step.

但是，当我运行附加到不同宏(同一数据库，同一服务器)的单独函数时，一切都会按预期执行. 数据库不是只读的.

However, when I run a separate function attached to a different macro (same database, same server), everything gets executed as expected. The database is not read-only.

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")

第一个区块出了什么问题?记录集是否默认为只读?

What's wrong with the first block? Does recordset default to read-only?

推荐答案

只需将'adLockOptimistic'更改为'adLockBatchOptimistic'

Just had to change 'adLockOptimistic' to 'adLockBatchOptimistic'

这篇关于VBA-出现"mysql服务器为--read-only"错误，但仅在使用RecordSet时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！