CODEWITHSUNDEEP
sql-servervbaodbcms-office
wullxz
wullxz

Reputation: 19540

Office VBA ODBC to SQL Server

I have to connect a Word Document to a SQL Server with VBA in order to read and write records. Now, I'M really confused as of the many different ways to connect to a database. There's ADO, ODBC and so on. I've seen very different ways of connecting a SQL Server database. A co-worker wants me to use ODBC.

So: What is the best way to connect to a SQL Server database using ODBC in Office VBA?

An example for a SELECT and INSERT statement would also be appreciated.

Edit: Isn't there something like "System.Data.SqlClient.SqlConnection" and "System.Data.SqlClient.SqlCommand" in VB .Net/C#?

Upvotes: 2

Views: 26218

Answers (2)

Fionnuala
Fionnuala

Reputation: 91376

This example uses ODBC:

'Microsoft ActiveX Data Objects x.x library
Dim cn As New ADODB.connection
Dim rs As New ADODB.recordset

    cn.Open "DRIVER={SQL Server};SERVER=srv;" & _
       "trusted_connection=yes;DATABASE=test"

    rs.Open "SELECT * FROM table_1", cn

This example uses OleDb

'Microsoft ActiveX Data Objects x.x library
Dim cn As New ADODB.connection
Dim rs As New ADODB.recordset

    cn.Open "Provider=sqloledb;Data Source=srv;" & _
      "Initial Catalog=Test;Integrated Security=SSPI;"

    rs.Open "SELECT * FROM table_1", cn

See also: http://www.connectionstrings.com/sql-server-2008

Upvotes: 5

stb
stb

Reputation: 3513

The reason your co-worker probably wants you to use ODBC is that your company delivers standardized ODBC connections for every single workstation.

The good thing here is that you don't have to worry about connection properties except login/password, because it's all set. Imagine ODBC acting between a software (word) and a server, it makes things alot easier when it comes to changes in your company. (Server address may change and IT is able to change settings of ODBC without the end user even knowing about it)

Beste would be to talk to your co-worker and ask about his concerns. Another reason could be that ODBC might be faster than the ado one. I made this experience, too.

Upvotes: 0

Related Questions