topic Re: Connect to SQL Database in Inventor Programming Forum

Connect to SQL Database

Anonymous — Fri, 07 Dec 2007 16:47:50 GMT

I would like to be able to connect to an SQL database via code in VBA. I have been struggling the code to open the connection. Does anyone have experience doing this or some sample code on how to establish the connection? Here is what I have so far:

Public Sub GetData

Dim oConn As New ADODB.Connection

Dim strConn As String
strConn = "PROVIDER=SQLOLEDB;"
strConn = strConn & "Data Source=ServerName;Initial Catalog = XYZDatabase;"
strConn = strConn & " Integrated Security = SSPI;"

oConn.Open strConn

'Open recordset and manipulate data here

oConn.Close

End Sub

I get a User Type not defined error on Dim oConn As New ADODB.Connection. Thanks for any help. -Rob

Re: Connect to SQL Database

Anonymous — Fri, 07 Dec 2007 20:06:06 GMT

You first need to set a reference to the "Microsoft ActiveX Data Objects 2.x
Library" using .

Joe ...

wrote in message news:5795555@discussion.autodesk.com...
I would like to be able to connect to an SQL database via code in VBA. I
have been struggling the code to open the connection. Does anyone have
experience doing this or some sample code on how to establish the
connection? Here is what I have so far:

Public Sub GetData

Dim oConn As New ADODB.Connection

Dim strConn As String
strConn = "PROVIDER=SQLOLEDB;"
strConn = strConn & "Data Source=ServerName;Initial Catalog = XYZDatabase;"
strConn = strConn & " Integrated Security = SSPI;"

oConn.Open strConn

'Open recordset and manipulate data here

oConn.Close

End Sub

I get a User Type not defined error on Dim oConn As New ADODB.Connection.
Thanks for any help. -Rob

Re: Connect to SQL Database

Anonymous — Fri, 07 Dec 2007 20:20:24 GMT

Thanks for the reply Joe. I have the Microsoft ActiveX Data Objects 2.8 Library check box selected under Tools-->References. Do I somehow need to reference it in the code?

I have the following reference check boxes selected:
Visual Basic For Applications
Autodesk Inventor Object Library
OLE Automation
Microsoft Forms 2.0 Object Library
Microsoft ActiveX Data Objects 2.8 Library

Do I need anything else? Thanks. I have cleaned up my code a bit but it still won't connect:

Public Sub GetData_Click()

Dim oConn As New ADODB.Connection
Set oConn = CreateObject("ADODB.Connection")

Dim oRecord As New ADODB.Recordset
Set oRecord = CreateObject("ADODB.Recordset")

With oConn
.Provider = "SQLOLEDB.1;"
.ConnectionString = "Data Source=ServerName; Initial Catalog=DatabaseName; Integrated Security=SSPI"
.Open
End With

If oConn.State = adStateOpen Then
Debug.Print "Connection successfully opened."
Else
Debug.Print "Connection failed."
End If

oRecord.ActiveConnection = oConn
oRecord.Open "Select * FROM dbo.TableName", oConn, adOpenForwardOnly, adLockOptimistic

oRecord.Close
oConn.Close

End Sub

Re: Connect to SQL Database

Anonymous — Fri, 07 Dec 2007 21:07:53 GMT

Ok, I got it to work. I needed to connect to a specific SQL server instance instead of just the server.

...Data Source=ServerName\InstanceName;...

I was missing the InstanceName. Thanks.

Re: Connect to SQL Database

Anonymous — Mon, 10 Dec 2007 18:48:47 GMT

Here is the function I use to create the connection, you'll need to customize for your server. Also note that you may or may not want to use the INTEGRATED SECURITY. We have a single user account that all users use to access the SQL database. However, if INTEGRATED SECURITY=sspi, then it will try to log in as your windows login name. However, if each user has their own account, then definitely use it so you don't have to embed the password (a security risk). You could also prompt for password, but our users don't actually know who they are logging in as, they just press a button on the toolbar 🙂

-----------------------------------------------------------------------

Function CreateSQLConnection() As ADODB.Connection

Dim sProvider As String
Dim sServer As String
Dim sCatalog As String
Dim sUser As String
Dim sPassword As String

sProvider = "SQLOLEDB"
sServer = 'your server name here
sCatalog = 'your catalog here
sUser = 'your user account here
sPassword = ' your password here

' Create a connection object.
Dim cnDB As ADODB.Connection
Set cnDB = New ADODB.Connection

' 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
Debug.Print Err.Description
Debug.Print Err.Number
Debug.Print Err.Source
Err.Clear
Return
End If

On Error GoTo 0

Set CreateSQLConnection = cnDB

End Function

-----------------------------------------------------------------------

Re: Connect to SQL Database

Anonymous — Mon, 10 Dec 2007 19:28:44 GMT

Josh, thanks for the info and the code. The integrated security will work in our case since each user has their own account. Half of the program data comes from our SQL based order tracking system and half is entered by the user on a form. A magic button on the form combines the two and prevents any potentially painful thinking. 😛