DAO database connection to SQL Server 
Author Message
 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  
 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  
 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  
 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  
 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  
 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;")

- 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  
 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  
 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  
 
 [ 8 post ] 

 Relevant Pages 

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

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

3. disconnecting DAO-ODBC Connection to SQL Server

4. DAO Connection to SQL Server

5. Connection problems to SQL server database

6. Database connection with SQL server

7. Active X Document / SQL Server Database Connection

8. Connection to remote SQL Server database?

9. App opening 3 connections to SQL Server database

10. ADO connection to remote SQL-Server database refused

11. ADO - DSN less connection to SQL server Database

12. SQL Server connections not closing when Access connection also open

 

 
Powered by phpBB® Forum Software