SPONSORS:






User Tag List

Results 1 to 9 of 9
  1. #1
    Senior Member
    Join Date
    Jun 1999
    Location
    Fremont, CA, USA
    Posts
    355
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Can a DSN-Less connection to Excel be made?

    I am keeping all of my data files in Excel workbooks. However, having a DSN entry made for each one is a pain, especially when putting the code on different systems.

    What I would like to do is have a DSN-Less connection to the Excel workbook so that I don't have that problem.

    I have tried the following

    On the DB_Connect(sConnect) statement, an exception is generated at it fails. I am guessing that the parameter names I am using ("Driver" and "Dbq") may not be the ones that SilkTest wants.

    Does anyone know the proper syntax for creating a DSN-Less connection to an Excel workbook from 4Test?



    ------------------
    David Genrich
    david@e2Open.com
    david_kim@usa.net

  2. #2
    Senior Member
    Join Date
    Dec 1999
    Location
    Sarasota FL USA
    Posts
    109
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Re: Can a DSN-Less connection to Excel be made?

    You need the DSN to make the connection. What we had to do was use DSN files and not the DSN entry in the registry. This allowed us to create the DSN file in the script, or create a file and reference it from a common network directory.

    ------------------

  3. #3
    Senior Member
    Join Date
    Jun 1999
    Location
    Fremont, CA, USA
    Posts
    355
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Re: Can a DSN-Less connection to Excel be made?

    How do you create a DSN file then?

    ------------------
    David Genrich
    david@e2Open.com
    david_kim@usa.net

  4. #4
    Senior Member
    Join Date
    Jul 2000
    Posts
    117
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Re: Can a DSN-Less connection to Excel be made?

    I took a slightly different approach. I use the User DSN that you get "for free" when you install Excel.

    You'll see it on the User DSN page in the ODBC Administrator as "Excel Files".

    In my connection string, I use:

    hDB = DB_Connect ( "DSN=Excel Files;DBQ={FileName}" )

    Now, the "tricky" bit is that this is a User DSN and thus may not exist for all users on a system. Not a big deal, though, since we use the same user-id on all the QA Lab machines. Also really not a big deal as you can add the entry manually for any other users that need it.

    This way it's a fairly generic kind of thing rather than needing a ton of specific DSNs.



    ------------------

  5. #5
    Senior Member
    Join Date
    Dec 1999
    Location
    Sarasota FL USA
    Posts
    109
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Re: Can a DSN-Less connection to Excel be made?

    To create a file DSN..

    Go into the ODBC Mngr, select the FILE tab, and create a new DSN. Ypu will be prompted for the file name and path. Enter all the settings that you need in the ODBC mngr. Once finished (and tested) open the file you created in notepad. The information should be easy to read and edit if needed.

    ------------------

  6. #6
    Senior Member
    Join Date
    Jul 2000
    Posts
    117
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Re: Can a DSN-Less connection to Excel be made?

    ... oooohhhh, I like that! (creating a DSN on the fly) ...

    The problem that I mentioned (in using a User DSN) has been an occasional pain. By creating the DSN "as needed", you get code that is truly transportable ... assuming, of course, that the MS drivers are there, which they should be.

    Nice approach, bjc.

    ------------------

  7. #7
    bjc
    bjc is offline
    Member
    Join Date
    Jan 2000
    Location
    CT
    Posts
    66
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Re: Can a DSN-Less connection to Excel be made?

    another approach is to create a system DSN on the fly.

    include file:
    script file:
    ------------------

  8. #8
    Senior Member
    Join Date
    Oct 2000
    Location
    Laurel, Maryland, USA
    Posts
    204
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Re: Can a DSN-Less connection to Excel be made?

    My thanks as well. We have 24 different databases that we use, and it's really a pain setting these up for each one. On-The-Fly is great!

    ------------------
    Atlee
    Atlee

  9. #9
    bjc
    bjc is offline
    Member
    Join Date
    Jan 2000
    Location
    CT
    Posts
    66
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Re: Can a DSN-Less connection to Excel be made?

    here's a link to ODBC Programming Reference if you are interested.

    SQLConfigDataSource

    the fRequest argument (i use ODBC_ADD_SYS_DSN because it doesn't matter if a DSN w/ the same name exists or not. either way it'll be created) is really an integer (in this case 4). i found this out when i tried using this API call in Silk. in WinRunner, i could pass the fRequest arg as is (ie: ODBC_ADD_SYS_DSN), but Silk kept telling me that the variable was not set.

    anyway, i was just happy it worked. hope it helps you as much as it has helped me.

    Brian

    ------------------

 

 

Posting Permissions

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

vBulletin Optimisation provided by vB Optimise v2.6.0 Beta 4 (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
User Alert System provided by Advanced User Tagging v3.0.9 (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
Questions / Answers Form provided by vBAnswers (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
vBNominatevBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
Feedback Buttons provided by Advanced Post Thanks / Like (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
Username Changing provided by Username Change (Free) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
BetaSoft Inc.
Digital Point modules: Sphinx-based search
All times are GMT -8. The time now is 12:20 PM.

Copyright BetaSoft Inc.