Why doesn't this code work???

[mheden]

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=ImportCM;Database=ImportCM; UID=xxxx;
PWD=yyyy"
Set wrkODBC = CreateWorkspace("", "", "", dbUseODBC)
Set conImportCM = wrkODBC.OpenConnection("", dbDriverNoPrompt, ,
strConnection)
Set qdf = conImportCM.CreateQueryDef("", "{ ? = call
prcCountViewContract}")

qdf.Parameters(0).Direction = dbParamReturnValue
qdf.Execute

MsgBox "Row count is " & qdf.Parameters(0).Value

The stored procedure that's being executed is:-

CREATE PROCEDURE prcCountViewContract
@Rows int = NULL OUTPUT
AS
SELECT ContractID FROM dbo.tblViewContract
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 prcCountViewContract' 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

[Terry Kreft]

Did you installl DCOM98 on the Win98 machine?

This should have been done before you installed MDAC.

--
Terry Kreft
MVP Microsoft Access
<mh****@bigfoot.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...

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=ImportCM;Database=ImportCM; UID=xxxx;
PWD=yyyy"
Set wrkODBC = CreateWorkspace("", "", "", dbUseODBC)
Set conImportCM = wrkODBC.OpenConnection("", dbDriverNoPrompt, ,
strConnection)
Set qdf = conImportCM.CreateQueryDef("", "{ ? = call
prcCountViewContract}")

qdf.Parameters(0).Direction = dbParamReturnValue
qdf.Execute

MsgBox "Row count is " & qdf.Parameters(0).Value

The stored procedure that's being executed is:-

CREATE PROCEDURE prcCountViewContract
@Rows int = NULL OUTPUT
AS
SELECT ContractID FROM dbo.tblViewContract
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 prcCountViewContract' 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

[MikeH]

Terry,

No, I just installed MDAC 2.7 - I didn't see anything in the
documentation about installing DCOM first, and the MDAC installation
didn't complain at all. I've since checked the version of DCOM on the
Win 98SE machine - it is 'DCOM Win98 Gold 4.71.0.1719'.

Should I install DCOM98 1.3, or am I clutching at straws here?

Regards,

Mike

Terry Kreft wrote:

Did you installl DCOM98 on the Win98 machine?

This should have been done before you installed MDAC.

--
Terry Kreft
MVP Microsoft Access
<mh****@bigfoot.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...

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=ImportCM;Database=ImportCM; UID=xxxx; PWD=yyyy"
Set wrkODBC = CreateWorkspace("", "", "", dbUseODBC)
Set conImportCM = wrkODBC.OpenConnection("", dbDriverNoPrompt, , strConnection)
Set qdf = conImportCM.CreateQueryDef("", "{ ? = call
prcCountViewContract}")

qdf.Parameters(0).Direction = dbParamReturnValue
qdf.Execute

MsgBox "Row count is " & qdf.Parameters(0).Value

The stored procedure that's being executed is:-

CREATE PROCEDURE prcCountViewContract
@Rows int = NULL OUTPUT
AS
SELECT ContractID FROM dbo.tblViewContract
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 prcCountViewContract' 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

[Terry Kreft]

IIRC, Wni98se has DCOM pre-installed in which case you shouldn't need to
install it again, support articles on DCOM are at (watch for wrapping).

http://support.microsoft.com/search/...&srchExtraQry=
DCOM has to be installed on a machine for ADO to work, having to install
DCOM as a separate step is only an issue with Win95 and Win98 machines.
--
Terry Kreft
MVP Microsoft Access
"MikeH" <mh****@bigfoot.com> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...

Terry,

No, I just installed MDAC 2.7 - I didn't see anything in the
documentation about installing DCOM first, and the MDAC installation
didn't complain at all. I've since checked the version of DCOM on the
Win 98SE machine - it is 'DCOM Win98 Gold 4.71.0.1719'.

Should I install DCOM98 1.3, or am I clutching at straws here?

Regards,

Mike

Terry Kreft wrote:

Did you installl DCOM98 on the Win98 machine?

This should have been done before you installed MDAC.

