0

I am experimenting with executing SQL Server stored procedures on a SQL Server back end from Access via DAO / ADO.

I can get it working quite well in DAO by using a pass-through query with a connection string to an ODBC data source When I try to use the same connection string on an ADO Connection object however, I just get the message:

Error -2147467259: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (Microsoft OLE DB Provider for ODBC Drivers)

The connection string that worked with DAO was a pointer to a DSN file: "ODBC;FILEDSN=RISCGen2.dsn".

Thinking that ADO might not be able to digest this, I tried replacing the string with the relevant content from the DSN file, namely:

“ODBC;DRIVER={ODBC Driver 11 for SQL Server};UID=my.loginid;PWD=mypassword;SERVER=WYNRISC08;Database=RISCGen2”.

However, I am still getting the same error.

Can someone spot where I am going wrong with this? Thanks in advance.

ADDENDUM

Dan Guzman has answered the question I posed. The problem has now become one of understanding certain messy details about ADO's way of doing things. I am thinking I will need to ask another question to deal with it...New question posted : Execute SQL stored procedure from VBA and retrieve all messages and result sets

Improve this question
1

1 Answer 1

Reset to default
0

ODBC;DRIVER={ODBC Driver 11 for SQL Server};UID=my.loginid;PWD=mypassword;SERVER=WYNRISC08;Database=RISCGen2

Omit the extraneous ODBC specification:

DRIVER={ODBC Driver 11 for SQL Server};UID=my.loginid;PWD=mypassword;SERVER=WYNRISC08;Database=RISCGen2

Note that classic ADO is natively OLE DB and uses the Microsoft OLE DB Provider for ODBC Drivers (MSDASQL) to use ODBC drivers. It would be best to use an OLE DB provider instead. You could use the legacy SQL Server OLE DB provider that ships with Windows (SQLOLEDB) but it would be best to use the latest MSOLEDBSQL driver as of this writing. The connection string for that is:

Provider=MSOLEDBSQL;UID=my.loginid;PWD=mypassword;SERVER=WYNRISC08;Database=RISCGen2

You might need to specify DataTypeCompatibility=80 if you use newer SQL data types because ADO classic is a mature API that hasn't been enhanced since SQL Server 2000 and is unaware the data types added since.

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

Well, getting rid of the leading "ODBC;" seems to allow the query to run through to completion - it ran for a little over a minute, which is what I expected. On what little things does happiness depend. However, it comes back with an error instead of the 2 rows that I got when trying via DAO. Note that the SP actually returns 3 result sets in the course of its execution and DAO just returns the first one. The error is unhelpful and just mentions the name of the first sub-SP that gets exec'ed by the main one. I will try the up to date driver you suggest - it may say something more helpful.
The link you gave for the latest MSOLEDBSQL driver just led me to the main MS downloads area. This link is more specific:(version 18)
@marktwo, I updated the link to the direct download. One thing to watch out for with ADO is to make sure SET NOCOUNT ON is specified, otherwise, the code will need to handle multiple result sets.
I have switched to the new driver and done some reading up. However, now the output messages from the SP are coming back not as lines in a recordset but as lines in the connection's Errors object, all with error number -2147467259. There are 127 lines, the last 3 of which are: Error -2147467259: DIED insert complete (Microsoft OLE DB Driver for SQL Server) Error -2147467259: Unknown token received from SQL Server (Microsoft OLE DB Driver for SQL Server) Error -2147467259: Query timeout expired (Microsoft OLE DB Driver for SQL Server)
Yet another twist... I updated the sp so it produced less than 127 lines by removing a large number of spacey-outy print statements. It is now running without producing that particular error - though I would still like to capture the messages somewhere, preferably not in the Errors collection.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.