Solved

Excel SQL getting the last record entered

Posted on 2011-09-09
8
178 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 34

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 34

Expert Comment

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

Open in new window

0
Technology Partners: 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!

 
LVL 7

Expert Comment

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

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 34

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

751 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