Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Refreshing ADODB Recordset on an unbound MS Access adp form

Posted on 2011-04-26
9
Medium Priority
?
1,050 Views
Last Modified: 2012-08-14
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

0
Comment
Question by:AkAlan
  • 4
  • 3
  • 2
9 Comments
 
LVL 15

Accepted Solution

by:
Simon Ball earned 1000 total points
ID: 35473761
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
 
LVL 22

Expert Comment

by:Nico Bontenbal
ID: 35473892
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
 
LVL 6

Author Comment

by:AkAlan
ID: 35476640
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 6

Author Comment

by:AkAlan
ID: 35476777
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
 
LVL 22

Expert Comment

by:Nico Bontenbal
ID: 35477839
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
 
LVL 6

Author Comment

by:AkAlan
ID: 35479688
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
 
LVL 15

Expert Comment

by:Simon Ball
ID: 35481580
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
 
LVL 22

Assisted Solution

by:Nico Bontenbal
Nico Bontenbal earned 1000 total points
ID: 35481894
@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
 
LVL 6

Author Comment

by:AkAlan
ID: 35484718
@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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have had my own IT business for a very long time. I started mostly with hardware and after about a year started to notice a common theme. I had shelves with software boxes -- Peachtree, Quicken, Sage, Ouickbooks -- and yet most of my clients were…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

579 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question