Link to home
Start Free TrialLog in
Avatar of AkAlan
AkAlan

asked on

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

ASKER CERTIFIED SOLUTION
Avatar of Si Ball
Si Ball
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
Avatar of AkAlan
AkAlan

ASKER

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.
Avatar of AkAlan

ASKER

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.
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
Avatar of AkAlan

ASKER

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.
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?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of AkAlan

ASKER

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