Community
Inventor Programming - iLogic, Macros, AddIns & Apprentice
Inventor iLogic, Macros, AddIns & Apprentice Forum. Share your knowledge, ask questions, and explore popular Inventor topics related to programming, creating add-ins, macros, working with the API or creating iLogic tools.
cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 

How to connect to MS Access database from iLogic?

11 REPLIES 11
SOLVED
Reply
Message 1 of 12
MAKD
5184 Views, 11 Replies

How to connect to MS Access database from iLogic?

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

11 REPLIES 11
Message 2 of 12
xiaodong_liang
in reply to: MAKD

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

Message 3 of 12
fsanchou
in reply to: xiaodong_liang

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."

 

http://wikihelp.autodesk.com/Inventor/enu/2013/Help/1310-Autodesk1310/1878-iLogic1878/1879-Overview1...

 

Are you or Autodesk can show us two examples to directly read and write to Access database and SQL server?

 

Thanks

Message 4 of 12
xiaodong_liang
in reply to: fsanchou

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.

Message 5 of 12
mehatfie
in reply to: MAKD

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

Message 6 of 12
MAKD
in reply to: MAKD

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

Message 7 of 12
mehatfie
in reply to: MAKD

Thanks MAKD,

 

I've resolved this code and have working code to connect to the database. 

 

Thanks for the input!

Mitch

Message 8 of 12
MAKD
in reply to: MAKD

Hi Mitch,

 

Can you please share code in this forum if possible.

 

Thanks

Mayank

Message 9 of 12
mehatfie
in reply to: MAKD

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!!!

Message 10 of 12
ChristinaForest
in reply to: MAKD

i have this error code

 

error sql.PNG

Message 11 of 12

You need to include

Sub Main()

End Sub

at the TOP of the rule before this Sub.
Intel Xeon 3.5GHz (8 Cores)
Dual nVidia Quadro K2200 (4GB) - Dual Screen
32GB RAM
Message 12 of 12
SheldonEC
in reply to: mehatfie

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?

Can't find what you're looking for? Ask the community or share your knowledge.

Post to forums  

Autodesk Design & Make Report