--
Terry Kreft
MVP Microsoft Access
<mh****@bigfoot.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...

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=ImportCM;Database=ImportCM; UID=xxxx; PWD=yyyy"
Set wrkODBC = CreateWorkspace("", "", "", dbUseODBC)
Set conImportCM = wrkODBC.OpenConnection("", dbDriverNoPrompt, , strConnection)
Set qdf = conImportCM.CreateQueryDef("", "{ ? = call
prcCountViewContract}")

qdf.Parameters(0).Direction = dbParamReturnValue
qdf.Execute

MsgBox "Row count is " & qdf.Parameters(0).Value

The stored procedure that's being executed is:-

CREATE PROCEDURE prcCountViewContract
@Rows int = NULL OUTPUT
AS
SELECT ContractID FROM dbo.tblViewContract
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 prcCountViewContract' 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

[MikeH]

Definitely clutching at straws now...

The version of DCOM98 on the Windows 98SE PC wasn't the 'latest and
greatest' so I installed 1.3. It didn't make any difference at all.
The qdf.Parameters(0).Value still returns null instead of the expected
row count. Identical code runs fine on my Windows 2000 PC.

Any more ideas anyone? ...or is this one of those cases where the
quickest solution is going to be a re-write to get the row count in a
different way?

Thanks,

Mike

[Terry Kreft]

Mike,
Looking at your original post again, I would have written the SP as

CREATE PROCEDURE prcCountViewContract
@Rows int = NULL OUTPUT
AS
SELECT @Rows = COUNT(*) FROM dbo.tblViewContract

--
Terry Kreft
MVP Microsoft Access
"MikeH" <mh****@bigfoot.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...

Definitely clutching at straws now...

The version of DCOM98 on the Windows 98SE PC wasn't the 'latest and
greatest' so I installed 1.3. It didn't make any difference at all.
The qdf.Parameters(0).Value still returns null instead of the expected
row count. Identical code runs fine on my Windows 2000 PC.

Any more ideas anyone? ...or is this one of those cases where the
quickest solution is going to be a re-write to get the row count in a
different way?

Thanks,

Mike

[MikeH]

Terry,

I changed the stored procedure to:-

CREATE PROCEDURE prcCountViewContract
@Rows int = NULL OUTPUT
AS
SELECT @Rows = COUNT (*) FROM dbo.tblViewContract
RETURN @Rows
GO

.... and it works fine now. Thanks very much for your help!

Regards,

Mike

Terry Kreft wrote:

Mike,
Looking at your original post again, I would have written the SP as

CREATE PROCEDURE prcCountViewContract
@Rows int = NULL OUTPUT
AS
SELECT @Rows = COUNT(*) FROM dbo.tblViewContract

--
Terry Kreft
MVP Microsoft Access
"MikeH" <mh****@bigfoot.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...

Definitely clutching at straws now...

The version of DCOM98 on the Windows 98SE PC wasn't the 'latest and
greatest' so I installed 1.3. It didn't make any difference at all. The qdf.Parameters(0).Value still returns null instead of the expected row count. Identical code runs fine on my Windows 2000 PC.

Any more ideas anyone? ...or is this one of those cases where the quickest solution is going to be a re-write to get the row count in a different way?

Thanks,

Mike

[Terry Kreft]

BTW, you do realise that the RETURN statement is meant to return a value
indicating success or failure and that normally it returns 0 to indicate
success?

I think your VBA code should be

Dim wrkODBC As Workspace
Dim strConnection As String
Dim qdf As QueryDef

strConnection = "ODBC;DSN=ImportCM;Database=ImportCM;
UID=xxxx;PWD=yyyy"
Set wrkODBC = CreateWorkspace("", "", "", dbUseODBC)
Set conImportCM = wrkODBC.OpenConnection("", dbDriverComplete, ,
strConnection)

' ***************************
' NOTE DIFFERENCE HERE
Set qdf = conImportCM.CreateQueryDef("", "{? = call
prcCountViewContract(?)}")

qdf.Parameters(0).Direction = dbParamReturnValue
qdf.Parameters(1).Direction = dbParamReturnValue
qdf.Execute

MsgBox "Success is " & (qdf.Parameters(0).Value > 0)
MsgBox "Row count is " & qdf.Parameters(1).Value

--
Terry Kreft
MVP Microsoft Access
"MikeH" <mh****@bigfoot.com> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...

Terry,

I changed the stored procedure to:-

CREATE PROCEDURE prcCountViewContract
@Rows int = NULL OUTPUT
AS
SELECT @Rows = COUNT (*) FROM dbo.tblViewContract
RETURN @Rows
GO

... and it works fine now. Thanks very much for your help!

Regards,

Mike

Terry Kreft wrote:

Mike,
Looking at your original post again, I would have written the SP as

CREATE PROCEDURE prcCountViewContract
@Rows int = NULL OUTPUT
AS
SELECT @Rows = COUNT(*) FROM dbo.tblViewContract

--
Terry Kreft
MVP Microsoft Access
"MikeH" <mh****@bigfoot.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...

Definitely clutching at straws now...

The version of DCOM98 on the Windows 98SE PC wasn't the 'latest and
greatest' so I installed 1.3. It didn't make any difference at all. The qdf.Parameters(0).Value still returns null instead of the expected row count. Identical code runs fine on my Windows 2000 PC.

Any more ideas anyone? ...or is this one of those cases where the quickest solution is going to be a re-write to get the row count in a different way?

Thanks,

Mike

[MikeH]

Terry,

Thanks for the clarification. I guess it comes as no surprise to you
that I'm a bit new to the use of SPs from Access. I think that was a
bit of playing "guess the syntax" on my part! Time to get the manuals
out....

At least it all works now. Thanks again for all your help. Much
appreciated.

Regards,

Mike

Terry Kreft wrote:

BTW, you do realise that the RETURN statement is meant to return a value indicating success or failure and that normally it returns 0 to indicate success?

I think your VBA code should be

Dim wrkODBC As Workspace
Dim strConnection As String
Dim qdf As QueryDef

strConnection = "ODBC;DSN=ImportCM;Database=ImportCM;
UID=xxxx;PWD=yyyy"
Set wrkODBC = CreateWorkspace("", "", "", dbUseODBC)
Set conImportCM = wrkODBC.OpenConnection("", dbDriverComplete, ,
strConnection)

' ***************************
' NOTE DIFFERENCE HERE
Set qdf = conImportCM.CreateQueryDef("", "{? = call
prcCountViewContract(?)}")

qdf.Parameters(0).Direction = dbParamReturnValue
qdf.Parameters(1).Direction = dbParamReturnValue
qdf.Execute

MsgBox "Success is " & (qdf.Parameters(0).Value > 0)
MsgBox "Row count is " & qdf.Parameters(1).Value

--
Terry Kreft
MVP Microsoft Access
"MikeH" <mh****@bigfoot.com> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...

Terry,

I changed the stored procedure to:-

CREATE PROCEDURE prcCountViewContract
@Rows int = NULL OUTPUT
AS
SELECT @Rows = COUNT (*) FROM dbo.tblViewContract
RETURN @Rows
GO

... and it works fine now. Thanks very much for your help!

Regards,

Mike

Terry Kreft wrote:

Mike,
Looking at your original post again, I would have written the SP as
CREATE PROCEDURE prcCountViewContract
@Rows int = NULL OUTPUT
AS
SELECT @Rows = COUNT(*) FROM dbo.tblViewContract

--
Terry Kreft
MVP Microsoft Access
"MikeH" <mh****@bigfoot.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...
> Definitely clutching at straws now...
>
> The version of DCOM98 on the Windows 98SE PC wasn't the 'latest and > greatest' so I installed 1.3. It didn't make any difference at

all.

> The qdf.Parameters(0).Value still returns null instead of the

expected

> row count. Identical code runs fine on my Windows 2000 PC.
>
> Any more ideas anyone? ...or is this one of those cases

where the

> quickest solution is going to be a re-write to get the row

count in a

> different way?
>
> Thanks,
>
> Mike
>