工程--->引用--->Microsoft ActiveX Data Object 2.x(版本号) Dim CN As New ADODB.Connection '定义数据库的连接 Dim Rs As New ADODB.Recordset CN.ConnectionString = "Provider=sqloledb;Data Source=pmserver;Initial Catalog=northwind;User Id=sa;Password=xxxx;" CN.Open Rs.CursorLocation = adUseClient Rs.Open "select * from employees", CN, adOpenDynamic, adLockOptimistic
数据库--安全性---身份验证是用的"sql server和windows"吗?
我是那样写的代码呀,我是用sql server和windows混合模式进行身份验证的呀
检查你的机子能否与SERVER连接得上。
肯定是SA的登陆密码不正确或者没有SA这个用户
我是这样子的: 你试试!!! Dim conn1 As New ADODB.Connection Text1.Text = "" conn1.Mode = adModeReadWrite conn1.Open "driver={SQL Server};server=tianr;uid=sa;pwd=sa;database=jxjysql" Set rs1 = New ADODB.Recordset rs1.Open "select * from jytable", conn1, adOpenStatic, adLockOptimistic While rs1.EOF = False If Trim(rs1.Fields(0)) = Trim(Text1.Text) Then MsgBox "already you" Text1.SetFocus Text1.Text = "" Exit Sub Else rs1.MoveNext End If Wend
Dim M_Cnn As New ADODB.Connection Dim M_Rs As New ADODB.Recordset ' '创建一个连接(连接到SQL) '函数名:CreateSqlConn '参数: DbConnection ADODB连接,ServerName 服务器名,DBname 数据库名,UserID 登录用户名,UPw 登录密码,Timerout 连接超时 '返回值:TRUE 连接成功.FALSE 连接失败. '例: CreateSqlConn p_cnn,"CJH","cjherp001","sa","123",15 ' Public Function CreateSqlConn(ByRef DbConnection As ADODB.Connection, _ ServerName As String, _ DbName As String, _ UserID As String, _ UPw As String, _ Optional Timerout As Long = 15) As Boolean Dim ReturnVal As Boolean On Error Resume Next If DbConnection.State = adStateOpen And Not IsEmpty(adStateOpen) Then DbConnection.Close End If DbConnection.Provider = "MSDASQL.1" DbConnection.ConnectionString = "Driver={SQL Server};Server=" & ServerName & ";DataBase=" & DbName & ";Uid=" & UserID & ";Pwd=" & UPw & ";APP=" & App.Path & ";WSID=" & UserID & ";Connect Timeout=" & Timerout & ";" DbConnection.ConnectionTimeout = Timerout DbConnection.CommandTimeout = Timerout DbConnection.Open DoEvents If Err.Number = 0 Then ReturnVal = True Else Err.Clear ReturnVal = False End If CreateSqlConn = ReturnVal End FunctionPrivate Sub Command1_Click() Dim StrSql As String M_Cnn.CursorLocation = adUseClient CreateSqlConn M_Cnn, "CJH", "PUBS", "SA", "123" StrSql = "select * FROM authors" M_Rs.CursorLocation = adUseClient M_Rs.Open StrSql, M_Cnn, adOpenKeyset, adLockBatchOptimistic Set DataGrid1.DataSource = M_Rs DataGrid1.Refresh End Sub
Dim Rs As New ADODB.Recordset
CN.ConnectionString = "Provider=sqloledb;Data Source=pmserver;Initial Catalog=northwind;User Id=sa;Password=xxxx;"
CN.Open
Rs.CursorLocation = adUseClient
Rs.Open "select * from employees", CN, adOpenDynamic, adLockOptimistic
Dim conn1 As New ADODB.Connection
Text1.Text = ""
conn1.Mode = adModeReadWrite
conn1.Open "driver={SQL Server};server=tianr;uid=sa;pwd=sa;database=jxjysql"
Set rs1 = New ADODB.Recordset
rs1.Open "select * from jytable", conn1, adOpenStatic, adLockOptimistic
While rs1.EOF = False
If Trim(rs1.Fields(0)) = Trim(Text1.Text) Then
MsgBox "already you"
Text1.SetFocus
Text1.Text = ""
Exit Sub
Else
rs1.MoveNext
End If
Wend
rs1.AddNew
rs1.Fields(0) = Trim(Text1.Text)
rs1.Update
Dim M_Cnn As New ADODB.Connection
Dim M_Rs As New ADODB.Recordset
'
'创建一个连接(连接到SQL)
'函数名:CreateSqlConn
'参数: DbConnection ADODB连接,ServerName 服务器名,DBname 数据库名,UserID 登录用户名,UPw 登录密码,Timerout 连接超时
'返回值:TRUE 连接成功.FALSE 连接失败.
'例: CreateSqlConn p_cnn,"CJH","cjherp001","sa","123",15
'
Public Function CreateSqlConn(ByRef DbConnection As ADODB.Connection, _
ServerName As String, _
DbName As String, _
UserID As String, _
UPw As String, _
Optional Timerout As Long = 15) As Boolean
Dim ReturnVal As Boolean On Error Resume Next If DbConnection.State = adStateOpen And Not IsEmpty(adStateOpen) Then
DbConnection.Close
End If
DbConnection.Provider = "MSDASQL.1"
DbConnection.ConnectionString = "Driver={SQL Server};Server=" & ServerName & ";DataBase=" & DbName & ";Uid=" & UserID & ";Pwd=" & UPw & ";APP=" & App.Path & ";WSID=" & UserID & ";Connect Timeout=" & Timerout & ";"
DbConnection.ConnectionTimeout = Timerout
DbConnection.CommandTimeout = Timerout
DbConnection.Open
DoEvents
If Err.Number = 0 Then
ReturnVal = True
Else
Err.Clear
ReturnVal = False
End If
CreateSqlConn = ReturnVal
End FunctionPrivate Sub Command1_Click()
Dim StrSql As String
M_Cnn.CursorLocation = adUseClient
CreateSqlConn M_Cnn, "CJH", "PUBS", "SA", "123"
StrSql = "select * FROM authors"
M_Rs.CursorLocation = adUseClient
M_Rs.Open StrSql, M_Cnn, adOpenKeyset, adLockBatchOptimistic
Set DataGrid1.DataSource = M_Rs
DataGrid1.Refresh
End Sub