[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