Use Access forms as a front end to SQL Server

[LPurvis]

>> "You (or I) cannot open recordset this way from command object"

Well, that "you" would definitely be "you" in this case. :-s
I can, and do, open a recordset from a command object such as that perfectly well.
It's a well established ADO syntax.

What error are you getting upon attempting to do so right now? (You have just tried it I assume?)
The syntax is sound.

Cheers.

 

[valeryk2000]

Leigh,

This aproach does not work (at least for me):
rs.Open cmd, , adOpenStatic, adLockOptimistic
It still is forward-only, in other words, rs.MoveLast or rs.MoveFirst do not work
Banana,
You are right, sp contain a Select statement and returns a rowset. However, to call this sp you create ADODB.Command object and open recordset through
Set rs=command.Execute
And the resulting recodset is forward only...
Regards

 

[valeryk2000]

Leigh,
OK. I will try to ran your code again. Currently it gives me error on
rs.MoveLast line. The fact that you successfully use this approach is encouraging. Hope I will be able to reproduce it
Thanks

 

[Banana]

This is from my code and it works:

Set r = New ADODB.Recordset

With r
    .ActiveConnection = "Data Source=XXXX"
    .CursorLocation = adUseClient
    .CursorType = adOpenStatic
    .LockType = adLockBatchOptimistic
    .Source = "SELECT * FROM dbo.fnPostsList(" & _
                        Me.Parent.txtThreadID & _
                        "," & CurrentPage & _
                        "," & MaxPageSize & ")"
    .Open
End With

Set Me.Recordset = r

Note no need for creating ADO Connection or Command objects. Also note that the Source is selecting from a table-valued function in T-SQL, something that can't be done in native Jet.

I think I said earlier that it is not required to have all objects; you can specify all the parameters you need just in time and ADO will implicitly create the appropriate objects taking in any arguments whenever given or using default if none are given.

The only bothersome thing is that it's not easy to know what is default exactly because it can change based on many other factors (e.g. a client-side cursor can only be static; keyset cursor would be ignored and thus default for cursor type would change if a client side was chosen for cursor location)


Not that my code is any better than Leigh's and Leigh know far more about ADO than I could possibly hope to.

 

[LPurvis]

What is the error you receive?

What is the definition of your SP?

 

[LPurvis]

I agree Banana that there isn't any need for a command object.
But it's bugging me that, given that commands are used throughout for updates and retrieval, this isn't a simple implementation while still using that command.

I can't help but feel we're not seeing something.
A piece of the overall puzzle that, once sat down infront of, would present pretty quickly.

Cheers.

 

[Banana]

Maybe a silly question, but could it be somehow related to what driver is being used?

 

[valeryk2000]

Leigh,

cmd.CommandText = "sp_GetReviewCriteria"
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Refresh
cmd.Parameters(1).Value = lngCase
'Set rsDb = cmd.Execute <<=== this line is currently commented out
rsDb.Open cmd, , adOpenKeyset, adLockOptimistic
rsDb.MoveNext <<<<=== OK
rsDb.MoveFirst <<<==== ERROR:
Run-Time error '-2147217896:
Rowset position cannot be restarted

Microsoft explaination - recordset is forward only
http://support.microsoft.com/kb/174225/en-us

 

[valeryk2000]

Dear Banana,
In your code you send SQL statement from client side and open recordset through Connection object. In this configuration you enjoy full control over the recordset type and cursor location. I HAVE to use stored procedure due to security issue. In other words if my Access code contains "SELECT DISTINCT PtID FROM tblPatients" somebody can open Access file, run "SELECT * FROM tblPatients" and get sensitive information as well as table structure from the remote SQL Server database. Stored procedure allows to keep SQL statement hidden from the end-user.
Thank you

 

[Banana]

Actually, it still works whether it's just a SQL statement, a table-valued function (as in my example) or a stored procedure. I just tested this on a stored procedure I have:

I used the same code as above, changing one line:

.Source = "EXEC [dbo].[NameOfMySP]"

Of course, the SP has to return a table, though.

 

[LPurvis]

