Solved

Excel VBA SQL - getting last ID

Posted on 2011-09-10
2
186 Views
Last Modified: 2012-05-12
Hi

I am trying to get the last ID entered using the code below
and at the line   varID = .Execute()
the variable varID seems to be blank even though there is no error
How do I get the integer value of the last ID ?

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.247,1444;Network Library=DBMSSOCN;Initial Catalog=test;User ID=mu;Password=ct3;"

    Set cmd = New ADODB.Command

    'Check last ID ---------------------------
    
    Dim oSQL_lastID As String
    oSQL_lastID = "SELECT MAX(ID) FROM Table1"

    
    Dim varID As Variant
    Dim intID As String
    
    With cmd
        .CommandText = oSQL_lastID
        .CommandType = adCmdText
        .ActiveConnection = con
        '.Execute
        varID = .Execute() 'varID is blank
    End With
    intID = CStr(varID) 'Error here
    '------------------------------------------

Open in new window

0
Comment
Question by:murbro
2 Comments
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 36517464
As I said http:Q_27301456.html#a36516694 the .Execute() is returning a Recordset; therefore, you have to use it as such.

i.e., try: Set rs = .Execute()
varID = rs(0)
0
 

Author Closing Comment

by:murbro
ID: 36518225
thanks
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

707 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

18 Experts available now in Live!

Get 1:1 Help Now