Connect Inventor'sVBA to SQL server 2005

[YvesGingras]

Hi!

Could anyone post a portion of code or a whole procedure demonstrating how to establish a connection from Inventor's VBA to an SQL Server 2005 data base?

If it can be done, of course...

Thanks in advance.

Yves

[Anonymous]

Your best bet for this is to search the web for any
VBA implementations of connecting to SQL Server.  You'll might be able to
find some Word or Excel examples.  There's nothing specific in the
Inventor API to enable this.  Accessing the SQL database should be generic
and the use of Inventor's API will be indepent of the fact that you're getting
or setting data from SQL Server.
--
Brian Ekins
Autodesk Inventor API
Product Designer

href="http://blogs.autodesk.com/modthemachine">http://blogs.autodesk.com/modthemachine

[YvesGingras]

Thanks Brian.

By the way, I've started to read your blog and noticed your good woodworking skill...

That's a nice hobby; congratulations.

YG

[Anonymous]

here is some code I use from Excel, like Brian said, should be same for IV VB(A). What you do with the connection is your business.

BTW, this embeds the login and password, which is usually a bad idea. you could use a MsgBox to get the password if you wanted to be a little safer.






{code}

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 = "???"
sCatalog = "???"
sUser = "???"
sPassword = "???"

' 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



{/code}

[YvesGingras]

Thanks Josh,

I'm going to look at this right away...

[JBerns]

Community,

Using code examples from this page and others, I have been able to connect to and read from an SQL database.

However, the MDF and LDF files date/time stamp changes after I read the data. I have searched on how to connect/read as read-only, but have been unsuccessful. The Visual LISP code developed for AutoCAD does not alter the date/time stamp on the MDF and LDF files.

Here are my connection string and record open statements: 

.ConnectionString = "Data Source=HP-WIN10-06\SQLEXPRESS; Initial Catalog=ProNest13; Integrated Security=SSPI"

oRecord.Open "Select * FROM dbo.Customers", oConn, adOpenForwardOnly, adLockReadOnly

Should I start a new post or continue on this related post?

I look forward to the replies.

Regards,

Jerry

[vladimir_michl]

If you work with a recordset, set its LockType property:

oRs.LockType = 1 ' adLockReadOnly

Vladimir Michl, www.cadstudio.cz - www.cadforum.cz

[JBerns]

@vladimir_michl,

Is the lock setting I have in the Open statement equivalent?

oRecord.Open "Select * FROM dbo.Customers", oConn, adOpenForwardOnly, adLockReadOnly

I am basing the NON read-only status on the fact that the MDF and LDF file date/time stamp change after I read the database. These files do not change when I read the database with AutoCAD Visual LISP code.

Regards,

Jerry

[vladimir_michl]

Yes, it should be equivalent.

Vladimir Michl, www.cadstudio.cz - www.cadforum.cz

[JBerns]

@vladimir_michl,

Would you know why the MDF and LDF file change date/time after I read the database?

This is not the "Last Accessed" file property, but the Modified property.

In fact, several of the databases change date/time after I read the ProNest database. See attached image of the ...MSSQL\DATA folder.

Since I am new to database accessing, a date/time change would indicate to me that the file was modified. I am trying to avoid this.

Regards,

Jerry