QRe: Setting a combo Rowsource from SQL server

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



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


.

Relevant Pages

  • Project Error
    ... Private Declare Sub Sleep Lib "Kernel32" ... Dim strDataSrc As String ...
    (microsoft.public.vb.bugs)
  • Re: Is there a way to prevent a RichTextBox from scrolling?
    ... Private _isRegex As Boolean ... Public Sub New(ByVal thispattern As String, ... Dim entry As tDict ...
    (microsoft.public.dotnet.framework.windowsforms.controls)
  • Excel Listing tool using VB
    ... Sub ListFiles2() ... Dim directories() As String, CurrentDirectory As String ... Dim dirtopaste, dirok ...
    (microsoft.public.vb.general.discussion)
  • Form Error
    ... SMSDS_CallerID As String ... Private Declare Sub Sleep Lib "kernel32" ... Dim ComString As String ... Dim AppPath As String, FreeFileNo% ...
    (microsoft.public.vb.bugs)
  • Re: Encrypt/hide Password
    ... Public Sub New(ByVal strCryptoName As String) ... ' instantiated crypto class. ... Dim fsKey As New FileStream(strSaveToPath, FileMode.OpenOrCreate, _ ...
    (microsoft.public.scripting.wsh)