Populating a Combo or ListBox in Access 2000 using non-linked SQL Server tables

[tippy]

I am trying to convert an Access 2000 app by keeping my front end
forms, etc and moving my files, writing stored procs, views, etc to a
server with SQL Server 2000. I want to process as much on the server
side as possible so I don't want to use ODBC linked tables or pass-thru
queries using ODBC DSN's. I don't have the option of upgrading to
Access 2002 or higher. I need help!

I cannot figure out how to populate a combo box or list box by using
ADO to access the SQL Server tables on my backend server. I have tried
a couple of things with no success.

I tried the GetString Method but the string value built by the select
statement from the table being read is too large so I get an error when
I try to assign it to the RowSource. Sample code follows:

Dim strsql As String
Dim strFill As String
Dim rs As ADODB.Recordset

Set rs = New ADODB.Recordset

strsql = "SELECT field1, field2, field3 from table"
rs.Open strsql, gcn, adOpenDynamic, adLockReadOnly, adCmdText

strFill = rs.GetString(adClipString, columndelimeter:=";",
rowdelimeter:=";")
Me.cbo.RowSource = strFill 'Bombs-string too long
Me.cbo= Me.cbo_SubArea.ItemData(0)

Then I tried iterating through the recordset using the AddItem but the
method is not available
Sample code:

Do Until rst.EOF
UserForm1.ComboBox1.AddItem rst!field1 'ERROR here
rstEmployees.MoveNext
Loop

Any suggestions?

Tippy

 

[Douglas J. Steele]

I don't understand your reluctance to using pass-through queries. Is it
strictly that you're trying to avoid creating a DSN? The Connect property
can be DSN-less.

For example, if you're using Trusted Connection security, you can use the
following as your connect string:

ODBC;DRIVER={sql
server};SERVER=MyServerName;DATABASE=myDatabaseName;Trusted_Connection=Yes;

For other examples of the DSN-less string, start at
http://www.carlprothman.net/Default.aspx?tabid=90#ODBCDriverForSQLServer
(the example above corresponds to the 2nd string given in the Trusted
Connection security section)

Once you're set up a pass-through query to return the appropriate data,
simply use that as your RowSource for the combobox.

 

[tippy]

Doug,

That did work, thanks!

However, I was trying to stay away from the ODBC connections and go the
OLEDB, ADO route because of the network traffic. Also, there is the
issue of not always having users with a trusted connection. The base
of users are spread out all over the country. Some are part of our
organization and will have a trusted connection to the server - some
will not.

In the past, the app was created to run stand-alone and could be
downloaded from an intranet site and copied to an individual's machine.
It was just an analysis tool in which no changes to any data were
required and was much smaller. However, a lot of new features and data
have been added and some of the functionality changed to include fields
requiring updates.

A decision was made to move the the backend to a SQL Server. I am
somewhat familiar with SQL Server so I am trying to use the Access app
frontend as much as possible and then convert the queries, etc. to
views functions and stored procedures. If I was a web developer, I
would go that route but I don't have enough of those skills yet to be
comfortable.

Then I ran into this problem of populating combo boxes and list boxes
which are prevalent in the app. I'm stuck with Access 2000. I have
found in my google travels, that there is a RecordSource property in
Access 2002 and higher so the problem doesn't exist but my company has
170K+ employees and doesn't plan to upgrade any time soon.

Anyway, if you have any other suggestions where I could get away from
the ODBC connections, I would sure appreciate it. Thanks again for the
helpful suggestion on the pass-thru query.

Tippy

 

[Douglas J Steele]

I don't believe there's any difference in network traffic between using ODBC
and OleDB, or if there is, it'll be insignificant.

As to not all users being able to use TrustedConnection, for those where you
need a userid and password, you can prompt them for that information up
front, then loop through all of the queries in the QueryDefs collection,
changing the Connect property for the pass-through queries.

Once you've got the new Connect string stored in strConnect, you can use
something along the lines of:

Dim dbCurr As DAO.Database
Dim qdfCurr As DAO.QueryDef

Set dbCurr = CurrentDb()
For Each qdfCurr In dbCurr.QueryDefs
If Len(qdfCurr.Connect) > 0 Then
qdfCurr.Connect = strConnect
End If
Next qdfCurr

Set dbCurr = Nothing

 

[tippy]

I'll give it a try and thanks again for your help.