Use Access forms as a front end to SQL Server

[valeryk2000]

We have an Access application. I need to use its forms as a front end to SQL Server. Due to security issues all tables and queries are moved to SQL server (queries become stored procedures). In other words, SQL Server tables ARE NOT LINKED to Access form controls. Instead we are using VBA/ADO approach. I am wondering if anybody has an experience of building client/server application in this configuration? If yes, than I have several questions. One of them is issues of using subforms (continuous forms) ...

 

[pbaldy]

There are probably several of us that have done that type of thing, so ask away. Linked tables are certainly much easier. A continuous subform has to be bound to something, so in your case you're probably going to have to pull records into a local table.

 

[valeryk2000]

Well, this is exactly what I do. I have a local table (a mirror of base SQL server table) that is bound to the subform. Every new case (click the item in listbox) to which records in the subform are related would
1) delete all current records from the local tbl
2) populate this table with the new data
3) after requery the records show up in the subform

I can add (type in) a new record into subform that would be sent to SQL server table on BeforeUpdate event and refresh a content of the local table (again delete all record and repopulate it).

The problem is that requery method give me errors, and without requery the subform contains '#deleted' text

 

[pbaldy]

I'm not sure I would do it that way (okay, I wouldn't do it that way). You don't really have to empty/repopulate the local table; it already has the new info. I'd use the Insert/Update/Delete events to affect the server table appropriately.

 

[valeryk2000]

The reason is that I dump from base SQL server only records related to the specific case. Every time the user selects a new case the local table is reloaded. All selects, inserts, updates and deletes are performed by directly using SQL Server-side stored procedures. And - it works OK, however the updated info in the local table does not show up in the subform without requery - and here I have the runtime error messages.
If I am lucky enough to persuade my boss that continuous subforms are not reliable than I certainly will use simple list/text boxes and a set of command buttons (insert/update/delete).
By the way, did anyone use Access run-time applications? How reliable they are?

 

[pbaldy]

Continuous subforms are very reliable. I use them all the time.

Presuming you mean the runtime version of Access, that's what most of my users have. It's very reliable. You should have good error handling in your application, as un-handled errors will cause the application to shut down in the runtime version.

 

[LPurvis]

I agree with Paul's affirmations.
You seem to have some preconceived ideas (or at least hopes) about what's going to work for you - or rather what isn't.
Tackle the business requirement - not the technology implemented to satisfy it.

If you need to perform updates to multiple child rows then why are you maintaining the two sets of data?
I'm not saying it's never done - I'm just wondering about your reasoning for it.
It would normally be done to minimise network traffic (updating all child rows edited by the user at once). But you seem to be performing updates to the server with each row update.

In your question you said "Instead we are using VBA/ADO approach".
Do you mean, by that, that you're filling the local mirror tables with data from the server accessed using ADO code? (Filling the table data iteratively from a recordset?)
And are you performing these updates to the server through ADO code - and it's your desire to stick with ADO which is fuelling your choice of implementation?

Again - I'd just ask why.
Just to make clear, I'm a huge proponent of ADO in Access applications - particularly with SQL Server as opposed to Jet/ACE (though I happy use it then too, along with DAO too). But there should be decisions for its implementation born from particular and informed reasons.

The error messages don't speak to a lack of robustness of subforms.
Subforms are one of Access huge strengths. They've been implemented for the best part of two decades - they are not flakey. The code implementation is everything though.

As I say - maintaining focus on the goal more than the method is key.
So let's get back to the goal? Then we can fix your data/requerying issues if required.

Cheers.

 

[valeryk2000]

I am using ado command object to retrieve a recordset from stored procedure as well as to insert, update and delete (each has its own stored procedure). I like the idea of using one step updating the SQL server table. However , some of the records would be deleted, some - updated and some - inserted. This is why I am thinking of record based approach: "delete this record, update that one, insert this one". And repopulate the local table after each of these steps.
I am using DAO locally in Access, for the remote SQL Server connection I'm using ADO. Note that functionally my approach works ok. However I have error messages on control.requery event, when I want to refresh the content of the subform. And, my question actually concerns how to get rid of these error messages. May be I am using wrong events.
BUT!
Let me think over your suggestion of keeping all changes locally and send them to the SQL Server table ta the end.
THANKS!

 

[LPurvis]

Hi

OK to consider what you say:
"using ado command object to retrieve a recordset from stored procedure as well as to insert, update and delete (each has its own stored procedure). "

That's fine. But where does filling in the local table come in to that?
Certainly Access binds to native tables more easily than anything - but that loading of the local table from a recordset must be (as I mentioned previously) an iterative process. i.e. it's extra overhead - another process which must be completed with each parent record that you visit. And if the tables are indeed local then it is constant FE growth (some would say bloat, but I wouldn't - simply non-reclaimed growth).

To determine where you're going wrong with the subform errors we'd need to see the code that you're implementing.
It sounds like you're saying that you're performing operations to the data in the BeforeUpdate event of a control? That would indeed be odd (as opposed to a record/form event) and could easily be the cause of such errors.

Although binding your recordset to the form might not be what you want - I don't really see why not. The native table should be more robust (even though it isn't in your current implementation - but I'm convinced that's coding choices). But recordset binding would save a process.
If you used a disconnected recordset then the updates could be delayed (either on a per-row or per-batch basis). Since you're wanting to use SP's for all updates then the fact that form's don't support batch optimistic recordsets won't be a dissappointment. None the less - such recordsets can form the basis for comparison when performing an update (or series of updates) back to the database.
The problem with a series of record updates in one is passing that info. Even table type parameters (if you were using SQL 2008) would need to be built up as they're not directly supported in Access. And I've never loved the concept of parsing a concatenated multi-record string. But at least you can still perform updates only thse rows that need it - at the point of parent record commital. Even if you still execute the child (subform) record updates individually.

