VBA, DAO database connection to SQL Server

DAO database connection to SQL Server

Author	Message
david #1 / 8	DAO database connection to SQL Server I'm trying to do DAO.OpenDatabase to connect to SQL server (I don't actually want OpenConnection: I'm not using an ODBCdirect workspace. And it is an A97 application without an ADO reference). I am using DSN-less connections for linked tables, but apparently OpenDatabase requires a DSN? Am I missing something? (david)
Sun, 28 Nov 2004 18:06:44 GMT

Calvin Smit #2 / 8	DAO database connection to SQL Server With A97, OpenDatabase( ), and ODBC, [I think] one had to enter like the following example: Set SQLServerDB = OpenDatabase("", False, False, "ODBC;DSN=Production;DBQ=bonds;UID=CALVIN;PWD=HAWAII;QUERYTIMEOUT=0;") -- Calvin Smith http://www.CalvinSmithSoftware.com "Real-world Source Code Solutions" Mass Emailer - http://www.CalvinSmithSoftware.com/MassEmailer.htm VB/VBA Tools - http://www.CalvinSmithSoftware.com/AllComboDeals.htm Quote: > I'm trying to do DAO.OpenDatabase to connect to SQL server (I don't actually want OpenConnection: I'm not using an ODBCdirect Quote: > workspace. And it is an A97 application without an ADO reference). > I am using DSN-less connections for linked tables, but apparently OpenDatabase requires a DSN? Am I missing something? Quote: > (david)
Sun, 28 Nov 2004 18:56:29 GMT

david #3 / 8	DAO database connection to SQL Server Quote: > "ODBC;DSN=Production; So, apparently a DSN is required. Bummer. (david) Quote: > With A97, OpenDatabase( ), and ODBC, [I think] one had to enter like the > following example: > Set SQLServerDB = OpenDatabase("", False, False, > "ODBC;DSN=Production;DBQ=bonds;UID=CALVIN;PWD=HAWAII;QUERYTIMEOUT=0;") > -- > Calvin Smith > http://www.CalvinSmithSoftware.com > "Real-world Source Code Solutions" > Mass Emailer - http://www.CalvinSmithSoftware.com/MassEmailer.htm > VB/VBA Tools - http://www.CalvinSmithSoftware.com/AllComboDeals.htm > > I'm trying to do DAO.OpenDatabase to connect to SQL server (I don't > actually want OpenConnection: I'm not using an ODBCdirect > > workspace. And it is an A97 application without an ADO reference). > > I am using DSN-less connections for linked tables, but apparently > OpenDatabase requires a DSN? Am I missing something? > > (david)
Mon, 29 Nov 2004 08:10:49 GMT

michk #4 / 8	DAO database connection to SQL Server DSN is not required -- but the database type, name, et. al. are needed in order for the connection to work. -- MichKa Michael Kaplan Trigeminal Software, Inc. -- http://www.trigeminal.com/ International VB? -- http://www.i18nWithVB.com/ C++? MSLU -- http://msdn.microsoft.com/msdnmag/issues/01/10/ Quote: > > "ODBC;DSN=Production; > So, apparently a DSN is required. Bummer. > (david) Quote: > > With A97, OpenDatabase( ), and ODBC, [I think] one had to enter like the > > following example: > > Set SQLServerDB = OpenDatabase("", False, False, > > "ODBC;DSN=Production;DBQ=bonds;UID=CALVIN;PWD=HAWAII;QUERYTIMEOUT=0;") > > -- > > Calvin Smith > > http://www.CalvinSmithSoftware.com > > "Real-world Source Code Solutions" > > Mass Emailer - http://www.CalvinSmithSoftware.com/MassEmailer.htm > > VB/VBA Tools - http://www.CalvinSmithSoftware.com/AllComboDeals.htm > > > I'm trying to do DAO.OpenDatabase to connect to SQL server (I don't > > actually want OpenConnection: I'm not using an ODBCdirect > > > workspace. And it is an A97 application without an ADO reference). > > > I am using DSN-less connections for linked tables, but apparently > > OpenDatabase requires a DSN? Am I missing something? > > > (david)
Mon, 29 Nov 2004 08:43:21 GMT

