连接的Excel到MySQL更新问题

Question

我连接到MySQL表通过Excel VBA中，我更新它：

Set cn = New ADODB.Connection 
cn.Open "DRIVER={MySQL ODBC 5.1 Driver};" & _ 
    "SERVER=localhost;" & _ 
    "DATABASE=employees;" & _ 
    "USER=root;" & _ 
    "PASSWORD=M1llen;" & _ 
    "Option=3" 
'lets get the batch info 
' 
' open a recordset 
Set rs = New ADODB.Recordset 
rs.Open "batchinfo", cn, adOpenKeyset, adLockOptimistic, adCmdTable 
' all records in a table from Report 1 
'Set wsSheet1 = wbBook.Worksheets(1) 
' better refer by name 
'Set wsSheet1 = wbBook.Worksheets.("Report 1") 
Worksheets.Item("Report 1").Select 
dpath = Range("B2").Text 
atime = Trim(Range("B3").Text) 
rtime = Trim(Range("B4").Text) 
lcalib = Trim(Range("B5").Text) 
aname = Trim(Range("B6").Text) 
rname = Trim(Range("B7").Text) 
bstate = Trim(Range("B8").Text) 

instrument = GetInstrFromXML() 

With rs 
    .AddNew ' create a new record 
    ' add values to each field in the record 
    .Fields("datapath") = "abc" 
    .Fields("analysistime") = atime 
    .Fields("reporttime") = rtime 
    .Fields("lastcalib") = lcalib 
    .Fields("analystname") = aname 
    .Fields("reportname") = rname 
    .Fields("batchstate") = bstate 
    .Fields("instrument") = instrument 
    .Update ' stores the new record 
End With 

的问题是，那个被更新的唯一领域是仪器领域！

这里91A的batchinfo表的mysql的说明：

mysql> desc batchinfo; 
+--------------+---------+------+-----+---------+----------------+ 
| Field  | Type | Null | Key | Default | Extra   | 
+--------------+---------+------+-----+---------+----------------+ 
| rowid  | int(11) | NO | PRI | NULL | auto_increment | 
| datapath  | text | YES |  | NULL |    | 
| analysistime | text | YES |  | NULL |    | 
| reporttime | text | YES |  | NULL |    | 
| lastcalib | text | YES |  | NULL |    | 
| analystname | text | YES |  | NULL |    | 
| reportname | text | YES |  | NULL |    | 
| batchstate | text | YES |  | NULL |    | 
| instrument | text | YES |  | NULL |    | 
+--------------+---------+------+-----+---------+----------------+ 
9 rows in set (0.00 sec) 

有趣的是，当我重新创建表，而不AUTO_INCREMENT然后正常工作

我真的需要有人来回答这个问题，我不关心，如果你有一种预感，或者不是很确定，我会尝试任何解决方案

Answer 1

我不熟悉MySQL，但TEXT看起来像一个BLOB类型？如果是这样，我感到惊讶它可以工作，因为ADO需要BLOBS的特殊处理（http://dev.mysql.com/tech-resources/articles/vb-blob-handling.html）

尝试使用VARCHAR类型。

您也可以尝试ADOCn.Execute "INSERT ..."