VBA中用ADO通过ODBC访问Mysql,但返回的中文字段为?号,查了论坛的相关帖子,原因应该是字符集没有设置为GB2312(数据库采用UTF8),那么应该如何在代码中设置?
Public Sub ADO_Query() Dim ConnStr As String, SqlCommand As String, Sqlstring As String
'////////////////////////////////////////////////
'// //
'// 定义连接字符串 //
'// //
'////////////////////////////////////////////////
ConnStr = ""
ConnStr = ConnStr & "Provider=MSDASQL.1;"
ConnStr = ConnStr & "Persist Security Info=True;"
ConnStr = ConnStr & "Extended Properties= DATABASE=jj;"
ConnStr = ConnStr & "DRIVER={MySQL ODBC 3.51 Driver};"
ConnStr = ConnStr & "OPTION=0;"
ConnStr = ConnStr & "PORT=0;"
ConnStr = ConnStr & "SERVER=10.203.0.58;"
ConnStr = ConnStr & "UID=root;"
ConnStr = ConnStr & "PWD=123"
'^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
'建立连接
Set conn = New ADODB.Connection
With conn
.ConnectionString = ConnStr
.Open
End With Sqlstring = "select * from zw" Sheet1.Range("A1").CopyFromRecordset conn.Execute(Sqlstring)
conn.Close
Set conn = NothingEnd Sub
Public Sub ADO_Query() Dim ConnStr As String, SqlCommand As String, Sqlstring As String
'////////////////////////////////////////////////
'// //
'// 定义连接字符串 //
'// //
'////////////////////////////////////////////////
ConnStr = ""
ConnStr = ConnStr & "Provider=MSDASQL.1;"
ConnStr = ConnStr & "Persist Security Info=True;"
ConnStr = ConnStr & "Extended Properties= DATABASE=jj;"
ConnStr = ConnStr & "DRIVER={MySQL ODBC 3.51 Driver};"
ConnStr = ConnStr & "OPTION=0;"
ConnStr = ConnStr & "PORT=0;"
ConnStr = ConnStr & "SERVER=10.203.0.58;"
ConnStr = ConnStr & "UID=root;"
ConnStr = ConnStr & "PWD=123"
'^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
'建立连接
Set conn = New ADODB.Connection
With conn
.ConnectionString = ConnStr
.Open
End With Sqlstring = "select * from zw" Sheet1.Range("A1").CopyFromRecordset conn.Execute(Sqlstring)
conn.Close
Set conn = NothingEnd Sub
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货