Refreshing ADODB Recordset on an unbound MS Access adp form

I have an unbound form in an Access project (.adp) and I am having problems with refreshing a summary form from a detail form. I thought I had this issue fixed from an earlier post but there are new problems once I deploy to my users. I am using an adodb recordset to populate an unbound summay form and then allowing the user to select a record to edit. I take them to a detail form and allow them to update the record and then resync the summary form when they exit. It all works fine when I am designing but gives an "Insufficient key column information for updating" error when the users run it. I have given them the permissions on all the underlying stored procedures and the summary form opens for the user the first time. I'll post the releavant code. Any ideas?

Here is the recordset I use to populate the summary form with:

Dim oaNum As String
 oaNum = Nz(Form_frmEngineeringHomePage.cboOaNumbers, "%")
 Dim cmd As New ADODB.Command
 Dim rst As New ADODB.Recordset
 
 With cmd
    .ActiveConnection = CurrentProject.Connection
    .CommandText = "spSelectENG_OverAndAboveDetails"
    .CommandType = adCmdStoredProc
    .CommandTimeout = 60000
    .Parameters("@OA_Nr") = oaNum
    rst.Open cmd, , , adLockOptimistic
 End With
 
 Set Me.Recordset = rst

Open in new window


Here is the command I use from the detail table to resync the summary form:

If CurrentProject.AllForms("frmOaDetails").IsLoaded Then Form_frmOaDetails.Recordset.Resync

Open in new window

LVL 6
AkAlanAsked:
Who is Participating?
 
Simon BallCommented:
googled the error code:

http://support.microsoft.com/kb/815542

Do you have primary keys?

this one came from the same search

http://support.microsoft.com/kb/814893
http://support.microsoft.com/kb/815542

Do you have primary keys?

"You have a ADO Recordset that is populated with data from a join query, and the table that is used in the join query does not have primary keys. When you try to update the data of this recordset in the database, you may receive the following error message:
Run-time error '-2147467259 (80004005)' : 
Insufficient key column information for updating or refreshing.


WORKAROUND
To work around this problem, use server-side cursor to update the recordset as follows:myCn.CursorLocation = adUseServer
myCn.Open
myRs.Open "Your SQL Query", myCn

Open in new window

0
 
Nico BontenbalCommented:
I get the same error when the table the stored procedure returns records from doesn't have a primary key. So make sure the table has a primary key, and return this primary key in the result set.
0
 
AkAlanAuthor Commented:
Well I thought I was on to something since my stored procedure uses three functions that return summed data from other tables and those tables don't have PK's but I modified the sproc to not include any linked tables or functions. Now only using one table in my select statement just for testing and I get the same error.

Problem identified. I had to give the user permissions on the table itself and now I get no error when resync command is given. The whole reason I went ot an unbound form using recordsets to control CRUD operations was so that I didn't have to give permissions directly to the table. I officially am giving up on MS Access projects.

I am going to try and use the server-side cursor to see if that works but I don't have high hopes at this time. I'll post back with results. Thanks to both of you for your help, it did put me on the right track.
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
AkAlanAuthor Commented:
I changed my cursor to server-side cursor and get the same error. I'm going to leave this post open but I need to move on. If anyone can figure out how to resync a recordset without having to give the user permissions at the table level, I would appreciate it.
0
 
Nico BontenbalCommented:
Seems like you can set the stored procedure to use for the resync command:
http://msdn.microsoft.com/en-us/library/ms676094(VS.85).aspx
That should solve your problem with the resync. But I'm afraid updating the data is not possible through a stored procedure. You might also have a look at this article that talks about using a stored procedure as the record source of the form. Might be you don't need the code but can specify the stored procedure in the recordsource of the form:
http://msdn.microsoft.com/en-us/library/aa139945(office.10).aspx#apg03_tsql
0
 
AkAlanAuthor Commented:
Thanks Nicobo, I read both articles but neither have any new information that helps my situation. I'll keep looking for a way to resync the form without having to give permissions to the table, which is a deal breaker, I will not give permissions at the table level.
0
 
Simon BallCommented:
i have a system with 8 users who need to update specific tables.  i created one user on the db server, gave it permissions to update the specific tables, and used that username to create the odbc links in the access db.

could you do the same and use that username to create your ADODB connection for this part of the system?
0
 
Nico BontenbalCommented:
@AkAlan. You are right. When I set the Resync command this stored procedure is used, but it still only works if the user has at least read permissions on the underlying table. Couldn't figure out why this is. Maybe with the SQL Server profiler you could check which commands are sent to SQL Server. But I'm afraid there is no solution for this problem.
0
 
AkAlanAuthor Commented:
@Sudonim, I  do the same thing, sot of. When I  create Logins on the SQL Server, I use Acitve Directory Roles. Then I create Users on the databases from those roles. Then I control permissions from those Users. The issue here is more of an Access Project (.adp) issue. It seems that unless the user has permissions on the table itself, (by way of being a member of the Role that the User account is made from in my case), they can't just update the table through a SELECT stored procedure, nor can they Resync an ADODB Recordset.

@Nicobo, I'm afraid I have to agree with you, I'm going to award points to both of you for the effort you put into this for me, thanks. Maybe MS will fix this issue but my understanding is they aren't putting a lot of effort into supporting adp projects.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.