Solved

Max value + MS Access

Posted on 2007-03-20
7
836 Views
Last Modified: 2008-02-26
How to retrieve the maximum value from the primary key?

I am trying to create a function that gets the maximum value from an MS Access database record.  I am creating a small jsp application to work with MS Access.  I am using a prepared statement something like this; "Select Max(table.id) from table".  I cannot seem to figure out how to get the result value.

Any help is greatly appreciated, thanks all.
0
Comment
Question by:OliviaRedhorse
  • 3
  • 3
7 Comments
 
LVL 119

Assisted Solution

by:Rey Obrero
Rey Obrero earned 100 total points
ID: 18758193
if you are using VBA

dim maxID
maxID=Dmax("ID", "NameOFTable")

in a query , your statement is correct
Select Max(table.id) from table
0
 
LVL 39

Assisted Solution

by:stevbe
stevbe earned 400 total points
ID: 18758221
I forget the names of the object types but I think you need to create a jdbc connection and open a dataset based on the SQL you posted above. Then you get the value by looking at the value of the collection of fields returned. You might be better off getting this question cross posted into a web TA
0
 

Author Comment

by:OliviaRedhorse
ID: 18758259
I keep getting the error; Invalid descriptor index

Yes, I do have a database connection.
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 39

Assisted Solution

by:stevbe
stevbe earned 400 total points
ID: 18758355
SELECT Max([CustID]) AS Expr1 FROM tblCust;

where are you getting the error? In executing the pull of data or in trying to get the value out of the object returned? Do you have to *fill* and object after you describe it? It have been 4 years since I did anything with java/jsp I just donlt remeber the details.
0
 

Author Comment

by:OliviaRedhorse
ID: 18759440
This is what I am working with right now;

String sql = "Select Max(table.id) from table";
            PreparedStatement pstmt = con.prepareStatement(sql);
            ResultSet rset = pstmt.executeQuery();
     
            DBean dbean = new DBean();
            dbean.setNumber("What is suppose to go here?");
     
            pstmt.close();
            pstmt.close();

I am trying to get the value from the database and send it to a bean I created.  Then I can retrieve it later.
0
 
LVL 39

Accepted Solution

by:
stevbe earned 400 total points
ID: 18762846
String sql = "SELECT Max(table.id) AS MaxID FROM table";
PreparedStatement pstmt = con.prepareStatement(sql);
ResultSet rset = pstmt.executeQuery();    
rset.next();  
DBean dbean = new DBean();
dbean.setNumber(rs.getLong("MaxID");

Steve
0
 

Author Comment

by:OliviaRedhorse
ID: 18961754
Sorry for the late reply, I do have this working. Thanks
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now