Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2003
    Posts
    2

    Unanswered: Using ado recordset to fill lstbox.rowsource

    Hi There,

    This is probably an obvious question, but I get confused a lot lately trying to migrate to ADO.

    In one database I open a connection to a second database and fill a recordset.
    Next I want to populate a listbox with this recordset. But:

    lstbox.rowsource=rst doesn't work. What am I overlooking/doing wrong?

    Set YearCNN = New Connection
    YearCNN.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Year(Now) & ".mdb;"

    Set RSTBar = New Recordset
    RSTBar.Open "SELECT * FROM crsBar", YearCNN, adOpenDynamic, adLockOptimistic
    lstBarBon.RowSource = RSTBar 'this generates an error

    LPent

  2. #2
    Join Date
    Jan 2003
    Location
    Rockville, MD
    Posts
    179
    The RowSource property needs to be a SQL statement, not a recordset object. Try this:
    Code:
    lstBarBon.RowSource = "SELECT * FROM [" & Year(Now) & ".mdb].crsBar"
    You might need to name the drive/path of the .mdb
    Code:
    lstBarBon.RowSource = "SELECT * FROM [C:\MyFolder\" & Year(Now) & ".mdb].crsBar"

  3. #3
    Join Date
    Jun 2003
    Posts
    2
    Okay, this works but what happens if I migrate this (in the future) to an SQL server?

    Could someone explain the difference between a table or query and a recordset? I always thought they were (technically) interchangeble. But since I can't assign a recordset to a rowsource in code I have come to think they are not.

  4. #4
    Join Date
    Jan 2003
    Location
    Rockville, MD
    Posts
    179
    If you plan to switch, you might want to use this syntax:

    "SELECT * FROM crsBar IN ""C:\MyFolder\" & Year(Now) & ".mdb"""

    This statement would change if you switch to SQL Server:

    "SELECT * FROM crsBar IN """" ""ODBC;dsn=<your dsn>;database=" & Year(Now) & ";uid=<login name>;pwd=<password>"""

    If you go this route, you can set a global variable upon system startup, with a string indicating the datasource. Use this string consistently throughout your app when building SQL statements. Then, when you switch to SQL Server, just change the assignment of the global variable.

    As an alternative, you could link the tables to your application, and skip the "IN" clause.

  5. #5
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    also depending on the order of the refernces you may try
    new adorecordset

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •