VB & Database, Need Connection string for VB6 DAO DSN-LESS Connections to SQL Server 2000

Need Connection string for VB6 DAO DSN-LESS Connections to SQL Server 2000

Author	Message
Barry G. Sumpte #1 / 15	Need Connection string for VB6 DAO DSN-LESS Connections to SQL Server 2000 Hi all, Does anyone know if this is possible? Does anyone have a WORKING example for this? Thanks, Barry G. Sumpter
Sat, 13 Aug 2005 06:37:42 GMT

Bob Barrow #2 / 15	Need Connection string for VB6 DAO DSN-LESS Connections to SQL Server 2000 A great site for connection string examples is Carl Prothman's www.able-consulting.com/ado_conn.htm Concentrate on the OLEDB providers section. HTH, Bob Barrows Quote: > Hi all, > Does anyone know if this is possible? > Does anyone have a WORKING example for this? > Thanks, > Barry G. Sumpter
Sat, 13 Aug 2005 07:43:49 GMT

Barry G. Sumpte #3 / 15	Need Connection string for VB6 DAO DSN-LESS Connections to SQL Server 2000 Hi Bob, Thanks for the quick response. I've spent quite some time researching this problem and on Carls' site. Carls' site is for ADO not DAO. I've used considerable variations on all these connections strings to no avail as yet. Thanks, Barry G. Sumpter Quote: > A great site for connection string examples is Carl Prothman's > www.able-consulting.com/ado_conn.htm > Concentrate on the OLEDB providers section. > HTH, > Bob Barrows > > Hi all, > > Does anyone know if this is possible? > > Does anyone have a WORKING example for this? > > Thanks, > > Barry G. Sumpter
Sat, 13 Aug 2005 08:05:28 GMT

Bob Barrow #4 / 15	Need Connection string for VB6 DAO DSN-LESS Connections to SQL Server 2000 Oh! I though it was a typo! I have a simple answer: Don't use DAO to communicate with anything besides a Jet database. 1. IIRC, there is no such thing as a DAO DSNless connection to SQL Server. I'm pretty sure it had to use a DSN. 2. You are severely limiting yourself in both performance and features by using DAO instead of ADO. 3. Nobody uses DAO for this anymore, so if you want to do it, you are going to be on your own. The only resources will be the online help for DAO, and maybe some older books. Are you trying to avoid distributing the ADO library (MDAC))? Chances are, your users will already have it if they are running XP or Win2000. And if they aren't, it's really not that painful to distribute it along with your application. Bob Barrows Quote: > Hi Bob, > Thanks for the quick response. > I've spent quite some time researching this problem and on Carls' > site. > Carls' site is for ADO not DAO. > I've used considerable variations on all these connections strings to > no > avail as yet. > Thanks, > Barry G. Sumpter > > A great site for connection string examples is Carl Prothman's > > www.able-consulting.com/ado_conn.htm > > Concentrate on the OLEDB providers section. > > HTH, > > Bob Barrows > > > Hi all, > > > Does anyone know if this is possible? > > > Does anyone have a WORKING example for this? > > > Thanks, > > > Barry G. Sumpter
Sat, 13 Aug 2005 08:28:11 GMT

