Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Feb 2003
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts

    DAO - Connect String (VB ver6)

    Hi,

    What are the connect strings in VB - DAO for connecting to SQL Server? Thanks...

    Regards,
    88

  2. #2
    4 Star Lounger
    Join Date
    Aug 2002
    Location
    Dallas, Texas, USA
    Posts
    594
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DAO - Connect String (VB ver6)

    DAO or ADO? I thought DAO was Jet specific.

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: DAO - Connect String (VB ver6)

    DAO is Jet specific, but you can use it to create ODBC links to SQL Server tables and you can use it to manipulate the data in those tables. The queries, etc. run in Access/Jet (that is, at the client) rather than on the server, but they do run, albeit slowly at times. The older data controls in VB are DAO-based.
    Charlotte

  4. #4
    4 Star Lounger
    Join Date
    Aug 2002
    Location
    Dallas, Texas, USA
    Posts
    594
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DAO - Connect String (VB ver6)

    So are you using a 'temp' .mdb when using DAO in VB to communicate with an SQL Server?

    Isn't it just faster to use ADO then, since it is already using ODBC. It may not be faster, but it should be the same speed. DAO is faster with Jet, but if it is going through ODBC to use other data sources, it must be comparable in speed then.

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: DAO - Connect String (VB ver6)

    I'm not using it. I'm just saying that the regular DataControl in VB is a DAO control, not ADO.
    Charlotte

  6. #6
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: DAO - Connect String (VB ver6)

    > Isn't it just faster to use ADO then, since it is already using ODBC.

    Just a footnote that with ADO you might not be using ODBC at all. You can choose your database "provider" with ADO: either the "native" SQL Server OLE DB provider -or- the generic OLE DB provider for ODBC data sources coupled with the SQL Server ODBC driver. One would think the first method would be faster, but I haven't done any testing of that theory.

  7. #7
    4 Star Lounger
    Join Date
    Aug 2002
    Location
    Dallas, Texas, USA
    Posts
    594
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DAO - Connect String (VB ver6)

    You may be right, but then again, and this is just from my memory, which is not 100% lately <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>, I seem to remember that ADO still goes through the ODBC layer, no matter what provider you use. (For example, I use the Microsoft.Jet.OLEDB.4.0 driver when communicating with an Access database. However, from what I understand, that is still using ODBC....I could be completely wrong on that.....who knows....if anyone really wants to know, I suppose someone could look it up on MS's ADO dev site.)

  8. #8
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: DAO - Connect String (VB ver6)

    Well, I'm only reporting the official line. To use the generic OLE DB provider for ODBC, your connection string looks like this (the first line is optional/default):

    "Provider=MSDASQL;" & _
    "Driver={Microsoft Access Driver (*.mdb)};" & _
    "DBQ=XXXXX.mdb" & _
    "SystemDB=YYYYY.mdw;" & _
    "UID=ZZZZZ;PWD=;"

    To use the native OLE DB provider for Jet, on the other hand, your connection string looks like this:

    "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=XXXXX.mdb" & _
    "Jet OLEDB:System Database=YYYYY.mdw;" & _
    "User ID=ZZZZZ;Password="

    In reviewing an old thread on the WROX P2P site (involving me, so I could find it quickly), "Ken" posted this link: Reasons to use the native Jet OLEDB Provider.

    Oh hey, here's a Microsoft article that has a downloadable comparison showing how to set up all the various connection types: FILE: MDACCON.EXE Using Connection Strings with ODBC/OLEDB/ADO/RDS (I didn't try it).

    Hope this helps in some way... <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

Posting Permissions

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