Stuart McLachlan
stuart at lexacorp.com.pg
Tue Aug 19 22:03:32 CDT 2008
Simplest way is to create a "PassThrough". Query
IN 2003 (other version should be similar)"
1. Create a New query but don't select any tables.
2. Select Query - SQL Specific - Passthrough from the main menu bar.
3. Select View - Properties (Or hit Alt+Enter)
4. Click in OBDC Connect Str, thenclick on the builder button (...) and select your ODBC
source (or enter a connection string if you have one written)
5. for an update query ,set the ReturnsRecords property to No.
6. Enter the sp name in the build window
7. Save the Query
Then just open the query when required.
Note that you can't pass parameters through a PassThrough query, if your sp requires
parameters, you'll have to do it in code with something like this:
Dim db As DAO.Database
Dim qdPTQuery As QueryDef
Dim strSQL As String
Dim strParam as String
strParam = "123"
Set db = CurrentDb
strSQL = "myUpdateSP " & strParam
Set qdPTQuery = db.CreateQueryDef("myPassThroughQuery")
qdPTQuery.Connect = "ODBC;DSN=................."
qdPTQuery.SQL = strSQL
qdPTQuery.returnsrecords = False 'if an update query
qdPTQuery.Close
db.Close
Set db = Nothing
On 20 Aug 2008 at 11:21, Darren D wrote:
> Hi team
>
>
>
> I need to call some Stored procedures (SPROCS) in SQL Server from my Access dB
>
> The SPROCS will be both simple update ones and some of them will return results
>
> Does anyone have examples how to interact with both types?
>
> IE Just run one. And with the other get the results back and display them in my
> Access dB?
>
>
>
> I think I have asked this question before but can't find the answer
>
> I think I was using an ADP when I asked though - This is a plain ole' MDB
> request
>
>
>
> Many thanks in advance
>
>
>
> Darren
>
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
--
Stuart Mclachlan