Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2003
    Location
    Levittown, NY
    Posts
    3

    Unhappy Unanswered: MSAccess DAO OpenRecordset returns only 1 row with SQL7

    I recently converted an MSAccess back-end to SQL7. Many of my forms use recordsets (db.OpenRecordset) to process records using VBA.

    The problem is that now the db.OpenRecordset statement retrieves only the first row of data.

    Here's the code:

    Dim db As Database
    Dim rst As Recordset
    Dim strGetUpdate As String

    Option Compare Database
    Option Explicit
    Private Sub Form_Load()

    strUserID = CurrentUser()
    strLookupUserID = "userid = '" & strUserID & "'"

    Set db = CurrentDb()
    Set rst = db.OpenRecordset("Users")

    rst.FindFirst strLookupUserID

    strUserName = rst(1)
    strUserInits = rst(2)
    strUserLoc = rst(3)

    etc....

    "Users" is a table with userid's and permissions, etc.

    When I run this, it can't find the userid because Set rst = db.OpenRecordset("Users") returns only the 1st row from the table. (It will find it however, only if the ID is the same as in the first record).

    The table is indexed, with the Userid as the primary key.

    Any help would be greatly appreciated.

    Thanx.

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    A little bad news...

    You'll have to convert from DAO recordsets to ADO recordsets to
    query linked SQL tables.
    Inspiration Through Fermentation

  3. #3
    Join Date
    Jul 2003
    Location
    Levittown, NY
    Posts
    3

    Cool

    Thanks! I had a feeling there might be no other way.

    I found some info from microsoft on how to convert DAO to ADO and made the following changes:

    Private Sub Form_Load()

    strUserID = CurrentUser()
    strLookupUserID = "userid = '" & strUserID & "'"


    Dim rst As New ADODB.Recordset
    rst.Open "Users", CurrentProject.Connection, adOpenKeySet, adLockOptimistic


    rst.Find strLookupUserID

    strUserName = rst(1)
    strUserInits = rst(2)
    strUserLoc = rst(3)

    etc...

    And it works great! Not to painful of change either. (didn't have to embed the connection string!)

    Thanks again!

Posting Permissions

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