Setting a combo Rowsource from SQL server

[Santiago Gomez]

Hello,
I am trying to set the rowsource of a combo box. I want to use a query from
the SQL Server.
I can do it if the tables are linked through ODBC, but I want to create a
DSNless connection (I think that's what it is called) so that the user does
not have to enter a password.

I get an error that says:
The record source 'SELECT * from tblProjects;' SPECIFIED ON THIS FORM OR
REPORT DOES NOT EXIST.

Thanks in advance...

here's the code I'm using.


Public Sub SetDropdownRowSource2()
Dim strSQL As String

'*************** OPEN CONNECTION TO SQL SERVER
Dim cnn As ADODB.Connection
Dim strConn As String
Set cnn = New ADODB.Connection
strConn = "Driver={SQL
Server};Server=10.33.0.33;Database=Pubs;Uid=sa;Pwd=*****;"
With cnn
.Open strConn
.CursorLocation = adUseClient
End With
'****************** END CONNECTION

strSQL = "SELECT * from tblProjects;"

Forms!frmMain.cmbSelectProject.RowSource = strSQL
End Sub

 

[Andy G]

First:
Set row source type=Value list

Second:
Private Sub Form_Load()
Dim strList As String
Dim strSQL As String

' Fill the list once
If Len(Me!Combo0.RowSource & "") > 0 Then
Exit Sub
End If

' Connection
Dim cnn As ADODB.Connection
Dim strConn As String
Set cnn = New ADODB.Connection
strConn = "Driver={SQL Server};Server=servername; Database=databasename;
UID=username; PWD=password;"
With cnn
.Open strConn
.CursorLocation = adUseClient
End With

Set rst = New ADODB.Recordset
With rst
.ActiveConnection = cnn
.CursorLocation = adUseClient
.Open Source:="SELECT * from tblProjects", ActiveConnection:=cnn,
CursorType:=adOpenStatic, Options:=adCmdText

strList = rst.GetString(adClipString, columndelimeter:=";",
rowdelimeter:=";")

'Row source property maxiumum length of 2KB
Me!Combo0.RowSource = Left(strList, 2048)
End With
rst.Close
Set rst = Nothing
End Sub


This worked for me. (Portions of code taken from MS Access Dev. Guide to
SQL server)
Andy

 

[Van T. Dinh]

Since you set the RowSource to the SQL String, Access will use its own
default, i..e looking in the Tables / linked Tables and not the connection
to the SQL Server. Basically, the Connection you established is not used.

See Andy's post or you can assign the Recordset Property of the ComboBox.
part of the code I used in an Access 2002 as an example:

****
Set gcnnSQL = New ADODB.Connection
gcnnSQL.Open gMSSQL_OLEDB

Set rsUser = New ADODB.Recordset
rsUser.ActiveConnection = gcnnSQL
rsUser.CursorLocation = adUseClient
rsUser.CursorType = adOpenForwardOnly
rsUser.LockType = adLockReadOnly

' RowSource for cboUserID
strSQL = "SELECT U.UserID, U.LastName + ', ' + U.FirstName AS FullName, "
& _
" U.Password, U.frg_FactoryID, U.frg_ProductGroup " & _
" FROM dbo.tblUser AS U " & _
" WHERE (U.frg_SecurityLvlID <> 255) " & _
" ORDER BY U.LastName,U.FirstName"
rsUser.Open strSQL, , , , adCmdText
Set Me.cboUserID.Recordset = rsUser
'DoEvents
rsUser.Close
****