ODBC Connection

[Adrian T]

Hello:


This is something I have never done before. I am trying to have an open
connection with a SQL database registered in my machine's ODBC driver. Here's
my code

Private Sub Connect()

Dim wrkODBC As Workspace
Dim conData As Connection

Set wrkODBC = CreateWorkspace("NewODBCWorkspace", "admin", "", dbUseODBC)

Set conData = wrkODBC.OpenConnection("Connection1", , ,
"ODBC;DATABASE=Sale;UID=sa;PWD=sa;DSN=SQL_SERVER")

End Sub


When I run the code, an error msg says "Run-time Error:13 (Type Mismatch)".
I am almost convinced that it has something to do with referencing to the
right library. Does anyone know how to fix this?


Thanks!

 

[Jake Marx]

Hi Adrian,

I would suggest using ADO instead of DAO to access your external data. Set
a reference to "Microsoft ActiveX Data Objects 2.x Library", where x is the
highest number available to you.

Then search groups.google.com and MSDN for examples on how to connect to a
database and retrieve data via ADO using a DSN. If you need more
assistance, let us know and we'll try to help.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

 

[Rob Bovey]

Hi Adrian,

The code below is using objects from the Microsoft DAO 3.X Object
Library. If you also have the Microsoft ActiveX Data Objects 2.X Library
(ADO) referenced you will have a collision on your Connection object,
because both of these object libraries contain a connection object.

The easiest way to solve this is to remove the reference to the ADO
object library. You could also fully qualify your object variable
declarations like so:

Dim wrkODBC As DAO.Workspace
Dim conData As DAO.Connection

and it would remove the ambiguity.

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* Unsolicited e-mail replies will be ignored *

 

[TK]

Hi Adrian,

I agree with Jake that if you are learning the preferred technique is ADO.
To answer your question you need to reference “Microsoft DAO 3.x object
Libraryâ€
If you are attempting to connect to an access database and would like to use
ADO post back and I will post you an example.

Good Luck
TK

 

[Adrian T]

Thank you guys for recommending ADO. Here's the code I ended up with:

Private Sub dataextract()
Dim conData As ADODB.Connection
Dim rsQuery As ADODB.Recordset

Set conData = New ADODB.Connection
Set rsQuery = New ADODB.Recordset

conData.Open "Provider=MSDASQL;DSN=SQL_SERVER;UID=sa;PWD=sa;"

With rsQuery
.ActiveConnection = conData
.Open "SELECT TOP 20 CERTID, STA_CM FROM PIF WHERE STA_CM='TX'"
ActiveWorkbook.Worksheets(1).Range("A1").CopyFromRecordset rsQuery
.Close
End With

Set rsQuery = Nothing
Set conData = Nothing

End Sub

Hi Adrian,

I would suggest using ADO instead of DAO to access your external data. Set
a reference to "Microsoft ActiveX Data Objects 2.x Library", where x is the
highest number available to you.

Then search groups.google.com and MSDN for examples on how to connect to a
database and retrieve data via ADO using a DSN. If you need more
assistance, let us know and we'll try to help.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

Hello:


This is something I have never done before. I am trying to have an
open connection with a SQL database registered in my machine's ODBC
driver. Here's my code

Private Sub Connect()

Dim wrkODBC As Workspace
Dim conData As Connection

Set wrkODBC = CreateWorkspace("NewODBCWorkspace", "admin", "",
dbUseODBC)

Set conData = wrkODBC.OpenConnection("Connection1", , ,
"ODBC;DATABASE=Sale;UID=sa;PWD=sa;DSN=SQL_SERVER")

End Sub


When I run the code, an error msg says "Run-time Error:13 (Type
Mismatch)". I am almost convinced that it has something to do with
referencing to the right library. Does anyone know how to fix this?


Thanks!