你首先要在VB菜单中:
“工程”-->“引用”-->“Microsoft AxtiveX Data Objects 2.X Library”
注:2.X为版本号,如果你机子上有高版本的就用高版本的,如:2.5或2.6的
示例:
dim cn as new adodb.Connection
dim rs as new adodb.recordset
dim sqlstr as string
cn.open "Provider=SQLOLEDB;Driver={SQL Server};Server=服务器名或IP地址;Uid=用户名" & _
";Pwd=密码;Database=数据库名"
rs.cursorlocation=aduseclient
rs.open "select * from XXX",cn,3,3
rs.save "d:\mydata\data.rst",adPersistXML'保存
rs.close
rs.open "d:\mydata\data.rst",cn,AdOpenDynamic,AdLockBatchOptimistic,adCmdFile'打开
msgbox "共有:" & rs.recordcount & "条记录!!!"
rs.close
set rs=nothing
cn.close
set cn=nothing
用ODBC的化就要配置ODBC
用ADO直接连接数据库比较方便些。
下面的用ADO连接SQL的例子:
Option Explicit
Public SqlConn As New ADODB.Connection
'功能简介:建立SQL数据库公共链接
'参数一:服务器名或IP
'参数二:数据库名称
'参数三:登陆用户名称
'参数四:登陆用户密码
'参数五:延时
'返回值:"ok"--成功, 否则返回错误信息
Function PulbicSQLConn(StrServerName As String, StrDataName As String, StrUserName As String, StrPassword As String, IntOutTime As Integer) As String
On Error GoTo SQLConnErr
Dim StrSql As String
StrSql = "Provider=sqloledb;Data Source=" & StrServerName & ",1433;Initial Catalog=" & StrDataName & ";User ID=" & StrUserName & ";Password=" & StrPassword & ";"
If SqlConn.State <> 0 Then SqlConn.Close
SqlConn.ConnectionTimeout = IntOutTime
SqlConn.Open StrSql
PulbicSQLConn = "ok"
Exit Function
SQLConnErr:
PulbicSQLConn = Err.Description
If SqlConn.State <> 0 Then SqlConn.Close: Set SqlConn = Nothing
End Function
'功能简介:关闭SOL数据库公共链接
Function PublicMdbClose()
If SqlConn.State <> 0 Then SqlConn.Close: Set SqlConn = Nothing
End Function