• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 196
  • Last Modified:

Excel SQL getting the last record entered

Hi

I need to modify the code below to get the  ID of the last record entered.
How do I do this. Thanks
Sub A()
    Call oAppend(Now, "another", 2)
End Sub

Public Sub oAppend(ByVal oDate As Date, ByVal oText As String, ByVal oNumber As Single)
 
    Dim oSQL As String
    
    On Error GoTo EH
    
    Set con = New ADODB.Connection
    
    con.Open "Provider=SQLOLEDB;Data Source=196.220.43.233,12244;Network Library=DBMSSOCN;Initial Catalog=test;User ID=mnmchhhh;Password=assupfaceinth3;"

    Set cmd = New ADODB.Command
    
    'Check last ID ---------------------------
    Dim oLastID As Variant
    oLastID = "SELECT MAX(ID) FROM Table1"
    With cmd
        .CommandText = oLastID
        .CommandType = adCmdText
        .ActiveConnection = con
        .Execute
    End With
End Sub

Open in new window

0
Murray Brown
Asked:
Murray Brown
  • 4
  • 3
2 Solutions
 
NorieVBA ExpertCommented:
Can't you just use Recordset instead of command?

Then you should get one record returned and it would be the MAX(ID)
   Set rst= New ADODB.Recordset
   strSQL"SELECT MAX(ID) FROM Table1"    

   rst.Open strSQL, con

   rst.Movefirst

   Msgbox rst.Fields(0).Value

Open in new window

0
 
JacobfwCommented:
This is an excellent question.  From experience the only way to do this is to have a column which is populated with the entry date/time stamp.  We do this by adding a column called Date_entered and then put a trigger on the table that populates this column on insert.  (I can supply the code if you need it).

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:912210644860
0
 
NorieVBA ExpertCommented:
Oops missed a =.
strSQL = "SELECT MAX(ID) FROM Table1"

Open in new window

0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
JacobfwCommented:
Are you looking for the Max(id) or the last record entered?
0
 
NorieVBA ExpertCommented:
Jacobfw

I originaly interpreted this in much the same way, a date field would be needed to get the last record added.

Then I looked at the posted code and saw the MAX(ID), so I've posted code for that.

If it's the last record based on the MAX(ID) then you can use a subquery.

SELECT *

FROM Table1

WHERE Table1.ID = (SELECT MAX(ID) FROM Table1)

0
 
JacobfwCommented:
Imnorie,

Correct, if ID represents a sequence ID of records entered, it would work.  However, if ID is either not unique and/or can be entred out of order (ie customer ID = 5 makes a purchase and then customer ID = 3 makes a purchase), it would not work.

Very application dependent if ID will be sufficient to determine entry sequence.

Jacob
0
 
NorieVBA ExpertCommented:
Jacob

I think we need to know more about the structure of the table.

The code includes this:
Call oAppend(Now, "another", 2)

Open in new window

Which to me indicates the date is involved somehow, and maybe some other things.

If there is a date field you should be able to replace MAX(ID) with MAX(DateField).

Or even use TOP 1.

SELECT TOP 1, Table1.*

FROM TABLE1

ORDER BY Table1.DateField DESC;
0
 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
thank you both for the help
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now