Solved

Excel SQL getting the last record entered

Posted on 2011-09-09
8
176 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
ID: 36510865
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
ID: 36510885
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
ID: 36510909
Oops missed a =.
strSQL = "SELECT MAX(ID) FROM Table1"

Open in new window

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 7

Expert Comment

by:Jacobfw
ID: 36510924
Are you looking for the Max(id) or the last record entered?
0
 
LVL 33

Expert Comment

by:Norie
ID: 36511005
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
ID: 36511043
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
ID: 36511149
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
ID: 36524563
thank you both for the help
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

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,…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

789 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