[AccessD] A97 SQL Server

no the PurgeOld.... is some code I used before. again just trying anything
to test.
No, just one parameter.
thx for responding.


bob 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of David Emerson
Sent: Tuesday, May 03, 2011 12:16 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] A97 SQL Server

Bob,

You say that the last line is where it falls over.  Does the "
conTest.Execute "EXEC PurgeOldServiceData '" & 11 & "'"  " line work?

If so then it would indicate that it is not the connection but the call to
the sp_Add_FutureServicesOrders_byRoute procedure that is the problem.
Basic stuff but have you double checked the name of the stored procedure.
Does it have more than 1 parameter?

David Emerson
Dalyn Software Ltd
New Zealand




At 4/05/2011, b heygood wrote:
>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
>
>
>--
>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