[AccessD] A97 SQL Server

Thanks again to all.

dropping the parameter does no differently, not does not using a variable,
just a number that I know is good....

RTE is a long var.
"ODBC - Call failed" message from A97
The code generates no errors up to the last line.
I can link to tables using the log on info and password successfully.
Sure would like to figure this out..

Latest Code - not working:

CONNECT_STR = "ODBC;DSN=" & DSNName & ";UID=" & strSQLUser & ";PWD=" &
strSQLPassword & ";DATABASE = " & DSNName

'populate the object variables
Set wkTest = CreateWorkspace("ODBCWorkspace", "admin", "", dbUseODBC)
Set conTest = wkTest.OpenConnection("CONNECTION", dbDriverNoPrompt, True,
CONNECT_STR)
conTest.QueryTimeout = 0

'Execute the stored procedure (SQL Server 7.0)
DoEvents
conTest.Execute "EXEC PurgeOldServiceData '" & 11 & "'"
'conTest.Execute "sp_Add_FutureServicesOrders_byRoute '" & "M" & "'"
conTest.Execute "EXEC sp_Add_FutureServicesOrders_byRoute " & RTE 


Bob Heygood


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Charlotte Foust
Sent: Tuesday, May 03, 2011 9:13 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] A97 SQL Server

The problem may be that trying to concatenate the parameter on the fly
doesn't work.  It's been so long since I used 97 that I simply don't recall.
If adding a space to the string before the parameter isn't working, I wonder
if it's because the EXEC is trying to run the root sp name without the
parameter.  I suppose you could test by dropping the parameter and seeing if
you get the same error.

Charlotte Foust

On Tue, May 3, 2011 at 9:00 AM, RANDALL R ANTHONY <RRANTHON at sentara.com>
wrote:
> Off the top of my head...
> Exec sp_name 123 is the syntax for executing/passing one number parameter
for an SP.
> Maybe your line needs to have the variable inside the double quotes in
some manner.
> conTest.Execute "EXEC sp_Add_FutureServicesOrders_byRoute" & RTE
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com 
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Charlotte 
> Foust
> Sent: Tuesday, May 03, 2011 10:27 AM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] A97 SQL Server
>
> It's been a long time, but I don't think it will work to try to 
> specify the name of the stored procedure using a variable like that.  
> Someone else can correct me if I'm having a senior moment.
>
> Charlotte Foust
> On Apr 28, 2011 8:19 AM, "b heygood" <bheygood at abestsystems.com> wrote:
>> having a "ODBC - Call failed" message from A97 when running the code
> below.
>> RTE is a long var.
>> code generates no errors up to the last line.
>> I can link to tables using the log on info and password successfully.
>>
>> any ideas??
>>
>>
>>
>> 'populate the object variables
>> Set wkTest = CreateWorkspace("ODBCWorkspace", "admin", "", dbUseODBC) 
>> Set conTest = wkTest.OpenConnection("CONNECTION", dbDriverNoPrompt, 
>> True,
>> CONNECT_STR)
>> conTest.QueryTimeout = 0
>>
>> 'Execute the stored procedure (SQL Server 7.0) DoEvents 
>> conTest.Execute "EXEC sp_Add_FutureServicesOrders_byRoute" & RTE
>>
>> --
>> AccessD mailing list
>> AccessD at databaseadvisors.com
>> http://databaseadvisors.com/mailman/listinfo/accessd
>> Website: http://www.databaseadvisors.com
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com