我靠,这样的问题问我饿.哈哈,
用dsn.手工设置以下odbc.或者用代码建立dsn.或者采用非dsn连接.
代码建dsn
Private Const ODBC_ADD_DSN = 1 ' Add data source
Private Const ODBC_CONFIG_DSN = 2 ' Configure (edit) data source
Private Const ODBC_REMOVE_DSN = 3 ' Remove data source
Private Const vbAPINull As Long = 0& ' NULL Pointer
Private Declare Function SQLConfigDataSource Lib "ODBCCP32.DLL" (ByVal hwndParent As Long, ByVal fRequest As Long, ByVal lpszDriver As String, ByVal lpszAttributes As String) As Long
Sub CreatDSN()
Dim intRet As Long
Dim strDriver As String
Dim strAttributes As String
strDriver = "Microsoft Access Driver (*.mdb)"----------这里要修改成sqlserver
'null 结尾的参数
'请看相关文档
'strAttributes = "SERVER=SomeServer" & Chr$(0)
'strAttributes = strAttributes & "DESCRIPTION=Temp DSN" & Chr$(0)'这里要修改
Public Function ConnectString() As String
ConnectString = "driver={SQL Server};server=" & 服务器名称 & ";uid=用户名;pwd=密码;database=数据库"
End Function
Public Function ExecuteSQL(ByVal SQL As String, MsgString As String) As ADODB.Recordset
'executes SQL and returns Recordset
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim sTokens() As String
' On Error GoTo ExecuteSQL_Error
sTokens = Split(SQL)
Set cnn = New ADODB.Connection
cnn.Open ConnectString
If InStr("INSERT,DELETE,UPDATE", _
UCase$(sTokens(0))) Then
cnn.Execute SQL
MsgString = sTokens(0) & _
" query successful"
Else
Set rst = New ADODB.Recordset
rst.Open Trim$(SQL), cnn, _
adOpenKeyset, _
adLockOptimistic
'rst.MoveLast 'get RecordCount
Set ExecuteSQL = rst
MsgString = "查询到" & rst.RecordCount & " 条记录 "
End If
ExecuteSQL_Exit:
Set rst = Nothing
Set cnn = Nothing
Exit Function
ExecuteSQL_Error:
MsgString = "查询错误: " & Err.Description
Resume ExecuteSQL_Exit
End Function
以上放在模块中;
在程序中:
Dim txtSQL As String
Dim MsgText As String
Dim ADOname As ADODB.Recordset
txtSQL = "select * from 表"
Set ADOname = ExecuteSQL(txtSQL, MsgText)