Public Function ConnectString() _ As String 'returns a DB ConnectString ConnectString = "FileDSN=studentinfo.dsn;UID=sa;PWD=" End Function
这是公用模块里的定义 Public fMainForm As frmMain Public UserName As String Sub Main() Dim fLogin As New frmLogin fLogin.Show vbModal If Not fLogin.OK Then 'Login Failed so exit app End End If Unload fLogin Set fMainForm = New frmMain fMainForm.Show End Sub Public Function ConnectString() _ As String 'returns a DB ConnectString ConnectString = "FileDSN=studentinfo.dsn;UID=sa;PWD=" 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 FunctionPublic Function Testtxt(txt As String) As Boolean If Trim(txt) = "" Then Testtxt = False Else Testtxt = True End If End Function
Private conn As ADODB.Connection Private WithEvents rs As ADODB.Recordset Private Sub Form_Load() Dim apppath As String Dim dbfilename As String Dim ConnectString As String Dim i As Integer Set conn = New ADODB.Connection If Right(App.Path, 1) = "\" Then apppath = App.Path Else apppath = App.Path & "\" End If dbfilename = apppath & "order.mdb" ConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbfilename & ";Persist Security Info=False;" conn.Open ConnectString conn.CursorLocation = adUseClientSet rs = New ADODB.Recordset rs.Open "select * from orders", conn, adOpenDynamic, adLockPessimistic If rs.EOF Then Exit Sub end if end sub
Option Explicit'工程->引用->Microsoft ActiveX Data Objects 2.0 Library (后面为版本号) Dim cn As New ADODB.Connection Dim rs As New ADODB.RecordsetPrivate Sub Command1_Click() rs.Open "SELECT * FROM TABLENAME WHERE NAME = '" & txtName.Text & "'", cn, adOpenDynamic, adLockOptimistic If Not rs.EOF Then Label1.Caption = rs!ID Label2.Caption = rs!Memo End If
rs.AddNew rs!ID = "编号" rs!Memo = "备注" rs.Update End SubPrivate Sub Form_Load() cn.ConnectionString = "Driver={SQL Server};SERVER=DataServer;DATABASE=zxzx;UID=information;PWD=information*&#" cn.Open '查询字符串可以上这里查 'http://www.connectionstrings.com/ End SubPrivate Sub Form_QueryUnload(Cancel As Integer, UnloadMode As Integer) On Error Resume Next rs.Close Set rs = Nothing cn.Close Set cn = Nothing End Sub
不会吧? 你是不是用 '工程->引用->Microsoft ActiveX Data Objects 2.0 Library (后面为版本号) Dim conn As New ADODB.Connection Dim rs As New ADODB.Recordsetconn.ConnectionString = "driver={SQL Server};server=主机机器名或IP;uid=sa;pwd=;ConnectionTimeout=0;database=数据库名" conn.Openrs.CursorLocation = adUseClient rs.Open sql语句, conn如果是这样用应该不会连不上,我这就很好连的啊。
'工程->引用->Microsoft ActiveX Data Objects 2.6 Library 一些资料: 1、SQL dim cnn as new adodb.connection ‘//dim eventwith cnn as adodb.connection dim rs as new adodb.recordset cnn.connectionstring=”Driver={Sql server};server=SeverName;database=数据库名;UID=sa;Pwd=” 或者‘//cnn.ConnectionString="provider=SQLOLEDB.1;Persist Security Info=true;User ID=sa;Password=;Initial Catalog=数据库名;data source=服务器名" cnn.open rs.cursorlocation=aduseclient rs.cursortype=adopenstatic rs.activeconnection=cnn rs.locktype=adlockbatchoptimistic ‘//开放式更新 updateBatch rs.open sql语句 Datagrid1.datasorce=rs ‘//或者将driver={sql server} 改为provider=sqloLedb.1 2、Access dim cnn as new adodb.connection kk=”Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=false;data source=d:\exg.mdb” cnn.open kk
首先引用 ADO(Active Data Object) 2.1(或其他版本)Dim con as Connection Dim com as Connection Dim rst as RecordsetSet con = new Connection Set com = new Command Set rst = new Recordsetcom.ActiveConnection=conwith con .ConnectionString="User ID=登陆名;Password=密码;Data Source=? ;Initial Catalog=?" .Provider="SQLOLEDB" .Open end withrst.Open "select* from 表名",con,adOpenDynamic, adLockOptimistictextbox1.text=rst!列名... VB连接SQL SERVER 2000有很多方法,这里是ADO编程哈
As String
'returns a DB ConnectString
ConnectString = "FileDSN=studentinfo.dsn;UID=sa;PWD="
End Function
Public fMainForm As frmMain
Public UserName As String
Sub Main()
Dim fLogin As New frmLogin
fLogin.Show vbModal
If Not fLogin.OK Then
'Login Failed so exit app
End
End If
Unload fLogin
Set fMainForm = New frmMain
fMainForm.Show
End Sub
Public Function ConnectString() _
As String
'returns a DB ConnectString
ConnectString = "FileDSN=studentinfo.dsn;UID=sa;PWD="
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 FunctionPublic Function Testtxt(txt As String) As Boolean
If Trim(txt) = "" Then
Testtxt = False
Else
Testtxt = True
End If
End Function
Private WithEvents rs As ADODB.Recordset
Private Sub Form_Load()
Dim apppath As String
Dim dbfilename As String
Dim ConnectString As String
Dim i As Integer
Set conn = New ADODB.Connection
If Right(App.Path, 1) = "\" Then
apppath = App.Path
Else
apppath = App.Path & "\"
End If
dbfilename = apppath & "order.mdb"
ConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbfilename & ";Persist Security Info=False;"
conn.Open ConnectString
conn.CursorLocation = adUseClientSet rs = New ADODB.Recordset
rs.Open "select * from orders", conn, adOpenDynamic, adLockPessimistic
If rs.EOF Then
Exit Sub
end if
end sub
conn.Open ConnectString这是连接sqlserver的
Dim cn As New ADODB.Connection
Dim rs As New ADODB.RecordsetPrivate Sub Command1_Click()
rs.Open "SELECT * FROM TABLENAME WHERE NAME = '" & txtName.Text & "'", cn, adOpenDynamic, adLockOptimistic
If Not rs.EOF Then
Label1.Caption = rs!ID
Label2.Caption = rs!Memo
End If
rs.AddNew
rs!ID = "编号"
rs!Memo = "备注"
rs.Update
End SubPrivate Sub Form_Load()
cn.ConnectionString = "Driver={SQL Server};SERVER=DataServer;DATABASE=zxzx;UID=information;PWD=information*&#"
cn.Open
'查询字符串可以上这里查
'http://www.connectionstrings.com/
End SubPrivate Sub Form_QueryUnload(Cancel As Integer, UnloadMode As Integer)
On Error Resume Next
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub
请大虾们帮帮忙,谢先!
conn.Open ConnectString这类。。等,我都试过,在我本机上能连接,但是放到没有安装SQL的就好像不行!
当然不行了。
你在没有安装SQL的机子上运行的话,这台机子就相当是客户机了。
你装你的那句连接语句改一下就OK了,还有就是要关掉双方的防火墙.
ConnectionString = "driver={SQL Server};server=主机机器名或IP;uid=sa;pwd=;ConnectionTimeout=0;database=数据库名"
conn.Open ConnectString
不会吧?
你是不是用
'工程->引用->Microsoft ActiveX Data Objects 2.0 Library (后面为版本号)
Dim conn As New ADODB.Connection
Dim rs As New ADODB.Recordsetconn.ConnectionString = "driver={SQL Server};server=主机机器名或IP;uid=sa;pwd=;ConnectionTimeout=0;database=数据库名"
conn.Openrs.CursorLocation = adUseClient
rs.Open sql语句, conn如果是这样用应该不会连不上,我这就很好连的啊。
一些资料:
1、SQL
dim cnn as new adodb.connection ‘//dim eventwith cnn as adodb.connection
dim rs as new adodb.recordset
cnn.connectionstring=”Driver={Sql server};server=SeverName;database=数据库名;UID=sa;Pwd=”
或者‘//cnn.ConnectionString="provider=SQLOLEDB.1;Persist Security Info=true;User ID=sa;Password=;Initial Catalog=数据库名;data source=服务器名"
cnn.open
rs.cursorlocation=aduseclient
rs.cursortype=adopenstatic
rs.activeconnection=cnn
rs.locktype=adlockbatchoptimistic ‘//开放式更新 updateBatch
rs.open sql语句
Datagrid1.datasorce=rs
‘//或者将driver={sql server} 改为provider=sqloLedb.1
2、Access
dim cnn as new adodb.connection
kk=”Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=false;data source=d:\exg.mdb”
cnn.open kk
Dim com as Connection
Dim rst as RecordsetSet con = new Connection
Set com = new Command
Set rst = new Recordsetcom.ActiveConnection=conwith con
.ConnectionString="User ID=登陆名;Password=密码;Data Source=? ;Initial Catalog=?"
.Provider="SQLOLEDB"
.Open
end withrst.Open "select* from 表名",con,adOpenDynamic, adLockOptimistictextbox1.text=rst!列名...
VB连接SQL SERVER 2000有很多方法,这里是ADO编程哈
里面对数据库连接方式有详细的说明.
如果用odbc:
CONNECTSTR = "DSN=" & yourDSN & ";UID=" & yourUID & ";PWD=" & yourPWD如果用oledb:
CONNECTSTR = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=数据名;Server=sqlserver服务器名"