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
Hi Michael,
is it the only query that doen't work?
Sylvere.
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
*******
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.
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...
' 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
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