Barry G. Sumpte #5 / 15	Need Connection string for VB6 DAO DSN-LESS Connections to SQL Server 2000 My Responses In-Line: Quote: > Oh! I though it was a typo! > I have a simple answer: Don't use DAO to communicate with anything besides a > Jet database. Don't move to ADO because it the new technology. Have a look at the circumstances and make an informed decision. Quote: > 1. IIRC, there is no such thing as a DAO DSNless connection to SQL Server. > I'm pretty sure it had to use a DSN. Any links to verify this? Quote: > 2. You are severely limiting yourself in both performance and features by > using DAO instead of ADO. 'Severely' is overstated. With performance this simply isn't true. DAO is know to be faster than ADO. One web site stated 17% faster. As with all statistice, it depends on the circumstances. Quote: > 3. Nobody uses DAO for this anymore, so if you want to do it, you are going > to be on your own. The only resources will be the online help for DAO, and > maybe some older books. 'Nobody' is overstated. Again this simply isn't true. DAO is still a viable technology for those who know and understand it. There are too many web site resources refering to DAO for me to be on my own. Quote: > Are you trying to avoid distributing the ADO library (MDAC))? Chances are, > your users will already have it if they are running XP or Win2000. And if > they aren't, it's really not that painful to distribute it along with your > application. I want the fastest response. The least features. For a very simple database. With potentially a very large record count. For 300+ users to access. Quote: > Bob Barrows Thanks, Barry G. Sumpter Quote: > > Hi Bob, > > Thanks for the quick response. > > I've spent quite some time researching this problem and on Carls' > > site. > > Carls' site is for ADO not DAO. > > I've used considerable variations on all these connections strings to > > no > > avail as yet. > > Thanks, > > Barry G. Sumpter > > > A great site for connection string examples is Carl Prothman's > > > www.able-consulting.com/ado_conn.htm > > > Concentrate on the OLEDB providers section. > > > HTH, > > > Bob Barrows > > > > Hi all, > > > > Does anyone know if this is possible? > > > > Does anyone have a WORKING example for this? > > > > Thanks, > > > > Barry G. Sumpter
Sat, 13 Aug 2005 11:22:30 GMT

Barry G. Sumpte #6 / 15	Need Connection string for VB6 DAO DSN-LESS Connections to SQL Server 2000 Further reasons for my question. I'd rather NOT have to install a DSN on all 300+ user computers. Thanks, Barry G. Sumpter
Sat, 13 Aug 2005 11:36:36 GMT

Thomas Tonneso #7 / 15	Need Connection string for VB6 DAO DSN-LESS Connections to SQL Server 2000 Can you not use a FILE DSN and distribute this with teh application? Regards Thomas Quote: > Further reasons for my question. > I'd rather NOT have to install a DSN on all 300+ user computers. > Thanks, > Barry G. Sumpter
Sat, 13 Aug 2005 18:15:39 GMT

Bob Barrow #8 / 15	Need Connection string for VB6 DAO DSN-LESS Connections to SQL Server 2000 OK - sounds as if you're determined but, one more try: Quote: > My Responses In-Line: > > Oh! I though it was a typo! > > I have a simple answer: Don't use DAO to communicate with anything > > besides > a > > Jet database. > Don't move to ADO because it the new technology. > Have a look at the circumstances and make an informed decision. Sorry, but this is not an "informed" decision. ADO is NOT new technology any more. Your statement was true 5 (?) yrs ago when ADO 1.5 came out. At version 2.7, it is now a very robust technology that, frankly, runs circles around DAO in both performance and features when used with non-Jet databases. Quote: > > 1. IIRC, there is no such thing as a DAO DSNless connection to SQL > > Server. > > I'm pretty sure it had to use a DSN. > Any links to verify this? No, again, I said IIRC (if I recall correctly). I am not going to take time to try and look it up. Quote: > > 2. You are severely limiting yourself in both performance and features > > by > > using DAO instead of ADO. > 'Severely' is overstated. > With performance this simply isn't true. > DAO is know to be faster than ADO. Only with Jet databases. Unlike ADO, DAO can talk directly to the Jet database engine, and is "tuned" to optimize its interaction with that engine. That means that DAO can outperform (and can do more than) ADO when used with Jet databases. With non-Jet databases, DAO can only communicate through ODBC, which itself is obsolete technology which is no longer being supported by MS. DAO was never well-suited to the task of communicating via ODBC Quote: > One web site stated 17% faster. > As with all statistice, it depends on the circumstances. > > 3. Nobody uses DAO for this anymore, so if you want to do it, you are > going > > to be on your own. The only resources will be the online help for DAO, > > and > > maybe some older books. > 'Nobody' is overstated. > Again this simply isn't true. DAO is still a viable technology for > those who > know and understand it. :-) Yep, they're all chipping in to help you now, aren't they? Quote: > There are too many web site resources refering to DAO for me to be on > my > own. :-) And none of them contains the answer to this question? Has anyone else responded? You may have noticed that nobody in this newsgroup is very shy about jumping in with their opinions when they feel that someone has said something that they feel is clearly wrong. Have you seen anyone jump in and defend the use of DAO with SQL Server? <cue the sound of crickets chirping> Quote: > > Are you trying to avoid distributing the ADO library (MDAC))? Chances > > are, > > your users will already have it if they are running XP or Win2000. And > > if > > they aren't, it's really not that painful to distribute it along with > > your > > application. > I want the fastest response. The least features. > For a very simple database. With potentially a very large record > count. > For 300+ users to access. DAO is single-threaded. ADO is multi-threaded. This fact alone should rule out DAO as the technology to use with that many users. DAO is still a great technology to use to communicate with Jet databases. If I had to write a VB program today that used a Jet db for the backend, I would not hesitate to click off that DAO reference and use it. But SQL Server ... ? I'm going to stop now. Bob
Sat, 13 Aug 2005 20:14:27 GMT

