Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    ADO Provider string for Excel db (2000)

    I use the following string to identify MS Access as the database provider in a procedure using ADO
    <pre>sProv = "Provider=Microsoft.Jet.OLEDB.4.0;" '(for MS Access)</pre>



    Somewhere I have seen a list of strings for other providers, but can't find them at the moment.
    The string I am interested in currently is the one used when I query another Excel file rather than Access.

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

    Re: ADO Provider string for Excel db (2000)

    A good way to do this is to create a text file and give it an extension .udl, say Test.udl. Open the file, and set a connection string. When done, you can view the .udl file in Notepad to see what it looks like:

    Provider=MSDASQL.1;Extended Properties="Driver={Microsoft Excel Driver (*.xls)};DriverId=790;DBQ=C:ExcelMyFile.xls;"

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

    Re: ADO Provider string for Excel db (2000)

    See if Jan Karel Pieterse's Query Manager add-in (free from JKP-ADS Download page) helps.

  4. #4
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ADO Provider string for Excel db (2000)

    Hans

    [edited by Paul --- I think I have solved the 2nd question below, so disregard it. Thanks]

    After posting my question I ran into another bit of code that I tried and found it works:
    <pre>sCon = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & sFilename & ";" & _
    "Extended Properties=Excel 8.0;"</pre>



    Hence, the provider string seems to be the same for Access and Excel. Apparently the Data Source string
    is what I needed to change to the above. Now we have a couple of methods.

    Now, maybe I should start a new thread for this but the above leads me to a related topic:

    Originally, I started this process by using Data>Get External Data
    However, with this, the Data source was hardcoded. When I moved my directory, the Get External Data query
    was still looking for the source in the original location. I was unable to find a way to code the source. Hence,
    I turned to ADO. It is now working well.
    BUT, on opening the file, it is still attempting to run the GetExt Data query, since I had set the property to run
    on opening.
    I have removed the sheet where the query posted. Plus, I opened the Query folder and deleted the query that I
    had saved.
    Yet, I am still getting an error msg on opening, I assume because somehow it is attempting to run the query
    with no sheet to post the data to. Since the error msg has no Debug (only "End") I can't trace it down.

    Is there something else I need to do to delete this old query?


    Thanks

Posting Permissions

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