DAO - Connect String (VB ver6)

[chl88]

Hi,

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

Regards,
88

[Drew]

DAO or ADO? I thought DAO was Jet specific.

[charlotte]

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.

[Drew]

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.

[charlotte]

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

[jscher2000]

> 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.

[Drew]

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.)

[jscher2000]

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>