david #5 / 8	DAO database connection to SQL Server Quote: > DSN is not required -- but the database type, name, et. al. are needed in I'm encouraged, and proceed: set db=dao.OpenDatabase(codedb.name,,,"ODBC;DRIVER={SQL SERVER};ADDRESS=SVR,5555;SERVER=SVR.555.NET;WSID=PC;DATABASE=DB;TRUSTED_CONNECTION=YES;UID=555;PWD=555;NETWORK=DBMSSOCN") That uses Codedb.Name as a dummy file. I think it works. With empty string for database name, it opens an ODBC dialog. With a string value, it looks for a file of that name (accepts JET database or DSN) With no string, it returns an error (not an optional parameter) Is this a sensible approach? Is there a better idea than using CodeDB.Name as a dummy file? Quote: > DSN is not required -- but the database type, name, et. al. are needed in > order for the connection to work. > -- > MichKa > Michael Kaplan > Trigeminal Software, Inc. -- http://www.trigeminal.com/ > International VB? -- http://www.i18nWithVB.com/ > C++? MSLU -- http://msdn.microsoft.com/msdnmag/issues/01/10/ > > > "ODBC;DSN=Production; > > So, apparently a DSN is required. Bummer. > > (david) > > > With A97, OpenDatabase( ), and ODBC, [I think] one had to enter like the > > > following example: > > > Set SQLServerDB = OpenDatabase("", False, False, > > > "ODBC;DSN=Production;DBQ=bonds;UID=CALVIN;PWD=HAWAII;QUERYTIMEOUT=0;") > > > -- > > > Calvin Smith > > > http://www.CalvinSmithSoftware.com > > > "Real-world Source Code Solutions" > > > Mass Emailer - http://www.CalvinSmithSoftware.com/MassEmailer.htm > > > VB/VBA Tools - http://www.CalvinSmithSoftware.com/AllComboDeals.htm > > > > I'm trying to do DAO.OpenDatabase to connect to SQL server (I don't > > > actually want OpenConnection: I'm not using an ODBCdirect > > > > workspace. And it is an A97 application without an ADO reference). > > > > I am using DSN-less connections for linked tables, but apparently > > > OpenDatabase requires a DSN? Am I missing something? > > > > (david)
Mon, 29 Nov 2004 09:53:49 GMT

michk #6 / 8	DAO database connection to SQL Server For DAO, you can never specify later optional params unless you include earlier parsms. Try filling values into those other two params in the middle, and get rid of that CodeDb.Name stuff (ZLS is what you want here). -- MichKa Michael Kaplan Trigeminal Software, Inc. -- http://www.trigeminal.com/ International VB? -- http://www.i18nWithVB.com/ C++? MSLU -- http://msdn.microsoft.com/msdnmag/issues/01/10/ Quote: > > DSN is not required -- but the database type, name, et. al. are needed in > I'm encouraged, and proceed: > set db=dao.OpenDatabase(codedb.name,,,"ODBC;DRIVER={SQL SERVER};ADDRESS=SVR,5555;SERVER=SVR.555.NET;WSID=PC;DATABASE=DB;TRUSTED_CONN ECTION=YES;UID=555;PWD=555;NETWORK=DBMSSOCN") Quote: > That uses Codedb.Name as a dummy file. I think it works. > With empty string for database name, it opens an ODBC dialog. > With a string value, it looks for a file of that name (accepts JET database or DSN) > With no string, it returns an error (not an optional parameter) > Is this a sensible approach? Is there a better idea than using CodeDB.Name > as a dummy file? Quote: > > DSN is not required -- but the database type, name, et. al. are needed in > > order for the connection to work. > > -- > > MichKa > > Michael Kaplan > > Trigeminal Software, Inc. -- http://www.trigeminal.com/ > > International VB? -- http://www.i18nWithVB.com/ > > C++? MSLU -- http://msdn.microsoft.com/msdnmag/issues/01/10/ > > > > "ODBC;DSN=Production; > > > So, apparently a DSN is required. Bummer. > > > (david) > > > > With A97, OpenDatabase( ), and ODBC, [I think] one had to enter like the > > > > following example: > > > > Set SQLServerDB = OpenDatabase("", False, False, "ODBC;DSN=Production;DBQ=bonds;UID=CALVIN;PWD=HAWAII;QUERYTIMEOUT=0;") - Hide quoted text - - Show quoted text - Quote: > > > > -- > > > > Calvin Smith > > > > http://www.CalvinSmithSoftware.com > > > > "Real-world Source Code Solutions" > > > > Mass Emailer - http://www.CalvinSmithSoftware.com/MassEmailer.htm > > > > VB/VBA Tools - http://www.CalvinSmithSoftware.com/AllComboDeals.htm message > > > > > I'm trying to do DAO.OpenDatabase to connect to SQL server (I don't > > > > actually want OpenConnection: I'm not using an ODBCdirect > > > > > workspace. And it is an A97 application without an ADO reference). > > > > > I am using DSN-less connections for linked tables, but apparently > > > > OpenDatabase requires a DSN? Am I missing something? > > > > > (david)
Mon, 29 Nov 2004 11:21:06 GMT

