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
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
Set rec = Nothing
myconn.Close
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
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