UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> pass sql results to variable    
 
   
jtwork
post Jan 4 2008, 04:00 AM
Post#1



Posts: 164
Joined: 20-December 06



I would like to pass an sql outcome value to a variable like so
ymax = docmd.runsql ("select max(id) from mytable")
how can i do this?
Go to the top of the page
 
mishej
post Jan 4 2008, 04:11 AM
Post#2


Retired Moderator
Posts: 11,289
Joined: 25-September 02
From: Milwaukee, WI


The DoCmd.RunSQL command is only for action queries - UPDATE, INSERT or DELETE. It can't handle return values as you would expect with a SELECT SQL query.
The normal way to do this is to open a recordset as defined by the DAO or ADO object model. You call the .OpenRecordset() proc with your SQL and this returns a RecordSet object that can contain rows and columns of data.
In your example you just need to lookup one value. Access provides the Domain functions to do this - DLookup(), DMax(), DMin() and others - all starting with the letter "D".
Try:
CODE
mymax = DMax("[id]", "myTable")

While it is great to use a one-liner of code as opposed to 10-20 lines of code to open and handle a Recordset there are some drawbacks to this code. The Domain functions are typically slow so there might be a performance hit (depending on how often this code is called). And... in a multi-user application there are concurrency issues.
Say you had users Fred and Sally. Fred's app runs this line and get 87 back as the max value. A nano-second later Sally's app uses the same code and gets 87 as well. If the intention of your code is to add a new record with an ID of 88 then who do you think will "win"?
But that's a whole 'nother issue...
Go to the top of the page
 
ChrisO
post Jan 4 2008, 05:42 AM
Post#3


Remembered
Posts: 3,905
Joined: 24-February 03
From: Brisbane, Australia


G’day John and you mention a good point.
think it’s a “whole 'nother’ issue” that needs explaining especially since the DMax+1 is often quoted as being the method to achieve the largest unique value in a multi-user system.
Just my input on this but I believe this is one occasion to deliberately cause an error in order to increment and recover from it.
Kind regards,
Chris.
Go to the top of the page
 
mishej
post Jan 4 2008, 06:01 AM
Post#4


Retired Moderator
Posts: 11,289
Joined: 25-September 02
From: Milwaukee, WI


This is a good resource:
http://support.microsoft.com/default.aspx?...Ben-us%3B191253
Go to the top of the page
 
ChrisO
post Jan 4 2008, 07:30 AM
Post#5


Remembered
Posts: 3,905
Joined: 24-February 03
From: Brisbane, Australia


Thanks John but it will really take me about twenty or more reads to even try to understand the complexity of that article.

Certainly open to discussion here but this is my approach to the situation…

It is somewhat old but it is to enforce uniqueness at the lowest possible level…the table field level in this case.

Given that, in absolute terms, a field value can not be duplicated then initially we should try to write DMax and not DMax + 1 (in other words…cause the error but do so as close to the non error situation as we can.)
The error is also raised close to commit time. (Close but not specified and uncontrollable.)
The error vectors to our error handling routine which increments the value and resumes at the write and commit point.
We deliberately put the code in an error loop: -

Write DMax
If Error then
Increment DMax
Resume Write
End If
Done

If required, a counter can be placed in the error routine to cause a bail out of the error increment/write loop after what may be deemed as excessive calls.

First principal…no duplicated values will enter the table.
Each user will need to error cycle till their unique value is accepted by the table.
A user can not absolutely predict its unique number prior to acceptance by the table.

Just my thoughts on a somewhat over glossed but interesting subject.

Kind regards,
Chris.

Edited by: ChrisO on Fri Jan 4 7:37:46 EST 2008.
Go to the top of the page
 
mishej
post Jan 5 2008, 01:01 PM
Post#6


Retired Moderator
Posts: 11,289
Joined: 25-September 02
From: Milwaukee, WI


I use the code from that MSKB for my apps. It does essentially all the things you outlined but in a different manner. It maintains a table just for the key value - one field and one row in one table. This table is only locked when a client's app needs to generate the next key value.
One of the reasons I like this is the table is separate from the data and less likely that its use will somehow affect data. You don't have to query against a potentially large record source - there is only one field and one row.
The recordset is opened with a full table lock so it has complete control; if there is a locking issue it tries 10 times to lock the table before returning -1 for the next key.
But the key for me was this from the article summary:<
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    17th December 2017 - 01:21 AM