Saving forms with stored procedures?

[hominamad]

Hi – I’m an experienced SQL Server and web programmer but don’t have too much experience programming in Access. For the current project I’m working on, using Access was a requirement. As I begin, I’m starting to think that maybe the way I’m approaching the design of my application isn’t really going along the “grain” for Access. I think I may be thinking more in web-type terms.

My question is this: Is it possible to have a disconnected form, that is populated via stored procedure, and then saved back to the database, also via stored procedure, when the user clicks a “Save” button of some sort? I’m using an Access Data Project because it seems to have tighter integration with my SQL Server backend. I am able to populate forms using stored procedures, but I can’t figure out how to update them the same way. Can I parse the form as I would a web and then make stored procedure calls? Is this not the recommended way to design an Access application?

Thanks in advance for any help!

H in the States

 

[RuralGuy]

Do you have a problem binding your query/table to a form? Access does all of the work for you.

 

[hominamad]

Do you have a problem binding your query/table to a form? Access does all of the work for you.

Thanks for the reply RG. I was just planning on having most of my business logic in stored procedures rather than in the application itself. Maybe this is not the Access way of doing things. I'm trying to get around it now and am binding my forms to queries, etc. I don't see the advantage to using ADP vs MDB if you can't fully utilize stored procedures like a normal client/server applicaiton would. If you can only use procedures for retrieving information, what's the difference between that and just writing regular Access queries?

I'm also having another strange problem with my bound form. I'm trying to add my own delete button to the form. When using the wizard for creating it, or even coding it myself, after the delete warning, I get an error saying "DoCmd action was canceled" or "RunCommand action was canceled" - -and the record doesn't delete. This happens whether or not I say yes to delete the record. When I delete it using the Access row selector, it deletes fine with no error.

Thanks again!

 

[RuralGuy]

M$ has depreciated the ADP and AFAIK an MDB can handle an SQL BackEnd just fine. I'm not an SQL person so some of what you say makes little sense to me. As far as your delete problem is concerned, it is difficult to say with the information supplied so far. Can you open your query directly and delete records? Is it something that SQL Server is stopping?

 

[hominamad]

M$ has depreciated the ADP and AFAIK an MDB can handle an SQL BackEnd just fine. I'm not an SQL person so some of what you say makes little sense to me. As far as your delete problem is concerned, it is difficult to say with the information supplied so far. Can you open your query directly and delete records? Is it something that SQL Server is stopping?

I can delete directly in the query. I can also delete using the in-form row selector. When I add my own button though, I get those errors. I've reduced my code down to nothing more than one line: RunCommand adCmdDeleteRecord. Gives me the regular delete warning, then the error I described. This is driving me crazy.

Thanks again...

 

[RuralGuy]

Do you have any error handling in that procedure so you can see what error is generated?

 

[hominamad]

Do you have any error handling in that procedure so you can see what error is generated?

What type of error handling do you mean exactly? I can see the error being returned by VB. It's Run-Time error '2501'. Is there someting I can do to get more information on the error?

 

[KeithG]

Also you are reffering to an unbound form which is possible in Access. You have to write a procedure to update your tables with the info from the form.

 

[RuralGuy]

The | action was canceled.
@You used a method of the DoCmd object to carry out an action in Visual Basic, but then clicked Cancel in a dialog box.
For example, you used the Close method to close a changed form, then clicked Cancel in the dialog box that asks if you want to save the changes you made to the form.@@1@@1

Do you have any other code in the form?

 

[hominamad]

I don't have any other code other than one Sub which contains the one line for delete.

 

[hominamad]

I figured it out. A FK violation was being triggered behind the scenes. When I deleted this record in query view, the error message came through. Now this leads me to another question (or two):

1) How can I get the SQL FK Violation message to propagate up to my form?

2) This is why I like to do everything via stored procedures. My ideal solution would be to show a nice warning before even trying to delete, explaining that it can't be deleted because of other relationships. Or maybe I want to have a complex delete procedure that goes through and deletes all the relationships. How can this be accomplished?

Thanks for all your help!

 

[RuralGuy]

You may need to have:
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand adCmdDeleteRecord
Do your records have a unique identifier field? How about showing it with a MsgBox before deleting as a diagnostic?

 

[RuralGuy]

I use SQL to delete records.

CurrentDb.Execute "DELETE FROM tblInvDetail WHERE DetailKey = " & ThisKey, dbFailOnError

 

[wazz]

you can enforce (or not) referential integrity and cascading deletes in the relationships window (right-click the black line that connects two tables to get to the Edit Relationships dialog)...with RI enforced you should get a fairly decent error message that tells you that you can't make a deletion because there is a record in a related (child) table.

 

[hominamad]

you can enforce (or not) referential integrity and cascading deletes in the relationships window...

I want to, and am enforcing it in SQL Server. Problem is that it doesn't seem like the SQL error is propagating up from the database to my application.

 

[boblarson]

I don't have my Access Developer's Guide to SQL Server available to me at the moment, but basically that is one of the things with SQL and Access. SQL can generate errors that Access will be completely unaware about unless you specifically utilize some code to return the error to Access.

Most of the time it isn't a problem, but in some cases it is. So, while I know that this is a problem, I can't remember exactly how to work this (I don't do it much and so I have to refer to my book).

 

[RuralGuy]

I want to, and am enforcing it in SQL Server. Problem is that it doesn't seem like the SQL error is propagating up from the database to my application.

Have you tried using the DoCmd.Execute method with SQL code and see if the error propagates back to the error handler?

 

[hominamad]

Have you tried using the DoCmd.Execute method with SQL code and see if the error propagates back to the error handler?

Thanks RG. I'm going to try this. Actually, it may be even better because then I can maybe do the actual INSERT in a stored procedure. The issue then is - will the recordset automatically update, or do I have to somehow refresh it and reset the cursor to the last row? You guys are very helpful and I appreciate it greatly.

 

[RuralGuy]

...The issue then is - will the recordset automatically update, or do I have to somehow refresh it and reset the cursor to the last row?...

You will have to do your own Requery which puts you back to the first record and then go to the record on which you want to be.

 

[pbaldy]

Just to throw out alternatives, I use the ADO Command object to pass parameters to and execute a stored procedure on SQL Server (I use mdb's, not adp's). You can pass parameters back from the SP that indicates the success or failure of it, and I suppose the specific error could be passed back as well.