Solved

Excel SQL getting the last record entered

Posted on 2011-09-09
8
173 Views
Last Modified: 2012-05-12
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
Comment
Question by:murbro
  • 4
  • 3
8 Comments
 
LVL 33

Expert Comment

by:Norie
Comment Utility
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
 
LVL 7

Expert Comment

by:Jacobfw
Comment Utility
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
 
LVL 33

Expert Comment

by:Norie
Comment Utility
Oops missed a =.
strSQL = "SELECT MAX(ID) FROM Table1"

Open in new window

0
 
LVL 7

Expert Comment

by:Jacobfw
Comment Utility
Are you looking for the Max(id) or the last record entered?
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 33

Expert Comment

by:Norie
Comment Utility
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
 
LVL 7

Accepted Solution

by:
Jacobfw earned 250 total points
Comment Utility
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
 
LVL 33

Assisted Solution

by:Norie
Norie earned 250 total points
Comment Utility
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
 

Author Closing Comment

by:murbro
Comment Utility
thank you both for the help
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

772 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