Cheers.

 

[valeryk2000]

Hello,
Thank you. Inspired by yours and pbaldy's suggestions I will try update the SQL server table after all changes for current case have been made and the users switches to another case (or exits the form). There are some limitations on recordsets that are generated by stored procedures - they are forward-only type. It is used to download current records related to the selected case from SQL Server table and populate local table. Once this is done the recordset is closed (it is useless anyway). Disconnecting this recorset does not help - you cannot navigate back and forth (no rs.movefirst command). The peculiarity of this project is that the data is highly sensitive (patients data) and no way should exist to get the data from the Access (e.g. no linked tables and no sql statements on the client side). All sql statements are therefore kept as stored procedures on the server side (as well as tables).
So I will
1) remove all codes from the continuous subform
2) create a procedure of updating SQL server table by all the changes that the user made (and which is currently in the local form-bound table).
3) This procedure will be fired on events of choice on the main form.

Regards

 

[Banana]

Hmm, just want to be sure...

You are aware it can be changed to be of a different cursor** other than forward-only? Furthermore, if you prefer to use recordsets from stored procedure, you can use ADO to generate a fully updatable recordset and bind the recordset to the form instead++?


** Cursors unfortunately have different contexts. If you're thinking/using of SQL Server's cursor where it is used to perform iterative task (e.g. DECLARE...OPEN...FETCH INTO...), it does have a option for specifying how it scrolls from forward only to dynamic along with its updatability.

However, there is also "CursorType" and "CursorLocation" in ADO, which is slightly different but also can be specified to be forward-only or other as well as whether it's server side or client side.

Therefore, if you wanted full updatability, you would want to use keyset, dynamic or static cursor in ADO. If your stored procedure has a SS cursor, then it needs to be specified as such.

++ Note that there are some "rules" as to what can be bound to form. I know that SQL Server has some more leniency than other sources, but you can't just throw just any old recordset at it; the appropriate cursor type/location has to be set in ADO before it becomes a candidate for binding to the form.

HTH.

 

[LPurvis]

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

 

[valeryk2000]

Hi Banana,
You can define cursor type and location in ado if you sent sql statement from client via connection object. Command object (that is used in calling stored procedure) returns forward-only - at least this is my experience (from SQL Server 6.5 to 2008). I would be glad if somebody proves that I am wrong.
Thanks

 

[Banana]

I don't work with Command object that often but I'm pretty sure for all major objects in ADO (Connection, Command & Recordset), you can define cursor type & locations. By default, Command & Recordsets inherit the settings from the Connection object they are bound to, unless otherwise specified in their declaration. (I'm not sure if they can override the settings, though I know that they can 'downgrade' from a Connection's default for say, dynamic type to a forward only cursor)

Either way, you still have full control over what cursor type/locations, lockedits and other settings when you instantiate a new ADO object and specify them as part of the instantiation.

HTH.

 

[valeryk2000]

Leigh,
The article describes recordsets that are opened via connection object. When you use command object the sequence of events is slightly different. This is a sample:

Set cmd = New ADODB.Command
cmd.ActiveConnection = connSql
cmd.CommandText = "sp_GetPersonNames"
cmd.CommandType = adCmdStoredProc

Set rsEnter = New ADODB.Recordset
does not work ==>> rsEnter.CursorType = adOpenKeyset
does not work ==>> rsEnter.CursorLocation = adUseClient
does not work ==>> rsEnter.LockType = adLockOptimistic
this line would not work either ==>> rsEnter.Open strSql, connSql, adOpenKeyset

Set rsEnter = cmd.Execute <<== this will open recordset

If Not rsEnter.EOF Then
etc

 

[LPurvis]

Using a command object isn't related to the concept.
It's about implementation (something I've been saying since minute one in this thread? ;-)

I'll wager you're using code analogous to

cmd.CommandText = "spWhatever"
... etc

Set rst = cmd.Execute

That will *always* return a forward-only, read-only cursor recordset type.
It's just about using the appropriate method.
Instantiate your recordset
Set rst = New ADODB.Recordset

And then open it.
Set rst.CursorLocation = adUseClient
rst.Open cmd, , adOpenStatic, adLockOptimistic

will give you a client side (and hence static) and optimistic recordset.
You're free to then disconnect that and it will have all navigation methods available to it.

Cheers.

P.S. Ah I see you've just replied and your code is as I suspected.
You then have it.

 

[valeryk2000]

Dear Banana,
I would be happy to be able to use client side cursor location. I tried it many times - this is not my theoretical idea - it is my sweat and tears...

 

[LPurvis]

Have you tried the revision then?

You say in your previous post
this line would not work either ==>> rsEnter.Open strSql, connSql, adOpenKeyset

Wouldn't work in what way?
What is strSql? You're wanting to use the command object surely? Hence the code that I suggested.

Cheers.

 

[valeryk2000]

You (or I) cannot open recordset this way from command object. strSql in this case would be SQL statement that you would sent to the database like "Select * from Emp"
Thanks

 

[Banana]

Hmmm, so in that code you show:

 sp_GetPersonNames

Does the SP process a SELECT statement and return a table as a result? That would be expected as a part of binding a recordset to the form.

Note that there is no need for a Command object in context of binding a recordset to a form. The only requisite is that the connection is of an appropriate connection and the parameters are accordingly set as detailed in the KB article that LPurvis linked.