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

[EuniceH]

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

[Ron Weller]

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"

[EuniceH]

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

[EuniceH]

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

[EuniceH]

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

[Ron Weller]

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...

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