Dim cn As New ADODB.Connection Dim sn As New ADODB.Recordset cn.ConnectionString = "provider=SQLOLEDB;server=(local);uid=sa;pwd=" cn.CursorLocation = adUseClient cn.Open sn.Open "sp_databases", cn Do While Not sn.EOF msgbox sn("DATABASE_NAME") '& " " & sn("DATABASE_SIZE") sn.MoveNext Loop sn.Close
'********************************************************* '* 名称:类模块DataBaseConnection '* 功能:连接SQL Server、Access等数据库的模块定义 '*********************************************************Option ExplicitPublic Function MdbConnectString(MdbFile As String, Optional UserName As String, Optional password As String) As String MdbConnectString = "Provider=MSDASQL.1;Extended Properties=" + """" + "DBQ=" + MdbFile + ";Driver={Microsoft Access Driver (*.mdb)};MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;SafeTransactions=0;Threads=3;UID=" + UserName + ";Pwd=" + password + """" End FunctionPublic Function XlsConnectString(XlsFile As String) As String XlsConnectString = "Provider=MSDASQL.1;Extended Properties=" + """" + "DBQ=" + XlsFile + ";Driver={Microsoft Excel Driver (*.xls)};MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;SafeTransactions=0;Threads=3" + """" End FunctionPublic Function CsvConnectString() As String CsvConnectString = "Provider=MSDASQL.1;Extended Properties=" + """" + "Driver={Microsoft Text Driver (*.txt; *.csv)};MaxBufferSize=2048;MaxScanRows=25;PageTimeout=5;SafeTransactions=0;Threads=3;" + """" End FunctionPublic Function SqlConnectString(ServerName As String, UserName As String, password As String) As String SqlConnectString = "Provider=SQLOLEDB.1;User ID=" + UserName + ";Pwd=" + password + ";Data Source=" + ServerName End Function 模块中定义的连接等公用变量 Public db As New ADODB.Connection Public rs As New ADODB.Recordset Public cmd As New ADODB.Command '********************************************************* '* 名称:连接服务器窗体 '* 功能:连接SQL Server数据库 '* 控件:三个文本框和两个按钮 '********************************************************* Private Sub Command1_Click() On Error Resume Next Dim DBC As New DataBaseConnection If db.State = 1 Then db.Close End If db.ConnectionString = DBC.SqlConnectString(Text1.Text, Text2.Text, Text3.Text) rs.CursorType = adOpenDynamic rs.CursorLocation = adUseClient rs.LockType = adLockOptimistic db.CursorLocation = adUseClient db.Open Set cmd.ActiveConnection = db If Err.Number Then MsgBox Err.Description, 16 + vbOKOnly, Err.Number Exit Sub End If db.DefaultDatabase = "student1" ’设置要连接数据库名称 If Err.Number Then MsgBox Err.Description, 16 + vbOKOnly, Err.Number Exit Sub End If d1 = Text1.Text d2 = Text2.Text d3 = Text3.Text Form1.Show Unload Me End Sub
其实只要一句就搞定了 1.connection连接到SqL的Master 2.select * from sysdatabases 我就有这个写一个SQL Server查询分析器。
Dim sn As New ADODB.Recordset
cn.ConnectionString = "provider=SQLOLEDB;server=(local);uid=sa;pwd="
cn.CursorLocation = adUseClient
cn.Open
sn.Open "sp_databases", cn
Do While Not sn.EOF
msgbox sn("DATABASE_NAME") '& " " & sn("DATABASE_SIZE")
sn.MoveNext
Loop
sn.Close
'* 名称:类模块DataBaseConnection
'* 功能:连接SQL Server、Access等数据库的模块定义
'*********************************************************Option ExplicitPublic Function MdbConnectString(MdbFile As String, Optional UserName As String, Optional password As String) As String
MdbConnectString = "Provider=MSDASQL.1;Extended Properties=" + """" + "DBQ=" + MdbFile + ";Driver={Microsoft Access Driver (*.mdb)};MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;SafeTransactions=0;Threads=3;UID=" + UserName + ";Pwd=" + password + """"
End FunctionPublic Function XlsConnectString(XlsFile As String) As String
XlsConnectString = "Provider=MSDASQL.1;Extended Properties=" + """" + "DBQ=" + XlsFile + ";Driver={Microsoft Excel Driver (*.xls)};MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;SafeTransactions=0;Threads=3" + """"
End FunctionPublic Function CsvConnectString() As String
CsvConnectString = "Provider=MSDASQL.1;Extended Properties=" + """" + "Driver={Microsoft Text Driver (*.txt; *.csv)};MaxBufferSize=2048;MaxScanRows=25;PageTimeout=5;SafeTransactions=0;Threads=3;" + """"
End FunctionPublic Function SqlConnectString(ServerName As String, UserName As String, password As String) As String
SqlConnectString = "Provider=SQLOLEDB.1;User ID=" + UserName + ";Pwd=" + password + ";Data Source=" + ServerName
End Function
模块中定义的连接等公用变量
Public db As New ADODB.Connection
Public rs As New ADODB.Recordset
Public cmd As New ADODB.Command
'*********************************************************
'* 名称:连接服务器窗体
'* 功能:连接SQL Server数据库
'* 控件:三个文本框和两个按钮
'*********************************************************
Private Sub Command1_Click()
On Error Resume Next
Dim DBC As New DataBaseConnection
If db.State = 1 Then
db.Close
End If
db.ConnectionString = DBC.SqlConnectString(Text1.Text, Text2.Text, Text3.Text)
rs.CursorType = adOpenDynamic
rs.CursorLocation = adUseClient
rs.LockType = adLockOptimistic
db.CursorLocation = adUseClient
db.Open
Set cmd.ActiveConnection = db
If Err.Number Then
MsgBox Err.Description, 16 + vbOKOnly, Err.Number
Exit Sub
End If
db.DefaultDatabase = "student1" ’设置要连接数据库名称
If Err.Number Then
MsgBox Err.Description, 16 + vbOKOnly, Err.Number
Exit Sub
End If
d1 = Text1.Text
d2 = Text2.Text
d3 = Text3.Text
Form1.Show
Unload Me
End Sub
1.connection连接到SqL的Master
2.select * from sysdatabases
我就有这个写一个SQL Server查询分析器。