I am trying to write an API macro to connect to an access mdb database (autonumber.mdb). It has one table (tblPartNumbers), with 4 fields (Partnumber, Description, Date and Drafter.

I have the connection working for the most part. (borrowed from a macro I found on the internet), but I can't seem to get the SELECT MAX() (but I'm not sure if VBA even implements it...)

So, this is what I have working.

*********

Public Function GetDocNumber() As String

    Dim partNumber As Integer

    Const dbPath As String = "(fill in your own path here)\Autonumber.mdb"

    Dim ConString As String

    SwApp.SendMsgToUser2 "Debug: GetDocNum Called", swMbInformation, swMbOk

    ConString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbPath & ";Persist Security Info=False"

    'Initialize the connection object

    Set cn = New ADODB.Connection

    'Set the connection string

    cn.ConnectionString = ConString

    'Open the connection to the database

    cn.Open

    'Initialize the recordset object

    Set rs = New ADODB.Recordset

    'Obtain the recordset through a hard-coded SQL query.

    Call rs.Open("SELECT tblPartNumbers.Partnumber FROM tblPartNumbers WHERE (((tblPartNumbers.Partnumber)= 'X1000'));", cn, adOpenDynamic, adLockOptimistic)

     'Obtain the partNumber from the recordset

    partNumber = rs!PartNumber

    SwApp.SendMsgToUser2 "Debug: partNumber = " & partNumber, swMbInformation, swMbOk

    'Clear Connection for other users

    Set rs = Nothing

    'Make sure you close the connection as Access has a maximum number of connections supported at one time (5 I think?).

    cn.Close

    Set cn = Nothing

    'Set functionVariable equal to partNumber to ensure that the string is returned to the calling procedure

    GetDocNumber = partNumber

End Function

******

The above command will pull only the record that contains the Partnumber X1000.

The problem is, I don't want to find a specific part number, I want to find the highest. I can't get the MAX command to work properly. I don't know why. No error messages that I can see, it just doesn't pull a record, and kicks out of the function without anything as far as I can tell.

I've tried a couple of other methods, but none of them seem to work.

A couple of alternatives I have been thinging about is:

Max command (that doesnt work right)

    Call rs.Open("SELECT MAX(tblPartNumbers.PartNumber) AS Partnumber FROM tblPartNumbers ;", cn, adOpenDynamic, adLockOptimistic)

Or Sort in Decending order and pull the Top Record

    Call rs.Open("SELECT TOP 1 tblPartNumber.PartNumber FROM tblPartNumbers ORDER tblPartNumber.PartNumber DESC ;", cn, adOpenDynamic, adLockOptimistic)

Again, both of these just bomb out without pulling an actual record. Any suggestions?

Thanks in advance,

Mike

(Note: There may be typo's in the above code I just changed some of the names on the fly to make it more readable, I appologize)