Driver choice did cross my mind. But it would be both inconsistent and surprising (as the difference is so specific and small).
I'd still suggest trying the opposite driver to that which you're using (i.e. if SQL Server then use Native - or vice versa).
You may just need an update on your driver version (but switching to the driver as a test could indicate that version is worth persuing).

Of Banana's suggestion to try the SQL statement - I'd agree. Not as a new implementation for you to adopt (you've explained why not) but this is the sort of process of elimination that you should perform as a test. Have you tested it? If it works it tells you something. If not - then it still tells you something.

There's nothing preventing you from also using the EXEC syntax offered (unless you're using and retrieving Output Parameter values from the SP's in question - which is a perfectly reasonable requirement and common too.
If Input parameters are required then that's, obviously, easily handled
"EXEC proc_YourName 'Param1', 'Param2'"

You've verified, not only that you can't freely navigate the recordset - but that it contains data yes? i.e. it has the same data as would be fetched otherwise contained in it?

And finally - that definition of the SP again. Could be informative in itself.

Cheers.

 

[BobMcClellan]

jumping in late here but.....
why not just use an .adp as opposed to an .mdb
I use SQL2005 to backend apps across our WAN.
Performance is great.
I use forms with many subforms.
Each subForm can be reset from the OnCurrent Event of the main form.

Simply set the param for each subform from there.
..like this..
Forms![frm_Warranty_Main].frm_Warranty_JobData.Form.InputParameters = "@WarrantyId=" & Me.WarrantyID

hth,
..bob

 

[Banana]

It's certainly an option.

The only concern would be that ADPs hasn't been enhanced/upgraded/updated since it came out in 2007, and there are no solid plan for ADP. Last time Microsoft remarked, it was recommended to develop new applications using .ACCDB instead.

Whereas Access 2010 has some promises to bring several new functionalities, it's quiet in ADP-land. So be mindful of that fact.

 

[LPurvis]

An option it is and I, personally, wouldn't worry too much yet that ADP's are less than flavour of the month. (AFAIK they've not even been depricated as is often mentioned). And that MS recommend linked tables doesn't compel me to move to that alone as a solution - as I'm sure it doesn't many others ;-)

If it's the technology you choose to use then fine. Of course it's a shift of thinking slightly. No local tables and ADO only code.
But there is more direct integration with server objects (such as SP's).
Of course we'll see what future versions of Access bring to the table to lure folks away from them (I don't believe for one minute MS will just drop them and say "OK - linked tables it is").

None the less - I wouldn't go down the upheaval of the shift in format due to this one issue. It just needs fixing/addressing.

Cheers.

 

[valeryk2000]

Leigh and all,
1. Believe me I tried everything before bringing up my problem(s) to your attention, including using local sql statement to open ANY type of recordset including a dynamic one. Banana, I will try "Exec..." code, thanks. Does rst.MoveFirst work?
2. is my first experience to make an application using Access as a front end (this is a requirement, and actually we upsizing an existing Access mdb to SQL Server). No doubt I have a lot of surprises with Access controls so I am learning, and, thanks to this forum, so far was able to solve many problems.
3 Trying different driver sounds interesting, and I will test this idea. However dealing with command object in different ADO and SQL Server versions (as well as Oracle) I always had a strong impression that command object returns forward-only type of recordset.
4 I am thinking of run-time Access application. This does not exclude sticking to the current strategy - to keep all sql logics at the server side. Why? In the future we plan to switch to a web-based front end (asp.net and ado.net). And I expect that the current configuration would make the transition easy.
5 Some of my stored procedures return output parameters - but in this case we do not have recordsets. Practically all insert, update and delete SPs returns value (to check if the procedure succeded).
6 Bob, does OnCurrent work with LINKED tables?
7 As for the future of Access, I expect that MS will introduce dotnet with all possible consequences - but I am not an Access expert, I am sure you guys know better.

 

[LPurvis]

1. Again, in a standard scenario, rst.MoveFirst works - because the recordset can have whatever type of cursor that the provider supports (for example Jet doesn't support Dynamic). SQL Server providers support all cursor types.

2. OK cool.

3. Trust me - it doens't. Command objects would be much more limited than they are. Using the Command in the recordset Source is perfectly viable. And, by itself, should not limit the types available to be returned.
Again - you need to test using the SQL statement and EXEC statement instead of the command object. (And both are important - it's very probable that the SQL statement could return a different type whereas the Exec returns ForwardOnly).

And there's still no sign of that SP definition being posted?
Is it sensitive in some way? (I can't imagine why really).

4. Runtime doesn't really affect the consideration. All of Access important functionality is still available in a Runtime install. (I don't include default user functionality as Filter by Form as important - apologies to the users who like it).

As for moving platforms... well. Yes having developed all your data procedures through SP's is technically more proper and will mean you can adopt the same philosophy in your web FE. But the overall work isn't much different. You're spending time now in the Access phase creating functionality you'll also use in the web phase. It's saving you the time and effort of implementing it later. You'd have had to do it then otherwise, so it's just a question of spending the time (and money?) now rather than later.
Access offers many RAD features for working with databases. You're not using so many of them in your scenario. So that's costing you time now - to save yourself time later. I don't think there's a substantial overall time saving here. Some yes - but not as much as some might think.

5. That's fine and quite normal. They could be present in your recordset ones too - but not necessarily.

6. I'm not Bob, but yes OnCurrent is raised for any bound form row navigation.

7. Well yes. It gets discussed a lot, both from informed warning and pure speculation so we'll not do so again here I reckon. ;-)
MS are committed to Access though. That much has been made clear. Those old scare monger stories are just that.

Cheers.

 

[valeryk2000]

Leigh,
6 --> it was the response to the post of BobMcClellan - this is where Bob came from (-:
**********************************
3. ---> Sorry, may be my English is not good enough, but did not I make it clear that I CANNOT use client side SQL statement (that without any doubt allows to open any type of recordset in both client and server sides)?
The sp definition is very simple, from the top of my head (I am using another computer and have no link to the SQL Server)
Create sp_BlaBla
@PtID Int
AS
Select * From tblKeyIssues Where PtID=@PtID
************************
And agree with the rest...

And I will be absolutely HAPPY if, as the result of this discussion, I'll learn how to return Keyset or Static or Dynamic recordset with command object...

 

[LPurvis]

Yeah I just meant I was answering that issue even though I'm not Bob.

So you can't even test using a SQL statement?
You have no permissions on the tables in question?
(I'm not talking about a change in implementation - just testing).

However regardless -you should be able to test the SP execution

rst.Open "EXEC SPName", connObject, adOpenKeyset, adLockOptimistic

or something similar.
What does that yield. Can you navigate?

Also - rather than relying on navigation to determine cursor type - we should really be testing explicitly.

After the recordset is open just a line as
Debug.Print rst.CursorType
You want to be seeing a value other than 0 (default forward only).

Without details of the SP's definition it's hard to say.
However I've known relatively simple (and similar!) SP's behave differently.
For the classic example, unless you have
Set NoCount On
at the start of your procedure then you'll generally get worse performance and ADO will not necessarily be predictable.

I've also known an SP return multiple (well two) recordsets instead of one - for no discernable reason.
Hence why I was wanting verification that you're sure this recordset is full of data as you'd expect.

Cheers.

 

[valeryk2000]

>>>> So you can't even test using a SQL statement?

I DID IT! It works perfectly to open any type of recordset.

>>>> However regardless -you should be able to test the SP execution
rst.Open "EXEC SPName", connObject, adOpenKeyset, adLockOptimistic

It returns Forward Only (rst.Move Last or MoveFirst do not work)

>>>> Without details of the SP's definition it's hard to say.

I included sp definition in my previous post

>>>>> Set NoCount On
This line is included in all stored procedures.


>>>> Hence why I was wanting verification that you're sure this recordset is full of data as you'd expect.

This recordset is full of data because it POPULATES MY LOCAL TABLE and, hence, the subform

 

[valeryk2000]

To add to my previous post:
Leigh,
I see what you mean. I certainly can use client side sql statement in development and test it (what I did). What I cannot do is to use it for production version, here I need use only stored procedures. Sorry for the mess.