: Dim svr As New SQLDMO.SQLServer Dim db As SQLDMO.Database Dim i As Integer svr.Connect "(local)", "sa" Set db = svr.Databases("pubs") For i = 1 To db.Tables.Count Debug.Print db.Tables(i).Name next
用SQLDMO可以,sqlserver2000的盘上有例子 'In form 'Combox --txtserver 'checkbox ---chkAuthentication 'textbox --txtUsername,txtPasswd 'CommandButton --cmdOK,cmdDisconnect,cmdCancelPrivate sUsername As String Private sPasswd As String'Use the SQL DMO object to find available SQL Servers Private oSQLServerDMOApp As SQLDMO.Application'Use the SQLServer object to connect to a specific server Public WithEvents oSQLServer As SQLDMO.SQLServer'Determine is NT Authentication is to be used or not Private Sub chkAuthentication_Click() If chkAuthentication.Value = vbChecked Then 'This assumes a trusted connection Frame1.Enabled = False Label1.Enabled = False Label2.Enabled = False Else 'This assumes a login is required Frame1.Enabled = True Label1.Enabled = True Label2.Enabled = True End If End SubPrivate Sub cmdCancel_Click() Unload Me End SubPrivate Sub cmdDisconnect_Click() 'When done with the connection to SQLServer you must Disconnect If Not oSQLServer Is Nothing Then oSQLServer.DisConnect Set oSQLServer = Nothing End If End SubPrivate Sub cmdOK_Click() On Error GoTo ErrorHandlerSet oSQLServer = New SQLDMO.SQLServeroSQLServer.LoginTimeout = -1 '-1 is the ODBC default (60) seconds 'Connect to the Server If chkAuthentication Then With oSQLServer 'Use NT Authentication .LoginSecure = True 'Do not reconnect automatically .AutoReConnect = False 'Now connect .Connect txtServer.Text End With Else With oSQLServer 'Use SQL Server Authentication .LoginSecure = False 'Do not reconnect automatically .AutoReConnect = False 'Use SQL Security .Connect txtServer.Text, sUsername, sPasswd End With End IfMsgBox "Your Login: " & oSQLServer.Login 'Show next form'frm.show 'Me.HideExit SubErrorHandler: MsgBox "Error: " & Err.Number & " " & Err.Description, vbOKOnly, "Login Error"End SubPrivate Sub Form_Load() Dim i As Integer 'Use the SQL DMO Application Object to find the available SQL Servers Set oSQLServerDMOApp = New SQLDMO.Application
Dim namX As NameList Set namX = oSQLServerDMOApp.ListAvailableSQLServers For i = 1 To namX.Count txtServer.AddItem namX.Item(i) Next 'Show top server txtServer.ListIndex = 0 End SubPrivate Sub Form_Unload(Cancel As Integer) If Not oSQLServer Is Nothing Then 'When done with the connection to SQLServer you must Disconnect oSQLServer.DisConnect End If
Set oSQLServer = Nothing Set oSQLServerDMOApp = Nothing End SubPrivate Sub oSQLServer_CommandSent(ByVal SQLCommand As String) 'CommandSent event occurs when SQL-DMO submits a Transact-SQL command batch to the connected instance Dim sMsg As String sMsg = "CommandSent: " & vbCrLf & _ SQLCommandMsgBox sMsg, vbOKOnly, "SQLServer Object Event" End SubPrivate Function oSQLServer_ConnectionBroken(ByVal Message As String) As Boolean 'ConnectionBroken event occurs when a connected SQLServer object loses its connection Dim sMsg As String sMsg = "ConnectionBroken: " & vbCrLf & _ MessageMsgBox sMsg, vbOKOnly, "SQLServer Object Event" End FunctionPrivate Function oSQLServer_QueryTimeout(ByVal Message As String) As Boolean 'QueryTimeout event occurs when Microsoft?SQL Server?cannot complete execution of a Transact-SQL command batch within a user-defined period of time Dim sMsg As String sMsg = "QueryTimeout: " & vbCrLf & _ MessageMsgBox sMsg, vbOKOnly, "SQLServer Object Event" End FunctionPrivate Sub oSQLServer_RemoteLoginFailed(ByVal Severity As Long, ByVal MessageNumber As Long, ByVal MessageState As Long, ByVal Message As String) 'RemoteLoginFailed event occurs when an instance of Microsoft?SQL Server?attempts to connect to a remote server fails Dim sMsg As String sMsg = "RemoteLoginFailed: " & vbCrLf & _ "Severity: " & Severity & vbCrLf & _ "MessageNumber: " & MessageNumber & vbCrLf & _ "MessageState: " & MessageState & vbCrLf & _ "Message: " & MessageMsgBox sMsg, vbOKOnly, "SQLServer Object Event" End SubPrivate Sub oSQLServer_ServerMessage(ByVal Severity As Long, ByVal MessageNumber As Long, ByVal MessageState As Long, ByVal Message As String) 'ServerMessage event occurs when a Microsoft?SQL Server?success-with-information message is returned to the SQL-DMO application Dim sMsg As String sMsg = "ServerMessage: " & vbCrLf & _ "Severity: " & Severity & vbCrLf & _ "MessageNumber: " & MessageNumber & vbCrLf & _ "MessageState: " & MessageState & vbCrLf & _ "Message: " & MessageMsgBox sMsg, vbOKOnly, "SQLServer Object Event" End SubPrivate Sub txtPasswd_Change() sPasswd = txtPasswd.Text End SubPrivate Sub txtUsername_Change() sUsername = txtUsername.Text End Sub
Dim svr As New SQLDMO.SQLServer
Dim db As SQLDMO.Database
Dim i As Integer
svr.Connect "(local)", "sa"
Set db = svr.Databases("pubs")
For i = 1 To db.Tables.Count
Debug.Print db.Tables(i).Name
next
'In form
'Combox --txtserver
'checkbox ---chkAuthentication
'textbox --txtUsername,txtPasswd
'CommandButton --cmdOK,cmdDisconnect,cmdCancelPrivate sUsername As String
Private sPasswd As String'Use the SQL DMO object to find available SQL Servers
Private oSQLServerDMOApp As SQLDMO.Application'Use the SQLServer object to connect to a specific server
Public WithEvents oSQLServer As SQLDMO.SQLServer'Determine is NT Authentication is to be used or not
Private Sub chkAuthentication_Click()
If chkAuthentication.Value = vbChecked Then
'This assumes a trusted connection
Frame1.Enabled = False
Label1.Enabled = False
Label2.Enabled = False
Else
'This assumes a login is required
Frame1.Enabled = True
Label1.Enabled = True
Label2.Enabled = True
End If
End SubPrivate Sub cmdCancel_Click()
Unload Me
End SubPrivate Sub cmdDisconnect_Click()
'When done with the connection to SQLServer you must Disconnect
If Not oSQLServer Is Nothing Then
oSQLServer.DisConnect
Set oSQLServer = Nothing
End If
End SubPrivate Sub cmdOK_Click()
On Error GoTo ErrorHandlerSet oSQLServer = New SQLDMO.SQLServeroSQLServer.LoginTimeout = -1 '-1 is the ODBC default (60) seconds
'Connect to the Server
If chkAuthentication Then
With oSQLServer
'Use NT Authentication
.LoginSecure = True
'Do not reconnect automatically
.AutoReConnect = False
'Now connect
.Connect txtServer.Text
End With
Else
With oSQLServer
'Use SQL Server Authentication
.LoginSecure = False
'Do not reconnect automatically
.AutoReConnect = False
'Use SQL Security
.Connect txtServer.Text, sUsername, sPasswd
End With
End IfMsgBox "Your Login: " & oSQLServer.Login
'Show next form'frm.show
'Me.HideExit SubErrorHandler:
MsgBox "Error: " & Err.Number & " " & Err.Description, vbOKOnly, "Login Error"End SubPrivate Sub Form_Load()
Dim i As Integer
'Use the SQL DMO Application Object to find the available SQL Servers
Set oSQLServerDMOApp = New SQLDMO.Application
Dim namX As NameList
Set namX = oSQLServerDMOApp.ListAvailableSQLServers
For i = 1 To namX.Count
txtServer.AddItem namX.Item(i)
Next
'Show top server
txtServer.ListIndex = 0
End SubPrivate Sub Form_Unload(Cancel As Integer)
If Not oSQLServer Is Nothing Then
'When done with the connection to SQLServer you must Disconnect
oSQLServer.DisConnect
End If
Set oSQLServer = Nothing
Set oSQLServerDMOApp = Nothing
End SubPrivate Sub oSQLServer_CommandSent(ByVal SQLCommand As String)
'CommandSent event occurs when SQL-DMO submits a Transact-SQL command batch to the connected instance
Dim sMsg As String
sMsg = "CommandSent: " & vbCrLf & _
SQLCommandMsgBox sMsg, vbOKOnly, "SQLServer Object Event"
End SubPrivate Function oSQLServer_ConnectionBroken(ByVal Message As String) As Boolean
'ConnectionBroken event occurs when a connected SQLServer object loses its connection
Dim sMsg As String
sMsg = "ConnectionBroken: " & vbCrLf & _
MessageMsgBox sMsg, vbOKOnly, "SQLServer Object Event"
End FunctionPrivate Function oSQLServer_QueryTimeout(ByVal Message As String) As Boolean
'QueryTimeout event occurs when Microsoft?SQL Server?cannot complete execution of a Transact-SQL command batch within a user-defined period of time
Dim sMsg As String
sMsg = "QueryTimeout: " & vbCrLf & _
MessageMsgBox sMsg, vbOKOnly, "SQLServer Object Event"
End FunctionPrivate Sub oSQLServer_RemoteLoginFailed(ByVal Severity As Long, ByVal MessageNumber As Long, ByVal MessageState As Long, ByVal Message As String)
'RemoteLoginFailed event occurs when an instance of Microsoft?SQL Server?attempts to connect to a remote server fails
Dim sMsg As String
sMsg = "RemoteLoginFailed: " & vbCrLf & _
"Severity: " & Severity & vbCrLf & _
"MessageNumber: " & MessageNumber & vbCrLf & _
"MessageState: " & MessageState & vbCrLf & _
"Message: " & MessageMsgBox sMsg, vbOKOnly, "SQLServer Object Event"
End SubPrivate Sub oSQLServer_ServerMessage(ByVal Severity As Long, ByVal MessageNumber As Long, ByVal MessageState As Long, ByVal Message As String)
'ServerMessage event occurs when a Microsoft?SQL Server?success-with-information message is returned to the SQL-DMO application
Dim sMsg As String
sMsg = "ServerMessage: " & vbCrLf & _
"Severity: " & Severity & vbCrLf & _
"MessageNumber: " & MessageNumber & vbCrLf & _
"MessageState: " & MessageState & vbCrLf & _
"Message: " & MessageMsgBox sMsg, vbOKOnly, "SQLServer Object Event"
End SubPrivate Sub txtPasswd_Change()
sPasswd = txtPasswd.Text
End SubPrivate Sub txtUsername_Change()
sUsername = txtUsername.Text
End Sub