SQL Connection String / Open Recordset via ADO

[Bob Bonta]

I was using vbscript in a DTS package on SQL2000 to run some code that had
been working successfully for over a year.

We have recently installed a new SQL2005 server and my taks is to move
everything over from the SQL2000 server to the SQL2005 database.

Before I can even move this into an SSIS on SQL2005, I'm trying to test the
vbscript within MS Access to ensure connection strings and recordsets are
built properly and run successfully.

Below is is my connection string, which seems to be successful:
strConn = "ODBC;Provider=sqloledb;Data Source=servername;Initial
Catalog=dbname;UID=username;PWD=password;"
objConn.Open strConn

Below is the instantiation of the recordset object relying on the success of
the connection object:
Set objRecordset = CreateObject("ADODB.Recordset")
objRecordset.Open strQuery, objConn, adOpenStatic, adLockBatchOptimistic


As I step through the code in MS Access, with a watch on both the connection
object and the recordset object, the evidence suggests that the connection
object is successful but the recordset object is not.

I am getting the following on the recordset object:
strProject = objRecordset("ProjID")
<Item cannot be found in the collection corresponding to the requested name
or ordinal.>

In the watch pane, the value of the objRecordset.EOF property = <Operation
is not allowed when the object is closed>

This is suggesting to me that, although the connection object is open, the
recordset object is never opened.

I am at an impass .... help?!?

 

[Sylvain Lafontaine]

Are you sure that the connection object is open? You should check its
state; see:
http://msdn2.microsoft.com/en-us/library/ms807027.aspx

As to why your connection object remains closed, you should start by taking
a closer look at your connection string and start removing a things such as
« ODBC; » that is not part of an ADO connection string and replace things
like UID and PWD with their proper names for the SQL OLEDB provider; see:

http://www.connectionstrings.com/
http://www.carlprothman.net/Default.aspx?tabid=81

 

[Bob Bonta]

Incredible!

In most instances, there are numerous ways in which to skin a cat ... so to
speak.

However, to connect to a SQL Server database, one must be absolutely
specific and I detect there is but one way --- and I have yet to find it!

I have tried just about every method suggested from the link you provided.
I am trying to run a simple query to create a recordset through which I can
traverse top to bottom and perform some simple tasks with each record in the
RS.

If I use the same connection string (changing the servername and database
name or initial catalog name) on a SQL2000 server, I have absolutely no
problem at all. It connects, successfully builds a RS and I navigate through
it from beginning to end. But if I point the same code to a SQL2005 server
(again, changing the servername and database name or initial catalog name),
the RS never opens ... it continues to reflect as closed ("Operation is not
allowed when the object is closed").

The links you provided present such a plethora of options that one actually
gets a bit confused as to which would be the one needed. Based on the
particulars presented, is there a specific connection string that would work
to successfully connect and build/open a RS (Btw, I removed the ODBC in the
connection string as suggested)?

Completely baffled in VA!!!

Bob

 

[Bob Bonta]

In the Watch window for the connection object (objConn), the default database
property reflects: <Operation is not allowed when the object is closed> ...
the Initial Catalog property reflects the value I had set it to in the
connection string.

As it stands, this is my connection string:
"Driver={SQL Native Client};Data Source=myservername;Initial
Catalog=mydbname;Database=mydbname;User Id=xxxx;Password=xxxx;"

What am I doing wrong?

TIA ... Bob

 

[Sylvain Lafontaine]

First, « Driver={SQL Native Client}; » is for ODBC, so you must use ODBC
parameters such as UID and PWD. You must not confuse between ODBC; ODBC,
OLEDB and in the case of SQL-2005, Native Provider for ODBC and Native
Provider for OLEDB.

Notice than ODBC; and ODBC are not the same thing: ODBC; is used for
creating an ODBC linked table in Access under the normal JET (ODBC)
workspace instead of the ODBCDirect workspace while ODBC indicate the use of
an ODBC provider such as the {SQL Native Client}.

You say that you trying to access a SQL-Server 2005 server. Maybe you have
a problem with either the protocol used (name pipes, TCP/IP or shared
memory), a firewall (check the default port 1433 for static connection or
above for dynamic connections), a named instance or this SQL-Server 2005
server is not configured to accept external connection for the protocol
used.

Before trying to connect using DAO or ADO, you should try to connect using
either Enterprise Manager (EM), SSMA or SSMAE or VS2003/2005. A good ideal
would be to consult with the person who has setup this server to get the
proper connection parameters if this person is not you. Possibly that only
him/her can tell you what you are doing wrong while trying to connect to
this remote server.

Here are some references for troubleshooting connection problems:

http://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx
http://blogs.msdn.com/sqlexpress/archive/2004/07/23/192044.aspx
http://msdn2.microsoft.com/en-us/library/ms345318.aspx
http://www.datamasker.com/SSE2005_NetworkCfg.htm (connection strings for
sql providers)