Enumerate SQL Server databases using VBA

Question

0.00/5 (No votes)

See more:

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..

VB

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

Please help me to do this ...

Thanks in advance

Sathish

Posted 12-Aug-11 3:04am

sathish nagulan

Updated 15-Aug-11 8:36am

Maciej Los

v3

Add a Solution

Comments

Maciej Los 15-Aug-11 14:24pm

Always use "Improve question" to make changes in the text.
I have made some changes: was changed the title of question (was: Connecting to SQL Server), were changed tags. Now is much better.

2 solutions

Solution 1

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

Posted 12-Aug-11 3:09am

Wendelius

Comments

sathish nagulan 12-Aug-11 9:17am

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

Wendelius 12-Aug-11 9:33am

Here's one quick example, hope it helps:
http://support.microsoft.com/default.aspx?scid=kb;en-us;306125&sd=tech[^]

Mohammad hassani 12-Dec-17 3:02am

Hello All
which refrence should use for this line;

Dim oNames As SQLDMO.NameList
Dim oSQLApp As SQLDMO.Application

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2011-08-15T08:34:00

Solution 3

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

SQL

SELECT name FROM SYSDATABASES

The output:

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

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

VB

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

VB

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

VB

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

Posted 15-Aug-11 8:34am

Maciej Los

Updated 19-Aug-11 10:33am

v3

Comments

sathish nagulan 16-Aug-11 2:55am

As a DBA i know the query, but in VBA my second form i used this query im getting error like datasource not found, but in my first form i connected to sql.

Maciej Los 16-Aug-11 13:46pm

Show me the code of second form.

sathish nagulan 17-Aug-11 1:52am

Private Sub UserForm_Activate()
Dim rec As ADODB.Recordset
Set rec = New ADODB.Recordset

With rec

.ActiveConnection = myconn
.Open "select name from sysdatabases where name not in('master','tempdb','model','msdb','pubs','Northwind')"
DBName.AddItem
.Close

End With
myconn.Close
Set rec = Nothing
Set myconn = Nothing
End Sub

Maciej Los 17-Aug-11 11:33am

If the code is complete, i supose the error is in the line: .ActiceConnection = myconn, because object myconn have been declared only in the first form, so... in the second form it is not visible. Add the declaration of myconn and set it (as you made in the first form) and you'll find solution. If not, give me a sign (reply to my answer), and i'll try to help you again.

Read more abut scope variables ^

sathish nagulan 18-Aug-11 2:02am

This is to list all the available SQlServers in a combo box

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

After selecting the SQL Server this below code to login to the server

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

After login to the server below code to display the database names and list the table names in two combo box based on the selected database
for this i need a code..i posted here what i would have tried.

Private Sub UserForm_Activate()
Dim rec As ADODB.Recordset
Dim myconn As Connection

Set rec = New ADODB.Recordset

With rec

.ActiveConnection = myconn
.CursorLocation = adUseClient
.CursorType = adOpenDynamic
.LockType = adLockOptimistic
.Source = "select name from sysdatabases where name not in('master','tempdb','model','msdb','pubs','Northwind')"
Do While Not rec.EOF And Not rec.BOF
DBName.AddItem (rec.Fields("Name").Value)
'rec.MoveNext() = 1
Loop

'.Open

End With
'rec.Close
Set rec = Nothing
'myconn.Close
Set myconn = Nothing
End Sub

Maciej Los 18-Aug-11 15:15pm

See my answer after update.

sathish nagulan 19-Aug-11 3:54am

Thanks for your code..in that ".MoveNext()" show error like incorrect syntax

sathish nagulan 19-Aug-11 5:02am

Getting Run time error like "err no 424 Object Required"

Maciej Los 19-Aug-11 16:34pm

OK, i've made changes...

sathish nagulan 22-Aug-11 1:37am

Thanks for your continues support, still im getting the same error like "object required", this comes after login to the server it opens the second form and show this error, im using this in Excell not in access.

sathish nagulan 22-Aug-11 7:50am

Hi, the code is working fine in access can you please teach me howt to get uname.Value and pwd.Value from loaded form

sathish nagulan 22-Aug-11 8:50am

Hi, i want one more help, in my first combo box if i select database in my second combo box it has to show the table name ...i tried with the same code changing the query and values but i couldnt do that..please help me

Maciej Los 22-Aug-11 12:10pm

It's simple. Save server name, user name and password to ms excel sheet, like this:
ThisWorkbook.Worksheets("sh_name").Range("A1") = UserForm1.TxtUserName.Text
You need to do it before you show second form. Next (in the second form) you can read it to variable:
Dim user_name As String
user_name = ThisWorkbook.Worksheets("sh_name").Range("A1")

sathish nagulan 23-Aug-11 4:48am

Thank you so much...it willl save the username password rite..but we have three registered SQl servers if i login with any of the server it has to pass the connection to second form..how to do that

Maciej Los 23-Aug-11 13:38pm

OK, i'll try to help you but i don't have SQLDEMO application. So... i'll show simple example where list of sql servers is loaded from ms excel worksheet.

Maciej Los 29-Aug-11 5:37am

Download
EnumMSSQLServers.xls.
If you have any question, visit my website: http:\\losmac.republika.pl to contact with me (email button). I'm on the vacations, so... be patient.

sathish nagulan 23-Aug-11 7:55am

Hi..im trying to pass listbox selected item as my parameter to sql ..but getting error..my query is

Private Sub CommandButton1_Click()
Dim i As Integer
Dim myconn As Connection
Dim rec As ADODB.Recordset
Set rec = New ADODB.Recordset
Set myconn = New ADODB.Connection
strCS = "Provider=SQLOLEDB;" & _
"Data Source=ETSRV-NFS;" & _
"Uid=sa;" & _
"Pwd=enventure10;" & _
"trusted connection = yes;"
myconn.ConnectionString = strCS
myconn.Open strCS

With rec
.ActiveConnection = myconn
.CursorLocation = adUseClient
.CursorType = adOpenDynamic
'.LockType = adLocOptimistic
For i = 0 To Tbname.listindex
.Source = "SELECT distinct pr_1_name as PRName, count(pr_1_date) as TodayCount " & vbCr & _ here i want to pass listbox value
"FROM [" & DBName.Value & "].['" & Tbname.Text & "']" & vbCr & _
"WHERE pr_1_date = " & prdate.Value & "" & vbCr & _
"group by pr_1_name"
Next i
.Open
Sheet2.Range(a2, b2).Clear
Sheet2.Range(a2, b2).CopyFromRecordset rec
End With
rec.Close
Set rec = Nothing
myconn.Close
Set myconn = Nothing
End Sub