Calvin Smit #7 / 8	DAO database connection to SQL Server Sorry, my example was from an old Access-to-Sybase module, not SQL Server. That's probably going to be a very slow connection for you, but I'm sure you have your valid reasons for going that route. Good luck! :-) -- Calvin Smith http://www.CalvinSmithSoftware.com "Real-world Source Code Solutions" Mass Emailer - http://www.CalvinSmithSoftware.com/MassEmailer.htm VB/VBA Tools - http://www.CalvinSmithSoftware.com/AllComboDeals.htm Quote: > > "ODBC;DSN=Production; > So, apparently a DSN is required. Bummer. > (david) Quote: > > With A97, OpenDatabase( ), and ODBC, [I think] one had to enter like the > > following example: > > Set SQLServerDB = OpenDatabase("", False, False, > > "ODBC;DSN=Production;DBQ=bonds;UID=CALVIN;PWD=HAWAII;QUERYTIMEOUT=0;") > > -- > > Calvin Smith > > http://www.CalvinSmithSoftware.com > > "Real-world Source Code Solutions" > > Mass Emailer - http://www.CalvinSmithSoftware.com/MassEmailer.htm > > VB/VBA Tools - http://www.CalvinSmithSoftware.com/AllComboDeals.htm > > > I'm trying to do DAO.OpenDatabase to connect to SQL server (I don't > > actually want OpenConnection: I'm not using an ODBCdirect > > > workspace. And it is an A97 application without an ADO reference). > > > I am using DSN-less connections for linked tables, but apparently > > OpenDatabase requires a DSN? Am I missing something? > > > (david)
Tue, 30 Nov 2004 09:35:13 GMT

david #8 / 8	DAO database connection to SQL Server Thank you now using set DB=DAO.OpenDatabase("",False,False,sConnect) (david) Quote: > For DAO, you can never specify later optional params unless you include > earlier parsms. Try filling values into those other two params in the > middle, and get rid of that CodeDb.Name stuff (ZLS is what you want here). > -- > MichKa > Michael Kaplan > Trigeminal Software, Inc. -- http://www.trigeminal.com/ > International VB? -- http://www.i18nWithVB.com/ > C++? MSLU -- http://msdn.microsoft.com/msdnmag/issues/01/10/ > > > DSN is not required -- but the database type, name, et. al. are needed > in > > I'm encouraged, and proceed: > > set db=dao.OpenDatabase(codedb.name,,,"ODBC;DRIVER={SQL > SERVER};ADDRESS=SVR,5555;SERVER=SVR.555.NET;WSID=PC;DATABASE=DB;TRUSTED_CONN > ECTION=YES;UID=555;PWD=555;NETWORK=DBMSSOCN") > > That uses Codedb.Name as a dummy file. I think it works. > > With empty string for database name, it opens an ODBC dialog. > > With a string value, it looks for a file of that name (accepts JET > database or DSN) > > With no string, it returns an error (not an optional parameter) > > Is this a sensible approach? Is there a better idea than using CodeDB.Name > > as a dummy file? > > > DSN is not required -- but the database type, name, et. al. are needed > in > > > order for the connection to work. > > > -- > > > MichKa > > > Michael Kaplan > > > Trigeminal Software, Inc. -- http://www.trigeminal.com/ > > > International VB? -- http://www.i18nWithVB.com/ > > > C++? MSLU -- http://msdn.microsoft.com/msdnmag/issues/01/10/ > > > > > "ODBC;DSN=Production; > > > > So, apparently a DSN is required. Bummer. > > > > (david) > > > > > With A97, OpenDatabase( ), and ODBC, [I think] one had to enter like > the > > > > > following example: > > > > > Set SQLServerDB = OpenDatabase("", False, False, > "ODBC;DSN=Production;DBQ=bonds;UID=CALVIN;PWD=HAWAII;QUERYTIMEOUT=0;") > > > > > -- > > > > > Calvin Smith > > > > > http://www.CalvinSmithSoftware.com > > > > > "Real-world Source Code Solutions" > > > > > Mass Emailer - http://www.CalvinSmithSoftware.com/MassEmailer.htm > > > > > VB/VBA Tools - http://www.CalvinSmithSoftware.com/AllComboDeals.htm > message > > > > > > I'm trying to do DAO.OpenDatabase to connect to SQL server (I > don't > > > > > actually want OpenConnection: I'm not using an ODBCdirect > > > > > > workspace. And it is an A97 application without an ADO reference). > > > > > > I am using DSN-less connections for linked tables, but apparently > > > > > OpenDatabase requires a DSN? Am I missing something? > > > > > > (david)
Tue, 30 Nov 2004 11:26:21 GMT

Page 1 of 1

[ 8 post ]