Connecting Excel to Mysql Update PROBLEM

Question

i am connecting to a mysql table through vba in excel and i am updating it:

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

the issue is that the only field that gets updated is the instrument field!!

here i a desc of the batchinfo table 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)

funny thing is that when i recreate the table without the auto_increment then it works fine

i really need someone to answer this question, i dont care if you have a hunch or not exactly sure, i will try any solution

Please, please, please don't keep asking the same question over and over again. Just edit your original question. While I like Excel and VBA questions, there isn't a huge population of VBA users here to help out. You might try on some other VBA forums. — Lance Roberts
– Lance Roberts, Commented May 13, 2010 at 19:37

Alex K. · Accepted Answer · 2010-05-13 19:56:01Z

4

I'm not familiar with MySQL but TEXT looks like a blob type? If so I'm surprised it works at all as ADO requires special handling for BLOBS ( http://dev.mysql.com/tech-resources/articles/vb-blob-handling.html )

Try a VARCHAR type instead.

You could also try ADOCn.Execute "INSERT ..."

answered May 13, 2010 at 19:56

Alex K.

177k32 gold badges276 silver badges299 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Alex Gordon Over a year ago

alex thank u for your answer, why do you say its a blob? its just regular text

Alex Gordon Over a year ago

WOWOWOWOOW YOU ARE THE MAN!!!!!!!!!!!! IT WORKS!!!!!!!!! WOWOWOWO. can you please explain why did it work with varchar and not with text!?!??

Alex K. Over a year ago

TEXT is unlike other types like int and varchar dev.mysql.com/doc/refman/5.0/en/blob.html and probably needs to be handled differently, you'll need to ask a MySQL person about that.

Alex Gordon Over a year ago

thank you so so much!! ive spent 8 hours trying to figure this out!

Collectives™ on Stack Overflow

Connecting Excel to Mysql Update PROBLEM

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related