Access 03 - SQL Server

[JohnPapa]

I am using A03 to connect to SQL Server Express 2012 using ADO.

Please correct me if I am wrong:
It appears that an ADO Recordset should be used for READing and a Command should be used to INSERT, UPDATE, DELETE to preferably call a Stored Procedure.

To read say 1 million records from the SQL Server we can use an ADO Recordset, whose default settings use a Firehose cursor. These settings are as follows:

 [/SIZE][/FONT][/COLOR]
[COLOR=#333333][FONT=Arial][SIZE=2]CursorType = adForwardOnly [/SIZE][/FONT][/COLOR]
[COLOR=#333333][FONT=Arial][SIZE=2]CursorLocation = adUseServer [/SIZE][/FONT][/COLOR]
[COLOR=#333333][FONT=Arial][SIZE=2]LockType = adLockReadOnly [/SIZE][/FONT][/COLOR]
[COLOR=#333333][FONT=Arial][SIZE=2]CacheSize = 1 [/SIZE][/FONT][/COLOR]
[FONT=Arial][SIZE=2]

and we can use the following to display the data on a form

 [/FONT]
[FONT=Arial]rst.Open "tblClient", con, adOpenForwardOnly, adLockOptimistic[/FONT]
[FONT=Arial]Set Me.Recordset = rst[/FONT]
[FONT=Arial]

Is it advisable to store the data into a FE temp table or should the user work from the recordset?

Also, when using a Command instead of a recordset, should
CursorLocation = adUseClient?

Regards,
John

 

[mdlueck]

Greetings John,

For SQL that performs within the context of a single operation, I always use an adoCMD object and receive back from it an adoRS object. So, even SELECT statements.

When I need to scan an adoRS object, then I must start with the adoRS object, may thus define my cursor settings, and perform the operations in the two dependent methods (Fetch / FetchNext). Here the adoRS object is declared at class level so the three methods may all share the same adoRS object.

I forget what settings are associated with the adoRS object which gets created by an adoCMD object for its return object. I recall someone stating I believe that it was a forward only cursor, and no option to adjust that. So if setting custom options is important to you, then go ahead and create an adoRS object from the start.

As for download vs not to download... do you really need the records in the FE DB for more than to scan through them? If so, then perhaps download. If not, then do not download and needlessly bloat your FE DB filesize.

Your last Q: adoCMD object operating on the FE DB or BE DB? If BE, then I would think you are using Pass-Through SQL and in that case a server cursor would be the only correct choice in that case.

I have one example of using an adoRS object to "hand edit" the BE DB where Stored Procedures were not possible... part of a BE DB schema change.

  'Define attachment to database table specifics and execute commands via With block
  Set adoRStt = New ADODB.Recordset
  With adoRStt
    .ActiveConnection = ObjBEDBConnection.ADODBConnectionObj()
    .CursorType = adOpenDynamic
    .LockType = adLockOptimistic
    .Open strSQL

    'Perform operations

  End With

  'Close the database table
  adoRStt.Close

And the application was off-line / I was running solo against the BE DB. So I have no idea how well such performs in a multi-user environment.

 

[JohnPapa]

Many thanks as usual Michael,

A few comments on your reply:

1) In your code example you show, do you perform the work while the Connection is open? If this is the case, might this cause some bandwidth problems?
2) When using an adoRS object do you specify the CursorLocation (Client or Server)?
3) You refer to Fetch/FetchNext, is this user-defined functionality?
4) You mention “Here the adoRS object is declared at class level”, do you create a separate Class Module?

Regards,
John

 

[mdlueck]

1) In your code example you show, do you perform the work while the Connection is open? If this is the case, might this cause some bandwidth problems?

I clear up the ado objects after each DB transaction completes... be it a single class method performing all of the work, then I dispose of the adoCMD/adoRS objects at the end of that method, or in the case of my sample code of Select/Fetch/FetchNext then when the code returns to the original Select method, that method ends by closing / disposing the adoRS object.

So when the DB class returns control to the forms, all DB objects have been cleaned up. No long lingering connections to the BE DB.

No bandwidth problems... I have tested the application a few times working remote via VPN. Working that way, lack of the second screen is a much larger slow-down than the network speed.

2) When using an adoRS object do you specify the CursorLocation (Client or Server)?

The sample code I pasted above is a sample of how I coded before I started using Stored Procedures. So the LOC's provided are a representation of how I coded.

3) You refer to Fetch/FetchNext, is this user-defined functionality?

