Hello all,
I'm using an Access 97 front-end with a SQL Server 2000 back-end. One
particular bit of code runs a stored procedure to obtain a rowcount
from one of the back-end tables. This works fine on one PC, but not on
another. For the life of me, I can't see why it fails to work in the
'faulty' environment.
This is the code:-
Dim conImportCM As Connection
Dim wrkODBC As Workspace
Dim strConnection As String
Dim qdf As QueryDef
strConnection = "ODBC;DSN=Impor tCM;Database=Im portCM; UID=xxxx;
PWD=yyyy"
Set wrkODBC = CreateWorkspace ("", "", "", dbUseODBC)
Set conImportCM = wrkODBC.OpenCon nection("", dbDriverNoPromp t, ,
strConnection)
Set qdf = conImportCM.Cre ateQueryDef("", "{ ? = call
prcCountViewCon tract}")
qdf.Parameters( 0).Direction = dbParamReturnVa lue
qdf.Execute
MsgBox "Row count is " & qdf.Parameters( 0).Value
The stored procedure that's being executed is:-
CREATE PROCEDURE prcCountViewCon tract
@Rows int = NULL OUTPUT
AS
SELECT ContractID FROM dbo.tblViewCont ract
RETURN @@ROWCOUNT
GO
This works fine on one PC running Windows 2000 and returns the expected
rowcount in the message box. On a another PC running Windows 98 SE, it
fails to return any data - the qdf.Parameters( 0).Value returns null.
Initially, I thought it might be a permissions issue (because this is
running under a different user account on the two machines), but I can
fire-up MS Query on the Windows 98 machine and manually run the stored
procedure with 'exec prcCountViewCon tract' OK, so it looks like the
Windows 98 environment has the correct access to the SQL Server
back-end.
I've also updated MDAC on the Windows 98 machine so that it's the same
version as that used on the Windows 2000 PC (2.7). That has made
absolutely no difference to the symptoms.
Any ideas?
Thanks,
Mike
I'm using an Access 97 front-end with a SQL Server 2000 back-end. One
particular bit of code runs a stored procedure to obtain a rowcount
from one of the back-end tables. This works fine on one PC, but not on
another. For the life of me, I can't see why it fails to work in the
'faulty' environment.
This is the code:-
Dim conImportCM As Connection
Dim wrkODBC As Workspace
Dim strConnection As String
Dim qdf As QueryDef
strConnection = "ODBC;DSN=Impor tCM;Database=Im portCM; UID=xxxx;
PWD=yyyy"
Set wrkODBC = CreateWorkspace ("", "", "", dbUseODBC)
Set conImportCM = wrkODBC.OpenCon nection("", dbDriverNoPromp t, ,
strConnection)
Set qdf = conImportCM.Cre ateQueryDef("", "{ ? = call
prcCountViewCon tract}")
qdf.Parameters( 0).Direction = dbParamReturnVa lue
qdf.Execute
MsgBox "Row count is " & qdf.Parameters( 0).Value
The stored procedure that's being executed is:-
CREATE PROCEDURE prcCountViewCon tract
@Rows int = NULL OUTPUT
AS
SELECT ContractID FROM dbo.tblViewCont ract
RETURN @@ROWCOUNT
GO
This works fine on one PC running Windows 2000 and returns the expected
rowcount in the message box. On a another PC running Windows 98 SE, it
fails to return any data - the qdf.Parameters( 0).Value returns null.
Initially, I thought it might be a permissions issue (because this is
running under a different user account on the two machines), but I can
fire-up MS Query on the Windows 98 machine and manually run the stored
procedure with 'exec prcCountViewCon tract' OK, so it looks like the
Windows 98 environment has the correct access to the SQL Server
back-end.
I've also updated MDAC on the Windows 98 machine so that it's the same
version as that used on the Windows 2000 PC (2.7). That has made
absolutely no difference to the symptoms.
Any ideas?
Thanks,
Mike
Comment