David Emerson
newsgrps at dalyn.co.nz
Thu May 5 15:05:00 CDT 2011
Bob,
Have you tried running any other stored procedures using your code
(even creating a simple one with no parameters)?
David
At 6/05/2011, b heygood wrote:
>Yes, the sp works on the server.
>
>I am checking on my user security level, tho my attachment / linking of
>tables is using the same user/pass data and successful.
>
>Sure would like to figure this out.
>
>thx to all who responded.
>
>Bob Heygood
>
>
>-----Original Message-----
>From: accessd-bounces at databaseadvisors.com
>[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Doug Murphy
>Sent: Tuesday, May 03, 2011 4:06 PM
>To: 'Access Developers discussion and problem solving'
>Subject: Re: [AccessD] A97 SQL Server
>
>Just a dumb thought but does the SP work if you test it on the server?
>
>-----Original Message-----
>From: accessd-bounces at databaseadvisors.com
>[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of b heygood
>Sent: Tuesday, May 03, 2011 2:41 PM
>To: 'Access Developers discussion and problem solving'
>Subject: Re: [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
>
>--
>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