Bob Barrow #9 / 15	Need Connection string for VB6 DAO DSN-LESS Connections to SQL Server 2000 Quote: > > For 300+ users to access. > DAO is single-threaded. ADO is multi-threaded. This fact alone should rule > out DAO as the technology to use with that many users. I've got to retract this argument. It's totally irrelevant with a VB client-side app. For a server-side app (asp), this is a true statement. Bob
Sat, 13 Aug 2005 21:04:54 GMT

Paul Clemen #10 / 15	Need Connection string for VB6 DAO DSN-LESS Connections to SQL Server 2000 Hi all, Does anyone know if this is possible? Does anyone have a WORKING example for this? I'm assuming that you're an ODBCDirect workspace? If so you may want to try the following connection string. I don't have SQL Server handy so I can't verify whether it will work: "PROVIDER=MSDASQL;" & _ "DRIVER={SQL Server};" & _ "SERVER=MySQLServer;DATABASE=pubs;" & _ "UID=sa;PWD=;" Microsoft MVP (Visual Basic)
Sat, 13 Aug 2005 23:04:49 GMT

Andr #11 / 15	Need Connection string for VB6 DAO DSN-LESS Connections to SQL Server 2000 Barry, try this Dim cnStr as String Dim m_DB as Database Dim RecSet as RecordSet dim sSQL as String cnStr = "ODBC;Driver=SQL Server;Server='MyServer';UID='sa';PWD=;Database='MYDB'" 'Open database Set m_DB = OpenDatabase("", False, False, cnstr) 'open snap shot recordset like this Set RecSet = m_DB.OpenRecordset(sSQL, dbOpenSnapshot, dbSeeChanges) 'or open Dynaset recordset Set RecSet = m_DB.OpenRecordset(sSQL, dbOpenDynamic, dbSeeChanges, dbPessimistic) -- andrew Quote: > Further reasons for my question. > I'd rather NOT have to install a DSN on all 300+ user computers. > Thanks, > Barry G. Sumpter
Sun, 14 Aug 2005 02:07:34 GMT

Bob Barrow #12 / 15	Need Connection string for VB6 DAO DSN-LESS Connections to SQL Server 2000 Quote: > Barry, try this > Dim cnStr as String > Dim m_DB as Database > Dim RecSet as RecordSet > dim sSQL as String > cnStr = "ODBC;Driver=SQL Server;Server='MyServer';UID='sa';PWD=;Database='MYDB'" I just tested this, and it works as long as you get rid of the single quotes within the string: cnStr = "ODBC;Driver=SQL Server;Server=MyServer;UID=sa;PWD=password;Database=MYDB" Bob Barrows
Sun, 14 Aug 2005 02:19:32 GMT

Barry G. Sumpte #13 / 15	Need Connection string for VB6 DAO DSN-LESS Connections to SQL Server 2000 Hi Thomas, Thanks for the response. Yes I've seen this option as well. But would prefer NOT to use it unless neccessary. All the best, Barry G. Sumpter Quote: > Can you not use a FILE DSN and distribute this with teh application? > Regards > Thomas > > Further reasons for my question. > > I'd rather NOT have to install a DSN on all 300+ user computers. > > Thanks, > > Barry G. Sumpter
Mon, 15 Aug 2005 11:11:07 GMT

