SQL connection (pull largest part number)

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)

SolidworksApi macros
Answers 6
SF
SF 2011-12-15 Best Answer

Hi Michael,

is it the only query that doen't work?

Sylvere.

SF
SF 2011-12-15

Seems to be.

Meaning, with the above code it was pulling the value ok and passing it out of the function. But when changing the SQL statement, it fails.

I've tried a LOT of things now. Below is what I am currently using. Basically it open the Whole database, and go to the last record.

It works pretty well, as long as the part list remains in order. Unfortunately, there is no safety to it. It doesn't actually sort the list, it only takes the last item in the list. If something happens like the database gets corrupted, or a typo causes the numbers to get out of order, then it will fail.

If I could figure out an SQL statement to sort it in ascending order, I think that would solve it.

(Note: Anyone trying to use this function, you need to add a reference (Tools->References) and check "Microsoft ActiveX Data Objects 2.8 Library" (Or at least I think that's the right reference))

********

Public Function GetDocNumber() As String
            Dim NewPartNumber As String
            Dim TempNumber As String
            Const dbPath As String = swDataBaseDirectory
            Dim ConString As String
            'SwApp.SendMsgToUser2 "GetDocNumber 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 * FROM PartList;", cn, adOpenDynamic, adLockOptimistic)
            'call rs.sort  ' Doesn't work
            Call rs.MoveLast
            'SwApp.SendMsgToUser2 rs!Id & ", PN=" & rs!partNumber & ", " & rs!Drafter & ", " & rs!Description & ", " & rs!DrawingDate, swMbInformation, swMbOk
   
            'Obtain the partNumber from the recordset
            TempNumber = Val(rs!partNumber)
            'SwApp.SendMsgToUser2 TempNumber, swMbInformation, swMbOk
           
            NewPartNumber = Str(TempNumber + 1)
            'SwApp.SendMsgToUser2 NewPartNumber, swMbInformation, swMbOk
           
            'Clear the Record Set
            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 = NewPartNumber
End Function

*******

IK
IK 2011-12-15

SELECT Max(tblPartnumbers.partnummer) AS MaxPartnummer

FROM tblPartnumbers;

but if you want to know last inserted autonumber you can use

If DAO

RS.Move 0, RS.LastModified

lngID = RS!partnummer

if ADO

cn.Execute "INSERT INTO tblPartnumbers .....", , adCmdText + adExecuteNoRecords

Set rs = cn.Execute("SELECT @@Identity", , adCmdText)

Debug.Print rs.Fields(0).Value

@@Identity will return last inserted autonumber on connection.

SF
SF 2011-12-27

Hello Ivana,

Thanks for the suggestions. It looks like I am using the ADO connection (I don't know enough about SQL or using it in VBA to know for certain, but that's what I think it's using).

Regardless, the "last" number isn't _exactly_ what I am looking for. Since there is a small possibility that the last number added is not actually the largest (ie. someone went in and manully changed it through MS Access for some reason), I don't want to accidentally start reusing old numbers. Really the safest thing I can think of, is to find the maximum value, then add 1, so that I guarantee I'm not reusing an old number.

Anways, have you tried using the "SELECT MAX() AS MaxNumber" statement yourself within the Solidworks API? Do you have actual working code I could study?

I tried inserting your line of code in my select statement (and a few other variations) but it does not seem to store anything in the MaxPartNumber variable. I tested it by prefilling it with dummy data, but it does not overwrite it with anything.  It does not give an error, it just doesn't copy anything to the variable.

Could it have something to do with being inside the SQL statement, that it does not recognize it as a variable in the Solidworks Script (i.e. variable scope or something)? I don't know how it could be, since I declare it at the top, but I'm not sure how VBA interprets the Call rs.Open() command.

I wonder if it might just see it as passing in a dumb string, rather than actual variable with memory locations...

I saw many SQL help files on the internet that match what you have, but none where it's used in Solidworks...

But again, thanks for the suggestions...

IK
IK 2011-12-29

' create reference to   Microsoft ActiveX Data Objects 2.8 Library
' create msAccess database
' create table tblPartnumbers
' create columns:
' Partnumber autonumber
' Username   text
' CreateDate date/time

Sub main()

Dim dbPath As String
dbPath = "c:\Autonumber.mdb"
Dim ConString As String
ConString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbPath & ";Persist Security Info=False"
'Initialize the connection object
Dim cn As New Connection

'Set the connection string
cn.ConnectionString = ConString
'Open the connection to the database

cn.Open

Dim rs As ADODB.Recordset

Dim strSQL As String
' sql select to MaxNumber number ... But it is not safe, delete last inserted record and you could see the difference
strSQL = "select max (Partnumber) as MaxNumber from  tblPartnumbers "
Set rs = cn.Execute(strSQL, , ADODB.CommandTypeEnum.adCmdText)
MsgBox "selected max number:  " & (rs.Fields("MaxNumber").Value)


' safe method how to get autonumber from access.

strSQL = "INSERT INTO tblPartnumbers (username ,CreateDate ) values ('" & Environ("username") & "', #" & Now & "#) "
cn.Execute strSQL, , ADODB.CommandTypeEnum.adCmdText + ADODB.ExecuteOptionEnum.adExecuteNoRecords
Set rs = cn.Execute("SELECT @@Identity", , ADODB.CommandTypeEnum.adCmdText)

MsgBox "Partnumber just created: " & (rs.Fields(0).Value)

End Sub

DB
DB 2012-02-08

Hi There

I dont know of you are still working on this but I have some code which may help.

Basically I have a function built into an Access database and transfers data from one of the tables to a SQL table.

Part of this function gets the maximum data from each field by using the DMax() function

'Look up the drawing number and order number

VariableName = Nz(DMax("[FieldName]", "TableName"), 0)

VariableName = Nz(DMax("[FieldName]", "TableName"), 0).

This may help you

Cheers

Declan