Dim conn as ADODB.Connection dim rs as adodb.recordsetset conn = new Adodb.Connection conn.ConnectString="driver={SQL Server};server=[ServerName];Uid=sa;Pwd=;Database=[Your Database]"Conn.Openset rs=New adodb.recordset rs.open "Select * From [YourTable]" ....
首先在项目中添加一个模块mode。bas,原代码如下 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 = "driver={sql server};server=服务器名或者ip地址;database=你的数据库名;uid=sa;password=你设定的密码" End Function Public Function ExecuteSQL(ByVal SQL _ As String, MsgString As String) _ As ADODB.Recordset '执行sql语句,并返回一个记录集 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) & _ "成功完成查询!" 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在程序中这样调用: dim txtsql as string dim msgtext as string dim mrc as adodb.recordset txtsql="加入你的sql查询语句" set mrc=executesql(txtsql,msgtext) 就可以了
Set cn = New ADODB.Connection With cn .ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info = False;User ID = sa; Pwd=******; Data Source = SQLServerName; Initial Catalog = TableName" .Open If .State = adStateOpen Then 'Connection was Successful Else 'Connection was not Successful End If End With
private db as new adodc.connection private rs as new adodc.recordset private sql as string sql="数据库驱动程序" +"本地数据库路径" db.open sql /*打开数据库,准备对其操作 sql="创建数据表/删除数据表的sql语句" db.excute sql="selct * from 数据表 where 条件“ rs.open sql /*打开符合条件的记录 rs.close db.close
dim rs as adodb.recordsetset conn = new Adodb.Connection
conn.ConnectString="driver={SQL Server};server=[ServerName];Uid=sa;Pwd=;Database=[Your Database]"Conn.Openset rs=New adodb.recordset
rs.open "Select * From [YourTable]" ....
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 = "driver={sql server};server=服务器名或者ip地址;database=你的数据库名;uid=sa;password=你设定的密码"
End Function
Public Function ExecuteSQL(ByVal SQL _
As String, MsgString As String) _
As ADODB.Recordset
'执行sql语句,并返回一个记录集
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) & _
"成功完成查询!"
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在程序中这样调用:
dim txtsql as string
dim msgtext as string
dim mrc as adodb.recordset
txtsql="加入你的sql查询语句"
set mrc=executesql(txtsql,msgtext)
就可以了
With cn
.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info = False;User ID = sa; Pwd=******; Data Source = SQLServerName; Initial Catalog = TableName"
.Open
If .State = adStateOpen Then
'Connection was Successful
Else
'Connection was not Successful
End If
End With
private rs as new adodc.recordset
private sql as string
sql="数据库驱动程序" +"本地数据库路径"
db.open sql /*打开数据库,准备对其操作
sql="创建数据表/删除数据表的sql语句"
db.excute
sql="selct * from 数据表 where 条件“
rs.open sql /*打开符合条件的记录
rs.close
db.close