在VBA中查询MySQL? [英] query in VBA for MySQL?
问题描述
我已经使用MySQL构建了一个数据库,现在通过VBE链接到excel。我已设法创建连接,但我遇到查询问题。我获得的代码仅发布了特定表格的第一列。当我在我的src语句中专门指定
列时,我在代码的FirstRecordSet部分中收到错误消息。请参阅第一栏的原始代码。当我在src语句中为colukmn名称更改*时,Mess开始。我相信我的错误是
代码的整体逻辑,但我不是VB的专家,
非常感谢你的帮助
PS:我已在下面列出了这些部分。
Sub querydata_selectionCriteria()
Dim test As String
test =工作表(" databaseconnection")。范围(" C15")
Src =" SELECT * FROM statistics其中country_id = 10且year = 2006"
Dim cntMyConnection As ADODB.Connection
设置cntMyConnection =新ADODB.Connection
CnctSource =" DRIVER = {MySQL ODBC 5.1 Driver}; Server = localhost; Database = secondattempt; UID =根; PWD = P8195369x +; OPTION = 3;"
cntMyConnection.Open ConnectionString:= CnctSource
Dim rstFirstRecordset As ADODB.Recordset
设置rstFirstRecordset =新ADODB.Recordset
rstFirstRecordset.Open来源:= Src,ActiveConnection:= cntMyConnection
i = 1
工作表("DatabaseConnection")。激活
使用ActiveSheet
虽然不是rstFirstRecordset.EOF
    .Cells(i,1)= rstFirstRecordset(0).Value
    .Cells(i,2)= rstFirstRecordset(1).Value
    rstFirstRecordset.MoveNext
    i = i + 1
Wend
结束
设置rstFirstRecordset = Nothing
cntMyConnection.Close
设置cntMyConnection = Nothing
End Sub
我已经用MySQL构建了一个数据库,现在链接到 通过VBE excel
。
您已发布在"通常"用于VB.Net问题的论坛区域。
< p style ="">有关 EXCEL 请读取
。
请尝试VBA区域:>>
http://social.msdn.microsoft.com/Forums/en/isvvba/threads
这个EXCEL区域在 answers >>
http://answers.microsoft.com/en-us/office/forum/excel
和EXCEL区域 social.technet >>
http://social.technet.microsoft.com/Forums/en/excel/threads
最后,这里也是VBA上的外部(独立论坛)区域:>>
http://www.programmersheaven.com/mb/vba/Board.aspx
Hi,
I have built a DB with MySQL and now linking to excel via VBE. I have managed to create the connection but I am having trouble with queries. The code I have obtained only releases the first column of a specific table. When I specifically designate a column in my src statement I get an error message in the FirstRecordSet section of the code. Please see under the original code working for the first column. Mess starts when I change * in the src statement for a colukmn name. I believe my error is with the overall logic of the code, but I am not really a specialist of VB,
Many thanks for your help
PS: I have underlined the sections.
Sub querydata_selectionCriteria()
Dim test As String
test = Worksheets("databaseconnection").Range("C15")
Src = "SELECT * FROM statistics where country_id=10 and year=2006"
Dim cntMyConnection As ADODB.Connection
Set cntMyConnection = New ADODB.Connection
CnctSource = "DRIVER={MySQL ODBC 5.1 Driver};Server=localhost;Database=secondattempt; Uid=root; PWD=P8195369x+;OPTION=3;"
cntMyConnection.Open ConnectionString:=CnctSource
Dim rstFirstRecordset As ADODB.Recordset
Set rstFirstRecordset = New ADODB.Recordset
rstFirstRecordset.Open Source:=Src, ActiveConnection:=cntMyConnection
i = 1
Worksheets("DatabaseConnection").Activate
With ActiveSheet
While Not rstFirstRecordset.EOF
.Cells(i, 1) = rstFirstRecordset(0).Value
.Cells(i, 2) = rstFirstRecordset(1).Value
rstFirstRecordset.MoveNext
i = i + 1
Wend
End With
Set rstFirstRecordset = Nothing
cntMyConnection.Close
Set cntMyConnection = Nothing
End Sub
Hi,
I have built a DB with MySQL and now linking to excel via VBE.
Hi,
You have posted in a forum area 'usually' intended for VB.Net questions.
For help with an EXCEL read on please.
Please try the VBA area:>>
http://social.msdn.microsoft.com/Forums/en/isvvba/threads
This EXCEL area on answers :>>
http://answers.microsoft.com/en-us/office/forum/excel
and the EXCEL area on social.technet :>>
http://social.technet.microsoft.com/Forums/en/excel/threads
Finally, here is an external ( independent forum ) area on VBA too:>>
http://www.programmersheaven.com/mb/vba/Board.aspx
这篇关于在VBA中查询MySQL?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!