Hi,
There is a need to read access database to get values of iProperty and update it in Inventor.
I am not able to see any snippet for Access and not able to add reference for the same.
Is there any way to read the database and get values to update.
Thanks
Mayank Dubey
Solved! Go to Solution.
Solved by mehatfie. Go to Solution.
Hi,
Firstly, I do not see the wrapped methods of iLogic for MS Access. But I think you could write a macro of VBA doing MS Access, or a command of .NET add-in. Then execute the macro or command in iLogic.
e.g. assume we have a macro of VBA, you could call it by
ThisApplication.VBAProjects(1).InventorVBAComponents("Module1").InventorVBAMembers("VBAMain").Execute
Hi,
In the WikiHelp, it says that iLogic can
'By using VB.NET directly in your iLogic rules, you can:
[...]
Connect to external database applications such as Microsoft® Access or SQL Server."
Are you or Autodesk can show us two examples to directly read and write to Access database and SQL server?
Thanks
As I understand, that comment meants you can use the relevant API of MS Access within iLogic, instead of iLogic having the wrapped methods already.
I have not a code at this moment. I will try and get back to you when I work out it.
Hi All
Has there been anything more found for this subject?
I am now trying to retrieve data from a MS Access Database, currently we have iProperties with number values i nthem. An outside programmer created a code which takes these values and exports them to an MS Access Database, where they are then translated to mean something.
Instead of exporting those numbers, I want to use them to find what they ACTUALLY mean within the database and bring that information back into iLogic. (ie. 123456 actually means "1/4 Flat Bar Steel")
Thanks
Mitch
Hi Mitch,
You can do it writing external code in VBA or VB.net that will connect to Access and return expected value.
Thanks
Mayank
Thanks MAKD,
I've resolved this code and have working code to connect to the database.
Thanks for the input!
Mitch
Hi MAKD,
The following code is what I used as a test to connect to the database and search for a given value within the tables.
I found it easiest to connect to the database in Excel and paste a Table from the database. This way you have an easy search engine for the correct table names and once the table is pasted, all of the Column names are visible.
Some of this code I found from another user for connecting to the database (Can't leave out credit).
Put this code into VBA within Inventor and create a reference as stated at the top of the code. Then fill out you're specific information anywhere you see the question marks "??????."
---------------------------------------- Start of Code -------------------------------------------------
Function CreateSQLConnection() As ADODB.Connection
'*/*****************/**************/****************************
'*/*****************/**************/****************************
'YOU MUST ADD "References..." UNDER THE "Tools" TAB IN ORDER FOR THIS PROGRAM TO WORK
'ADD A REFERNENCE TO "Microsoft ActiveX Data Objects 2.8 Library" (Or whatever version is shown)
'*/*****************/**************/****************************
'*/*****************/**************/****************************
Dim sProvider As String
Dim sServer As String
Dim sCatalog As String
Dim sUser As String
Dim sPassword As String
sProvider = "SQLOLEDB"
sServer = "??????"
sCatalog = "??????"
sUser = "??????"
sPassword = "??????"
' Create a connection object.
Dim cnDB As ADODB.Connection
Set cnDB = New ADODB.Connection
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
' Provide the connection string.
Dim strConn As String
'Use the SQL Server OLE DB Provider.
strConn = strConn & "PROVIDER=" & sProvider & ";"
'Connect to the database on the server.
strConn = strConn & "DATA SOURCE=" & sServer & ";" & "INITIAL CATALOG=" & sCatalog & ";"
'Set user and password
strConn = strConn & "User Id=" & sUser & ";" & "Password=" & sPassword & ";"
'Use an integrated login (NOTE: DON'T USE WHEN LOGGING IN FROM OTHER USER ACCOUNT)
'strConn = strConn & "INTEGRATED SECURITY=sspi;"
'Open the connection
On Error Resume Next
cnDB.Open strConn
If Err Then
MsgBox (Err.Description)
MsgBox (Err.Number)
MsgBox (Err.Source)
Err.Clear
Return
End If
On Error GoTo 0
Set CreateSQLConnection = cnDB
'*********************************************************************
'********************* Search Code to Follow ************************
'*********************************************************************
Dim RecordTable As String
Dim ColumntoSearch As String
Dim KeyValue As Integer
Dim ColumntoRead As String
RecordTable = "??????" 'Name of Record Table (ie. UOM Description = 'tblMYTABLE')
ColumntoSearch = "??????" 'Column within the table you want to search for the key value
KeyValue = ?????? 'KeyValue to search the Column for
ColumntoRead = "??????" 'Actual Column you'd like to get the value you're looking for from
'Open _________ Record Table to Search
rs.Open Source:=RecordTable, ActiveConnection:=cnDB, CursorType:=adOpenKeyset, LockType:=adLockOptimistic, Options:=adCmdTableDirect
'Find Item in _____ Column with ____ Value
rs.Find ColumntoSearch & "=" & KeyValue, 0, adSearchForward, 0
'Display Value in ____ Column
MsgBox (rs.Fields(ColumntoRead).Value)
rs.Close
cnDB.Close
Set rs = Nothing
Set cnDB = Nothing
End Function
---------------------------------------- End of Code -------------------------------------------------
I hope this helps, if you have any questions just let me know and I'll try to help.
Regards
Mitch
Let me know if it helps.... Kudos if it works!!!
Hi,
I tried your code.
I'm getting "Invalid Use of Property" on this line 'rs = New ADODB.Recordset'...
Any ideas of what I'm doing wrong?