How to Update a form bound to a ADO recordset

[Toga]

Access front end and SQL server back end.

I'm used to working with unbound forms and DAO.

I was told I could actually bind a form to a specific table / record using an ADO recordset (without linking the table into the db) and then be able to update it. I created the connection string and the recordset...can pull up the specific record, but it doesn't allow me to edit it. The table itself is located in the SQL Server...

Set rs = New ADODB.Recordset
rs.Open xsql, ISODBC, adOpenDynamic, adLockOptimistic

'Set the form's Recordset property to the ADO recordset
Set Me.Recordset = rs

Does anyone else do this and do you have any ideas what I might be doing wrong?

 

[Remou]

Does the table/query have a primary key?

http://lessthandot.com

 

[RoyVidar]

See

http://support.microsoft.com/kb/281998/EN-US/,

or - if you're on earlier versions, check out

http://support.microsoft.com/default.aspx?scid=kb;EN-US;227053

Roy-Vidar

 

[Toga]

Yes, The table does have a primary key.

Yes Roy, that's where he must have got it. According to that paper, I'm Opening a Separate ADO Connection but it still doesn't work.

 

[Toga]

I wonder if it could be because the SQL Server is set up for Windows Authentication.

I tried the same thing on the server at work and it works fine!

 

[ByteMyzer]

Try replacing:

rs.Open xsql, ISODBC, adOpenDynamic, adLockOptimistic

...with:

With rs
    .ActiveConnection = ISODBC
    .CursorLocation = adUseClient
    .CursorType = adOpenKeyset
    .LockType = adLockOptimistic
    .Open xsql
End With

 

[Toga]

Thanks ByteMyzer. I tried your suggestion but it still doesn't work.

I also created a seperate log in so I wasn't using windows authentication and it still doesn't allow me to edit the record set, or add new records. My connection string is as follows....

With ISODBC
.Provider "Microsoft.Access.OLEDB.10.0"
.Properties("Data Provider").Value = "SQLOLEDB"
.Properties("Data Source").Value = "My Server Name"
.Properties("Initial Catalog").Value = "My Db Name"
.Properties("User ID").Value = "My Login"
.Properties("Password").Value = "My Password"
.Open
End With

Any other suggestions would be greatly apprciated. Could it be that my op sys at home is XP Home Edition vs a Prof Edition? Grasoping at straws at this point!

 

[ByteMyzer]

Try:

With ISODBC
    .Open "Provider=sqloledb;" _
        & "Data Source=My Server Name;" _
        & "Initial Catalog=My Db Name;" _
        & "User ID=My Login;" _
        & "Password=My Password;"
End With

Without knowing your SQL Server setup at home, versus your setup at work, I don't have enough information to tell you why your home setup is not working for you.

Also, I definitely recommend upgrading to XP Pro. (I have had nothing but headaches in dealing with XP Home, but that's me)

 

[Toga]

Thanks ByteMyzer Unfortunately, that doesn't do it either.

At this point, I think your right wrt something having to be different with the set up at work, although I have no idea what it could be.

If I link the table in via an ODBC connection and use that as the record source, everything works fine. I looked at the sysobjects and I noticed one thing in the ODBC connection string that I'm unfamiliar with. "Network=DBMSSOCN" I wonder if that's someting I need to include in my ADO connection string somehow.

 

[Toga]

It's definitely something with my database setup on SQL Server. If I connect to a table on Northwind DB, it works fine.

Perhaps I'll try recreating the database....I just have no idea at all!

 

[Toga]

I guess I'll never know what the issue was. I deleted the db in SQL Server and then recreated it using the Access Upsizing Wizard and now it works fine.

Thanks for everyone's help just the same.