getting the MAX(id) value from an Access table

Results 1 to 3 of 3

Thread: getting the MAX(id) value from an Access table

  1. #1
    klovis Guest

    Default getting the MAX(id) value from an Access table

    i need to get the Max Id number from an Access table, i tried select MAX(id) FROM table, but the problem is that following this sql i have another query which inserts form data into the same table. is it feasible to have two queries on the same table? if so, do i need to open two connections, or two con.Executes for this to work?

  2. #2
    Join Date
    Dec 1969

    Default RE: getting the MAX(id) value from an Access table

    Without seeing your database code, I don&#039t know how you are doing it right now, but here&#039s how I would do it:<BR><BR>&#039Assume a connection object, objConn, has already been created and has been opened<BR>&#039Create a recordset object<BR><BR>Dim objRS<BR>Set objRS = Server.CreateObject("ADODB.Recordset")<BR><BR>&#03 9Now, get the max id value<BR>Dim strSQL<BR>strSQL = "SELECT MAX(ID) FROM TableName"<BR><BR>objRS.Open strSQL, objConn<BR><BR>&#039Save the MAX ID<BR>Dim iMaxID<BR>iMaxID = Cint(objRS(0))<BR><BR>&#039Now, use that Max ID to do something... in this case, I am deleting the record with the max ID<BR>strSQL = "DELETE FROM TableName WHERE ID = " & iMaxID<BR><BR>&#039Execute the SQL statement again with the same recordset object (need to close the old one first)<BR>objRS.Close<BR>objRS.Open strSQL, objConn<BR><BR>&#039Finally, close and clean up<BR>objRS.Close<BR>Set objRS = Nothing<BR><BR><BR><BR>Happy Programming!

  3. #3
    klovis Guest

    Default RE: getting the MAX(id) value from an Access table

    thanks a lot - i was having problems with executing 2 sql queries, but that works fine. <BR>do you happen to know how i can use an upload component to upload a file to a folder, and then link the uploaded file to a form? <BR>i am using the MAX(id) to send to an e-mail recipient so that they can click on a link, and the MAX(id) value will locate the record stored in a database with the form data. <BR>but, i also want to be able to upload a file, which relates to the form data, so that when viewing the form results, there&#039s also a link to the uploaded file?<BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts