Inventor Customization

Reply
Active Contributor
Posts: 28
Registered: ‎04-23-2010
Message 1 of 11 (1,004 Views)
Accepted Solution

How to connect to MS Access database from iLogic?

1004 Views, 10 Replies
09-10-2012 05:56 AM

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

ADN Support Specialist
xiaodong.liang
Posts: 1,190
Registered: ‎06-12-2011
Message 2 of 11 (978 Views)

Re: How to connect to MS Access database from iLogic?

09-12-2012 08:34 PM 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



Xiaodong Liang
Developer Technical Services
Autodesk Developer Network

Active Contributor
Posts: 61
Registered: ‎04-01-2010
Message 3 of 11 (954 Views)

Re: How to connect to MS Access database from iLogic?

09-24-2012 02:24 PM 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

ADN Support Specialist
xiaodong.liang
Posts: 1,190
Registered: ‎06-12-2011
Message 4 of 11 (931 Views)

Re: How to connect to MS Access database from iLogic?

09-28-2012 04:03 AM 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.



Xiaodong Liang
Developer Technical Services
Autodesk Developer Network

Mentor
mehatfie
Posts: 163
Registered: ‎02-10-2012
Message 5 of 11 (886 Views)

Re: How to connect to MS Access database from iLogic?

11-09-2012 09:22 AM 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

Active Contributor
Posts: 28
Registered: ‎04-23-2010
Message 6 of 11 (853 Views)

Re: How to connect to MS Access database from iLogic?

11-19-2012 03:57 AM 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

Mentor
mehatfie
Posts: 163
Registered: ‎02-10-2012
Message 7 of 11 (846 Views)

Re: How to connect to MS Access database from iLogic?

11-19-2012 06:17 AM 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

Active Contributor
Posts: 28
Registered: ‎04-23-2010
Message 8 of 11 (835 Views)

Re: How to connect to MS Access database from iLogic?

11-19-2012 10:00 PM in reply to: MAKD

Hi Mitch,

 

Can you please share code in this forum if possible.

 

Thanks

Mayank

Mentor
mehatfie
Posts: 163
Registered: ‎02-10-2012
Message 9 of 11 (828 Views)

Re: How to connect to MS Access database from iLogic?

11-20-2012 06:53 AM 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!!!

Distinguished Contributor
Posts: 162
Registered: ‎04-30-2012
Message 10 of 11 (724 Views)

Re: How to connect to MS Access database from iLogic?

01-24-2013 09:50 AM in reply to: MAKD

i have this error code

 

error sql.PNG

You are not logged in.

Log into access your profile, ask and answer questions, share ideas and more. Haven't signed up yet? Register

Announcements
Welcome to the new Autodesk Community!
If this is your first visit, click here to get started and make the most of the Community. Let us know what you think of the new experience in the Community Feedback Forum.

Need installation help?

Start with some of our most frequented solutions to get help installing your software.

Ask the Community


Inventor Exchange Apps

Created by the community for the community, Autodesk Exchange Apps for Autodesk Inventor helps you achieve greater speed, accuracy, and automation from concept to manufacturing.

Connect with Inventor

Twitter

Facebook

Blogs

Pinterest

Youtube