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