[AccessD] A97 SQL Server

David McAfee davidmcafee at gmail.com
Tue May 3 17:22:01 CDT 2011


Bob, you might have already mentioned this, but...

Can you open the query from the F11/database window? Does Access prompt you
for the parameter?



On Tue, May 3, 2011 at 2:40 PM, b heygood <bheygood at abestsystems.com> wrote:

> 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
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>



More information about the AccessD mailing list