使用VBA枚举SQL Server数据库 [英] Enumerate SQL Server databases using VBA

问题描述

我已经在excel VBA中创建了两个表单，第一个表单用于在组合框中列出可用的sql服务器，并使用选定的sql服务器登录.连接sql server之后，在第二种形式中，它必须在一个组合框中列出所有数据库名称，并在另一个组合框中选择特定数据库，它必须显示相应数据库的表名称.

在这里，我已经完成了第一种形式，但是我不知道如何使用第二种形式来列出数据库名称和表名称.

下面的代码我用来显示服务器列表和连接数据库，我知道查询列表dtabase和表的方法，但是我想从vb userform..new到vb编码..jus对此进行帮助. />

Hi,

I have created two forms in excel VBA , First form for list available sql servers in a combo box and login with selected sql server . After connecting sql server, in the second form it has to list all the database names in one combo box and select the a particular database in another combo box it has to show table name for the respective database.

Here I have completed the first form , but i dont know how to work in the second form to list the database name and table name.

Below code i used to show server list and to connect the database, i know the query to list dtabase and tables but i want to do that from the vb userform..new to vb coding..jus need help on this..

Private Sub UserForm_Activate()
Dim i As Integer
Dim oNames As SQLDMO.NameList
Dim oSQLApp As SQLDMO.Application

Set oSQLApp = New SQLDMO.Application
Set oNames = oSQLApp.ListAvailableSQLServers()

srvname.Clear
For i = 1 To oNames.Count
    srvname.AddItem oNames.Item(i)
Next i

End Sub

Private Sub CommandButton1_Click()
Dim myconn As ADODB.Connection
Dim rec As ADODB.Recordset
Dim canConnect As Boolean
Dim strSQL As String

Set myconn = New ADODB.Connection
Set rec = New ADODB.Recordset
strCS = "Driver=SQLOLEDB;" & _
    "Data Source=" & srvname.Value & ";" & _
    "Uid=" & uname.Value & ";" & _
    "Pwd=" & pwd.Value & ";" & _
     "trusted connection = yes;"
myconn.ConnectionString = strCS
myconn.Open Chr(34) & strCS & Chr(34)
If myconn.State = adStateOpen Then
    canConnect = True
    MsgBox "connected to server"
Else
    MsgBox "Invalid username & password"
End If

SelectTable.Show
End Sub

请帮我做...

在此先感谢

Sathish

Please help me to do this ...

Thanks in advance

Sathish

推荐答案

要枚举数据库，您需要询问"您的sql server;)，如下所示:

To enumerate databases, you need to "ask" your sql server ;) like this:

SELECT name FROM SYSDATABASES

输出:

The output:

master<br />
database1<br />
database2<br />
database3

它可能是Access应用程序，所以...(您的)更改后的代码(带有错误处理程序):

It''s probably Access application, so... (your) code after changes (with error handlers):

Private Sub UserForm_Activate()
Dim i As Integer
Dim oNames As SQLDMO.NameList
Dim oSQLApp As SQLDMO.Application

On Error GoTo Err_UserForm_Activate

Set oSQLApp = New SQLDMO.Application
Set oNames = oSQLApp.ListAvailableSQLServers()
srvname.Clear
For i = 1 To oNames.Count
    srvname.AddItem oNames.Item(i)
Next i

Exit_UserForm_Activate:
    On Error Resume Next
    Set oSQLApp = Nothing
    Set oNames = Nothing
    Exit Sub

Err_UserForm_Activate:
    MsgBox Err.Description, vbExclamation, "Err no. " & Err.Number
    Resume Exit_UserForm_Activate

End Sub

Private Sub CommandButton1_Click()
Dim myconn As ADODB.Connection
Dim rec As ADODB.Recordset
Dim canConnect As Boolean
Dim strSQL As String

On Error Goto Err_CommandButton1_Click

Set myconn = New ADODB.Connection
Set rec = New ADODB.Recordset

strCS = "Driver=SQLOLEDB;" & _
    "Data Source=" & srvname.Value & ";" & _
    "Uid=" & uname.Value & ";" & _
    "Pwd=" & pwd.Value & ";" & _
    "trusted connection = yes;"
myconn.ConnectionString = strCS
myconn.Open Chr(34) & strCS & Chr(34)

If myconn.State = adStateOpen Then
    canConnect = True
    MsgBox "connected to server"
Else
    MsgBox "Invalid username & password"
End If

SelectTable.Show


Exit_CommandButton1_Click:
    On Error Resume Next
    rec.Close 'always close recordset object
    Set rec = Nothing
    myconn.Close 'always close connection!
    Set myconn = Nothing
    Exit Sub

Err_CommandButton1_Click:
    MsgBox Err.Description, vbExclamation, "Err no. " & Err.Number
    Resume Exit_CommandButton1_Click
End Sub

Private Sub UserForm_Activate()
Dim myconn As ADODB.Connection
Dim rec As ADODB.Recordset
 
On Error GoTo Err_UserForm_Activate
 
Set myconn = New ADODB.Connection
'replace uname.Value and pwd.Value with constant value or get them from loaded form like this:
'below code works for me: 
strCS = "Provider=SQLOLEDB;" & _
        "Data Source=server_name;" & _
        "Uid=user_name;" & _
        "Pwd=user_wd;" & _
        "trusted connection = yes;"
myconn.ConnectionString = strCS
myconn.Open strCS
 
Set rec = New ADODB.Recordset
With rec
    .ActiveConnection = myconn
    .CursorLocation = adUseClient
    .CursorType = adOpenDynamic
    .LockType = adLockOptimistic
    .Source = "SELECT name" & vbCr & _
                "FROM [master].[dbo].[sysdatabases]" & vbCr & _
                "where name not in " & _
                "('master','tempdb','model','msdb','pubs','Northwind')"
    .Open
    Do While Not .EOF
        DBName.AddItem .Fields("Name").Value
        .MoveNext
    Loop
End With
 
Exit_UserForm_Activate:
    On Error Resume Next
    rec.Close
    Set rec = Nothing
    myconn.Close
    Set myconn = Nothing
    Exit Sub
 
Err_UserForm_Activate:
    MsgBox Err.Description, vbExclamation, "Err. no. " & Err.Number
    Resume Exit_UserForm_Activate
End Sub

例如，您可以使用 sys.databases [sys.tables [ ^ ]列出所选数据库中的表.

You can use for example sys.databases[^] to list the databases and sys.tables[^] to list the tables in the selected database.

这篇关于使用VBA枚举SQL Server数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！