UC知道EXCEL中VB连接MYSQL报错
来源：百度知道 编辑：UC知道 时间：2024/06/22 11:02:17
装了MYSQL的ODBC驱动，在数据源 (ODBC) Test 成功。
代码如下：
Sub insertData()
Dim sql As String
sql = "select * from sectable where no = 6"
Dim rs As New ADODB.Recordset
Set rs = conn(sql)
MsgBox sql
MsgBox rs.RecordCount
End Sub
Public Function conn(ByVal strSQL As String) As ADODB.Recordset
Dim db As New ADODB.Connection
Dim rst As ADODB.Recordset
db.Open "Provider=SQLOLEDB.1;User ID=root;Password=root;Persist Security Info=False;Initial Catalog=secdb;Data Source=127.0.0.1"
Set rst = New ADODB.Recordset
rst.Open Trim$(strSQL), db, adOpenKeyset, adLockOptimistic
Set conn = rst
End Function
这行出错，报数据库不存在或拒绝访问
db.Open "Provider=SQLOLEDB.1;User ID=root;Password=root;Persist Security Info=False;Initial Catalog=secdb;Data Source=127.0.0.1"
补充下：我的数据库管理ID和PWD都是root
代码如下：
Sub insertData()
Dim sql As String
sql = "select * from sectable where no = 6"
Dim rs As New ADODB.Recordset
Set rs = conn(sql)
MsgBox sql
MsgBox rs.RecordCount
End Sub
Public Function conn(ByVal strSQL As String) As ADODB.Recordset
Dim db As New ADODB.Connection
Dim rst As ADODB.Recordset
db.Open "Provider=SQLOLEDB.1;User ID=root;Password=root;Persist Security Info=False;Initial Catalog=secdb;Data Source=127.0.0.1"
Set rst = New ADODB.Recordset
rst.Open Trim$(strSQL), db, adOpenKeyset, adLockOptimistic
Set conn = rst
End Function
这行出错，报数据库不存在或拒绝访问
db.Open "Provider=SQLOLEDB.1;User ID=root;Password=root;Persist Security Info=False;Initial Catalog=secdb;Data Source=127.0.0.1"
补充下：我的数据库管理ID和PWD都是root
改成这个应该就可以了。
Public Function conn(ByVal strSQL As String) As ADODB.Recordset
Set cn = CreateObject("ADODB.Connection")
strCn = "driver=MySQL ODBC 5.1 Driver;" & _
"server=127.0.0.1;" & _
"database=secdb;" & _
"uid=root;" & _
"pwd=root"
cn.Open strCn
Set rst = New ADODB.Recordset
rst.Open Trim$(strSQL), cn, adOpenKeyset, adLockOptimistic
Set conn = rst
End Function
我建了一样的数据库连上了，数据库弄了条数据，但用你的代码查询不出来。
rs.RecordCount = -1
SERVER 服务器没有指定赋值;
DATABASE 哪个数据库没有指定
条件要有Provider提供者;SERVER服务器名;USER登录名;PWD密码;DATABASE数据库名
你看看在连接