ODBC Connection
 

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_SERV ER")

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!

ODBC Connection
 

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]


Adrian T wrote:
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_SERV ER")

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!

ODBC Connection
 

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 *


"Adrian T" wrote in message
...
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_SERV ER")

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!

ODBC Connection
 

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" wrote in message
...
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_SERV ER")

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!

ODBC Connection
 

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").CopyFromR ecordset rsQuery
.Close
End With

Set rsQuery = Nothing
Set conData = Nothing

End Sub


"Jake Marx" wrote:

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]


Adrian T wrote:
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_SERV ER")

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!