Barry G. Sumpte #14 / 15	Need Connection string for VB6 DAO DSN-LESS Connections to SQL Server 2000 Wow! Thanks for the deatailed and passionate response, Bob. No need to discuss this further as you've convinced me to seriously consider ADO for SQL Server and confirmed that DAO should be used for Jet only. I Don't recall the date on the statistics but as I said the circumstance make the difference. Have a long healthy life, Barry G. Sumpter Quote: > OK - sounds as if you're determined but, one more try: > > My Responses In-Line: > > > Oh! I though it was a typo! > > > I have a simple answer: Don't use DAO to communicate with anything > > > besides > > a > > > Jet database. > > Don't move to ADO because it the new technology. > > Have a look at the circumstances and make an informed decision. > Sorry, but this is not an "informed" decision. > ADO is NOT new technology any more. Your statement was true 5 (?) yrs ago > when ADO 1.5 came out. At version 2.7, it is now a very robust technology > that, frankly, runs circles around DAO in both performance and features > when used with non-Jet databases. > > > 1. IIRC, there is no such thing as a DAO DSNless connection to SQL > > > Server. > > > I'm pretty sure it had to use a DSN. > > Any links to verify this? > No, again, I said IIRC (if I recall correctly). I am not going to take time > to try and look it up. > > > 2. You are severely limiting yourself in both performance and features > > > by > > > using DAO instead of ADO. > > 'Severely' is overstated. > > With performance this simply isn't true. > > DAO is know to be faster than ADO. > Only with Jet databases. Unlike ADO, DAO can talk directly to the Jet > database engine, and is "tuned" to optimize its interaction with that > engine. That means that DAO can outperform (and can do more than) ADO when > used with Jet databases. With non-Jet databases, DAO can only communicate > through ODBC, which itself is obsolete technology which is no longer being > supported by MS. DAO was never well-suited to the task of communicating via > ODBC > > One web site stated 17% faster. > > As with all statistice, it depends on the circumstances. > > > 3. Nobody uses DAO for this anymore, so if you want to do it, you are > > going > > > to be on your own. The only resources will be the online help for DAO, > > > and > > > maybe some older books. > > 'Nobody' is overstated. > > Again this simply isn't true. DAO is still a viable technology for > > those who > > know and understand it. > :-) Yep, they're all chipping in to help you now, aren't they? > > There are too many web site resources refering to DAO for me to be on > > my > > own. > :-) And none of them contains the answer to this question? > Has anyone else responded? You may have noticed that nobody in this > newsgroup is very shy about jumping in with their opinions when they feel > that someone has said something that they feel is clearly wrong. Have you > seen anyone jump in and defend the use of DAO with SQL Server? <cue the > sound of crickets chirping> > > > Are you trying to avoid distributing the ADO library (MDAC))? Chances > > > are, > > > your users will already have it if they are running XP or Win2000. And > > > if > > > they aren't, it's really not that painful to distribute it along with > > > your > > > application. > > I want the fastest response. The least features. > > For a very simple database. With potentially a very large record > > count. > > For 300+ users to access. > DAO is single-threaded. ADO is multi-threaded. This fact alone should rule > out DAO as the technology to use with that many users. > DAO is still a great technology to use to communicate with Jet databases. If > I had to write a VB program today that used a Jet db for the backend, I > would not hesitate to click off that DAO reference and use it. But SQL > Server ... ? > I'm going to stop now. > Bob
Mon, 15 Aug 2005 11:19:52 GMT

Barry G. Sumpte #15 / 15	Need Connection string for VB6 DAO DSN-LESS Connections to SQL Server 2000 Bob, Thomas, Andrew, and Paul. Thanks for all the responses I've just been a bit distracted porting my app to ADO/SQL(MSDE) from DAO/Access. I'll have a go at all your suggestions ASAP. Thanks for all the effort. Barry G. Sumpter Quote: > Hi all, > Does anyone know if this is possible? > Does anyone have a WORKING example for this? > Thanks, > Barry G. Sumpter
Mon, 15 Aug 2005 11:25:17 GMT

Page 1 of 2

[ 15 post ]