vb in Access 2000/SQL Server using DAO 3.6 OpenRecordset problem


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 6 of 6

Thread: vb in Access 2000/SQL Server using DAO 3.6 OpenRecordset problem

  1. #1
    Join Date
    Apr 2011
    Posts
    6

    vb in Access 2000/SQL Server using DAO 3.6 OpenRecordset problem

    Hi, and thanks in advance for any answers.
    I have been using Access 2000, and have just today switched the back end to SQL Server 2008 Express using SSMA 2008.
    Now I'm having trouble in my Access 2000 forms trying to openrecordset or get an rst.Index.

    I do ...
    Set gdb = DBEngine(0)(0)
    Set gdbTables = OpenDatabase("C:\Documents and Settings\myfolder\mydb.mdb")
    Dim rst As DAO.Recordset

    Here are the different combos I used....and their associated errors...
    Set rst = gdbTables.OpenRecordset("tblUsers", dbOpenTable, dbSeeChanges)
    *Error 3219:Invalid Operation

    Set rst = gdbTables.OpenRecordset("tblUsers", , dbSeeChanges)
    MsgBox "got past set rst"
    With rst
    MsgBox "got past With rst"
    .Index = "PrimaryKey"
    *error 3251: Operation is not supported for this type of object.

    Set rst = gdbTables.OpenRecordset("tblUsers")
    MsgBox "got past set rst"
    With rst
    MsgBox "got past With rst"
    .Index = "PrimaryKey"
    *error 3251: Operation is not supported for this type of object.

    Set rst = gdbTables.OpenRecordset("tblUsers", dbOpenDynaset, dbSeeChanges)
    MsgBox "got past set rst"
    With rst
    MsgBox "got past With rst"
    .Index = "PrimaryKey"
    *error 3251: Operation is not supported for this type of object.

    Set rst = gdbTables.OpenRecordset("tblUsers", dbOpenSnapshot, dbSeeChanges)
    MsgBox "got past set rst"
    With rst
    MsgBox "got past With rst"
    .Index = "PrimaryKey"
    *error 3251: Operation is not supported for this type of object.

    Set rst = gdbTables.OpenRecordset("tblUsers", dbOpenDynamic, dbSeeChanges)
    *Error 3001: Invalid argument.


    Set rst = gdbTables.OpenRecordset("tblUsers", dbOpenForwardOnly, dbSeeChanges)
    MsgBox "got past set rst"
    With rst
    MsgBox "got past With rst"
    .Index = "PrimaryKey"
    *error 3251: Operation is not supported for this type of object.

    Thanks, EuniceH

  2. #2
    Join Date
    Aug 2004
    Location
    Orange, California
    Posts
    1,261
    You can't use the Index property on an attached Sql Server table.
    I suggest that you change
    "tblUsers" to "SELECT * FROM tblUsers ORDER BY (list primary key fields here)"

    For Example, if UserID is the PrimaryKey then the SQL would look like this
    "SELECT * FROM tblUsers ORDER BY UserID"

  3. #3
    Join Date
    Apr 2011
    Posts
    6

    Red face still doesn't work

    ok I tried
    Set rst = gdbTables.OpenRecordset("select * from tblUsers order by username", dbOpenDynaset, dbSeeChanges)
    With rst
    .Index = "PrimaryKey"
    .Seek "=", Me.txtUserName
    If .NoMatch Then

    and get error 3251 "Operation is not supported for this type of object." at the .Index

  4. #4
    Join Date
    Apr 2011
    Posts
    6

    gotcha....

    ok gotcha, can't use .index and .seek at all.
    What do if the username isn't there, if the select statement returns nothing? I currently get an error 3021, I guess I can use that.
    Thanks,
    EuniceH

  5. #5
    Join Date
    Apr 2011
    Posts
    6

    Cool use BOF and EOF

    I searched online and you can check for no results by checking for recordset BOF and EOF.
    Thanks for your original answer!
    EuniceH

  6. #6
    Join Date
    Aug 2004
    Location
    Orange, California
    Posts
    1,261
    Quote Originally Posted by EuniceH View Post
    ok I tried
    Set rst = gdbTables.OpenRecordset("select * from tblUsers order by username", dbOpenDynaset, dbSeeChanges)
    With rst
    .Index = "PrimaryKey"
    .Seek "=", Me.txtUserName
    If .NoMatch Then

    and get error 3251 "Operation is not supported for this type of object." at the .Index
    You have to do it all in SQL...
    Code:
    Set rst = gdbTables.OpenRecordset("select * from tblUsers where username ='" & Me.txtUserName & "' order by username", dbOpenDynaset, dbSeeChanges)
    If rst.EOF Or rst.BOF Then
      'nothing was found...
    End If

Similar Threads

  1. Replies: 1
    Last Post: 09-17-2002, 10:57 PM
  2. Replies: 1
    Last Post: 04-26-2002, 02:26 PM
  3. Access & SQL Server
    By David Jones in forum Database
    Replies: 0
    Last Post: 08-31-2001, 12:22 PM
  4. Access mdb as linked server
    By Werner Zoller in forum authorevents.vieira
    Replies: 3
    Last Post: 01-24-2001, 02:58 PM
  5. Replies: 0
    Last Post: 12-20-2000, 12:22 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center