[AccessD] A97 SQL Server

It's been a while since I've used A97
Can you do it, via a query by first modifying the querydef?

Dim qdfCurr As QueryDef
Dim strSQL as String

strSQL = "EXEC sp_Add_FutureServicesOrders_byRoute @Parm1=" & RTE
Set qdfCurr = CurrentDb().QueryDefs("MyQueryNameHere")
qdfCurr.SQL = strSQL



On Thu, Apr 28, 2011 at 8:18 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
>