Re: Setting a combo Rowsource from SQL server

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

--
HTH
Van T. Dinh
MVP (Access)


"Santiago Gomez" <sgomez@xxxxxxxxxxxxxx> wrote in message
news:eRr7Hn2cFHA.584@xxxxxxxxxxxxxxxxxxxxxxx
> 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
>
>
>


.