Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 187
  • 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
 
NorieCommented:
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
 
NorieCommented:
Oops missed a =.
strSQL = "SELECT MAX(ID) FROM Table1"

Open in new window

0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
JacobfwCommented:
Are you looking for the Max(id) or the last record entered?
0
 
NorieCommented:
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
 
NorieCommented:
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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