Running SQL 7.0, Win 2000, Excel 2002 and MDAC 2.5 SP 2 or later.

ODBC Call VBA:

dbGMS.QueryTimeout = 0

Set rst = dbGMS.OpenRecordset(mstrSQL, dbOpenSnapshot,
dbSQLPassThrough)

Works on dev p.c.s and one non-key user p.c.

Key user p.c.'s get ODBC call Failed: 3146.

Have verified is not a SQL 7.0 rights issue.

MS Error help suggests it is related to "Select NoCount On" command.
Since this is a recordset call, a Select NoCount On is required at some
point.

VBA is identical to another Excel 97/2000 wb that works with a "Select
NoCount On" right at the beginning of the SQL string. I used this file
as a template for this new report.

Any suggestions?



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

Hi Jay_44,

Since this is a generic error, have you stepped through the Errors
collection of the DBEngine?

Dim MyError As Error
For Each MyError In DBEngine.Errors
With MyError
MsgBox .Number & " " & .Description
End With
Next MyError

This may give you more insight into what's causing the error. If that
doesn't work, is there any possibility of using ADO (OLEDB) instead of DAO?

One thing I noticed that may be a typo - shouldn't it be "SET NOCOUNT ON"
instead of "SELECT NOCOUNT ON"?

--
Regards,

Jake Marx
www.longhead.com


Jay_44 wrote:
Running SQL 7.0, Win 2000, Excel 2002 and MDAC 2.5 SP 2 or later.

ODBC Call VBA:

dbGMS.QueryTimeout = 0

Set rst = dbGMS.OpenRecordset(mstrSQL, dbOpenSnapshot,
dbSQLPassThrough)

Works on dev p.c.s and one non-key user p.c.

Key user p.c.'s get ODBC call Failed: 3146.

Have verified is not a SQL 7.0 rights issue.

MS Error help suggests it is related to "Select NoCount On" command.
Since this is a recordset call, a Select NoCount On is required at
some point.

VBA is identical to another Excel 97/2000 wb that works with a "Select
NoCount On" right at the beginning of the SQL string. I used this
file as a template for this new report.

Any suggestions?



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from
http://www.ExcelForum.com/

I meant and do have in code "Set NoCount On".

The Error occurs right at the

Set rst = dbGMS.OpenRecordset(mstrSQL, dbOpenSnapshot,
dbSQLPassThrough) line.

If I add the error code you sent right before that, will it help any in
capturing?

Also, what would be the ADO command string - that may be easiest to
try, since I will have to have a user walk through the error code - not
a great way to troubleshoot;-)



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

Hi Jay_44,

You can use an On Error Goto statement, then in the error handling routine,
loop through the DBEngine.Errors collection. That should work, but it's
been a long time since I've used DAO.

It's hard to say what the connection string would be for ADO since I don't
know the name/location/type of database you're connecting to. If it's
Access (Jet-based DB), then you may be better off staying with DAO. I would
try posting a question to one of the Access groups if you are working with
Access and passthrough queries. If, OTOH, you're connecting directly to SQL
Server 7, then you can/should use ADO. A search of the string "ADO" on the
MSDN library should give you a starting point.

--
Regards,

Jake Marx
www.longhead.com


Jay_44 wrote:
I meant and do have in code "Set NoCount On".

The Error occurs right at the

Set rst = dbGMS.OpenRecordset(mstrSQL, dbOpenSnapshot,
dbSQLPassThrough) line.

If I add the error code you sent right before that, will it help any
in capturing?

Also, what would be the ADO command string - that may be easiest to
try, since I will have to have a user walk through the error code -
not a great way to troubleshoot;-)



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from
http://www.ExcelForum.com/