Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post

    ADO vs ODBC (Excel 2003)

    I have an Excel workbook where I need to access data from a SQL Server database. Up until recently I used ODBC, but then I read that ADO is the preferred data access technology. I therefore created another version of the workbook that uses an ADO connection.

    It works fine usually, and the queries take about the same amount of time to run as with the ODBC connection. However, at other times that's not the case. Today I initiated identical queries at the same time, one using an ADO connection, the other ODBC. Actually the ADO query started first by about a second. The ODBC query finished in just under one and a half minutes; the ADO query was still running after five and a half minutes - when I cancelled it.

    I'm wondering if anybody has any ideas as to what's going on? There are other processes running on the SQL Server, so there will be times when a query will be getting less CPU cycles. All I can think is that the ODBC initiated query is somehow assigned a higher priority than an ADO query. Other relevant info (maybe): the tables that the queries are going against are all specified as (NoLock); the ODBC connections are Named Pipes rather than TCP/IP.

    Any suggestions/theories/lines of attack gratefully received...

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: ADO vs ODBC (Excel 2003)

    I'm not an expert on the technical aspects of connecting to SQL Server, but you should keep in mind that ADO and ODBC are different things:

    ADO is a high-level interface that lets programmers work with all kinds of data sources in a standardized way; the code looks the same, whether you are working with a text file, an Excel worksheet or an Oracle database, as long as you can create a connection to the data source. ADO talks with a database through a low-level interface such as ODBC or OLE DB.

    DAO is another high-level interface; it is more or less the predecessor of ADO. It is less general. Performance is better than ADO with Access, for which it was originally designed, but may be worse than ADO in other situations.

    If ODBC works well for you, I'd stay with it.

  3. #3
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: ADO vs ODBC (Excel 2003)

    Thanks, Hans. I may well stick with ODBC. One of the side benefits of ADO was that the connection parameters can be embedded in the Excel workbook, so the users don't have to have ODBC data sources defined. I have > 200 users and whenever any of them gets a new computer, I get a call because they can't access the SQL Server without the ODBC data source being set up. The other benefit is that with ADO, I can trap the time-outs and take appropriate action - e.g., give them a Continue/Cancel option every third time-out, say. I don't think that's possible with ODBC - it seems to just wait forever and the user has to cancel using Task Manager.

    I kind of knew that ADO and ODBC are not comparable, because I knew that you can use ADO and stil be connecting using ODBC. It's still very fuzzy, though!

    At the end of the day, what I need to do is construct an SQL query in Excel VBA, have it to run on a SQL Server database, and return the results to one of the worksheets. (The queries are based on the user's choices, so a stored procedure wouldn't work).
    At this point the only two ways I know are:
    (i) Have each user create an ODBC data source and retrieve the data using a QueryTable; or
    (ii) Use ADO (with the SQLOLEDB Provider) to populate a Recordset, and then use the .CopyFromRecordset function to transfer the data to the spreadsheet.

    I'm wondering if those are my only two choices, or is there a better option? I read somewhere that data can be transferred by way of user-defined types, but I don't know how that would be accomplished.
    Thanks.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: ADO vs ODBC (Excel 2003)

    Were you using DAO before?

  5. #5
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: ADO vs ODBC (Excel 2003)

    Not DAO, no. At least I don't think I was (?!). What I had was something like the following:
    <pre> With ActiveSheet.QueryTables.Add( _
    Connection:="ODBC;DSN=<dsn>;UID=<userID>;PWD=<pass word>", _
    Destination:=ActiveCell)
    .CommandText = sSql
    .Name = "tmpQ"
    .FieldNames = False
    .AdjustColumnWidth = False
    .PreserveFormatting = True
    .Refresh BackgroundQuery:=False
    End With</pre>

    - where sSql is a string variable containing the SQL Select statement to be run.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: ADO vs ODBC (Excel 2003)

    You should be able to specify the server and database in the connection string (a so-called DSN-less connection). That way, the uset doesn't have to create a DSN.
    ...
    Connection:="ODBC;Driver={SQL Server};Server=<myserver>;UID=<userid>;PWD=<passwo rd>;Database=<mydatabse>", _
    ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •