Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
ODBC connection failed. | Excel Discussion (Misc queries) | |||
ODBC connection failed. | Excel Discussion (Misc queries) | |||
ODBC connection failed. | Excel Discussion (Misc queries) | |||
Broken ODBC Connection | Links and Linking in Excel | |||
ODBC connection by udl | Excel Programming |