b heygood
bheygood at abestsystems.com
Thu May 5 09:45:38 CDT 2011
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