Issues with ListBox row source and SQL Server db

  • Thread starter Thread starter jerryk
  • Start date Start date
J

jerryk

Hi,

I have a List box that has as its RowSource an SQL statement of the form:

Select x,y,z from qry123.

qry123 joins a table in the SQL database with another query that runs
against the SQL database.

When I run the SQL statement as a query in my access database I get the
expected results. However, when I run it in my code the List box is not
populated. Of course this all works if I switch to the Access version of
the DB.

Any idea on what changes I need to make to SQL to make this work?

jerry
 
Of course this all works if I switch to the Access version of
the DB.

Hum, can you clarify what the heck the above means?????

Regardless, is qry123 a query in the ms-access query builder...or, a view on
the sql server?
(note that a view in sql server is the SAME concept as a saved query in
ms-access).

Are we talking about a ADP project, or a odbc with linked tables?

Try creating a view of the query on the sql server and save it..

eg create a view called my123view

Select x,y,z from qry123.


Now, on the ms-access side, go to add a new table, but add a linked table
(sql odbc), and select that view you just made...

Now, use that view as the data source for the list box.

myview

Don't use any sql, or anything extra for the data source name...but *just*
use the view name
(you can use sql that based on that view..but, just placing the view name
gives the most snappy performance).

While you are at it...you should set the sort order in the view also...

And, before you make the listbox...try clicking on the view that you just
linked (click on it while in the tables view tab..and make sure it works
BEFORE you try the listbox).
 
Back
Top