Yes, a collection of three methods in a class. The adoRS object is declared at the class level so that it will persist existence through the various inter class calls being performed. All is user defined functionality.

I took special care to test all possible scenarios the adoRS object might throw me... such as telling me there are more records, attempt to move next, check if there was really not additional records (in that case rrrr??? thought there were more records), etc...

4) You mention “Here the adoRS object is declared at class level”, do you create a separate Class Module?

Yes, a separate class module per logical data type the application deals with.

 

[JohnPapa]

Now that I am involved a bit more with SQL Server, it appears that my Access coding could benefit from the general way of thinking. Faster pcs can hide inefficient Access coding.

Regarding bandwidth issues, in SQL Server you would avoid opening a table with say 50K entries and view it on a form with bound fields, where the form could be open by the user for hours at a time.

Question1: From your experience, in Access-Jet/Ace would you discourage a user from opening a form with bound fields of a 50K record table and allow the user to have it open indefinitely? In other words would you avoid forms with bound fields as much as possible?

Question2: In Access I can lock a specific record that is being edited. Is there something similar in SQL Server? More specifically I would like to retrieve a record and diasallow any changes until the record is released by the user and ideally if no changes happen for a specific amount of time to unlock the record with some message going to the original user that the data he has retrieved may no longer be valid.

John

 

[Pat Hartman]

You defeat the entire benefit of using a server-side database such as SQL Server when you use techniques that are appropriate only for local Jet/ACE tables. When working with SQL Server or other RDBMS such as DB2, Oracle, etc., NEVER bind your forms to tables. The table row-count is irrelevant. Always bind your forms to queries with selection criteria that limit the number of rows returned. You will make no friend of your DBA if you make Access suck millions of rows from the server like a vampire. Add search fields on the form and use them to modify the form's RecordSource so SQL will find the new records and return only what was selected. Any server-side database engine can efficiently retrieve selected rows from millions within a second and it could take minutes or hours for Access to download all the data if it needs to do the search for you. It is design mistakes like this that give Access a bad name in the big guys camp.

You don't need to manage concurrent updates and you should not try. SQL server will automatically handle this for you. The most you have to do will be to trap the error message and convert it to something less scarey for the user.

Please do some reading on optimising Access for client/server before proceeding. You are making serious design flaws.

 

[mdlueck]

What Pat said, plus:

Question1:

Client/Server Architecture
http://www.access-programmers.co.uk/forums/showpost.php?p=1110794&postcount=5

You describe situation #1, downloading multiple records for read-only display. I would discourage against downloading "SELECT *". The only place this application performs such a query is selecting the top most level entity. Each subsequent SELECT is doing so based on the context chosen by prior screens.

Question2:

My applicaiton does no record locking what so ever. To perform UPDATE commands, my design architecture is to UPDATE WHERE ID = uniqueID AND LOGTIMESTAMP = lastknowntimestamp. If someone else changed the record while a given person had it in edit mode, the UPADTE will fail and I display a customized error message in that case instructing to ROLLBACK and begin the edit again, which downloads a fresh copy of that ONE record into Access client variables. (Runs the Stored Procedure to fetch the particular record based on ID.)

 

[Pat Hartman]

With bound forms and standard update queries, you don't need to manage the timestamp. SQL Server will automatically take care of comparing to it. If the timestamp has been changed, the server knows your app wasn't looking at the current record and so cancels the update for you. I think you get the three-option message which most users find confusing.

 

[mdlueck]

With bound forms and standard update queries, you don't need to manage the timestamp. SQL Server will automatically take care of comparing to it.

rrrrr????

If you use bound forms for Add/Edit forms, then Access comes up with what ever SQL it deems appropriate to get the job done.

I know of no way to use bound forms AND supply the custom pass-through SQL.

 

[JohnPapa]

Michael & Pat many thanks for your replies,

It appears that the "rowversion" data type would do the trick. It also appears that the "timestamp" data type has been deprecated and msdn advises the modification of existing timestamp to rowversion.

For reference puproses we can use something like

CREATE TABLE tblTest (intID int PRIMARY KEY ,intValue int, RV rowversion);

where rowversion must now be explicitly defined compared to timestamp.

The resulting RV value can now be used as per Michael's guide.

John

 

[mdlueck]

It also appears that the "timestamp" data type has been deprecated and msdn advises the modification of existing timestamp to rowversion.

Specifically I have been using:

    [logtimestamp] [datetime2](0) NOT NULL,

with SQL Server 2008 R2.

Thank you for pointing out the new rowversion datatype.