CaroleTSullivan
asked on
How do I call a stored procedure from MSAccess, passing it parameters and displaying the recordset that is returned?
I have an MSAccess application that calls various stored procedures. Below is an example of the code.
Public Sub LoadRefreshUserConfigurati
On Error GoTo LoadRefreshUserConfigurati
'*************************
'Set name of stored procedure and parms '
'*************************
objCommand.CommandText = "dbo.Insert_UploadTables_U
objCommand.CommandType = adCmdStoredProc
objCommand.Parameters.Refr
objCommand("@strmandt") = strMandt
objCommand.Execute
LoadRefreshUserConfigurati
Exit Sub
LoadRefreshUserConfigurati
Call DisplayError("LoadRefreshU
Resume LoadRefreshUserConfigurati
End Sub
It works very well, but now because non of the stored procedures have to return data. Question: How do I call a stored procedure that will execute a query and display the data as if was a local MSAccess query. The reason why I need to do this is because the queries are running waaaaaayyyyyy to slowly for some of our clients located across the world. I was thinking of rewriting all the queries as stored procedures so that all the processing happens on the server side. Anyone have an example.
Public Sub LoadRefreshUserConfigurati
On Error GoTo LoadRefreshUserConfigurati
'*************************
'Set name of stored procedure and parms '
'*************************
objCommand.CommandText = "dbo.Insert_UploadTables_U
objCommand.CommandType = adCmdStoredProc
objCommand.Parameters.Refr
objCommand("@strmandt") = strMandt
objCommand.Execute
LoadRefreshUserConfigurati
Exit Sub
LoadRefreshUserConfigurati
Call DisplayError("LoadRefreshU
Resume LoadRefreshUserConfigurati
End Sub
It works very well, but now because non of the stored procedures have to return data. Question: How do I call a stored procedure that will execute a query and display the data as if was a local MSAccess query. The reason why I need to do this is because the queries are running waaaaaayyyyyy to slowly for some of our clients located across the world. I was thinking of rewriting all the queries as stored procedures so that all the processing happens on the server side. Anyone have an example.
run it as a pass-thru query.
ASKER
I'm sorry, but I've never done that. How do I do that? Thanks!
A pass-thru query sends whatever SQL text you send directly to the SQL server.
Is this an MDB or is it an ADP?
If it's an MDB, you create a query. Select "Design View", don't pick any tables.
Under the query menu you go to Query Type, go to SQL Specific, then Pass-Thru.
You type your sql syntax here just like you would if you were running the query from QA.
You will have to specify an ODBC string pointing Access to the SQL Database.
Is this an MDB or is it an ADP?
If it's an MDB, you create a query. Select "Design View", don't pick any tables.
Under the query menu you go to Query Type, go to SQL Specific, then Pass-Thru.
You type your sql syntax here just like you would if you were running the query from QA.
You will have to specify an ODBC string pointing Access to the SQL Database.
following from easmike
the quickest way to get the data into your access db is to create 2 queries:
the first being the pas thru query with the odbc strings aesmike mentioned
the 2nd a "make table" or "insert" query which selects from your 1st query and select into or insert into a new/empty table.
then run the 2nd query.
you can automate this using DAO in your form or standard module in access.
the quickest way to get the data into your access db is to create 2 queries:
the first being the pas thru query with the odbc strings aesmike mentioned
the 2nd a "make table" or "insert" query which selects from your 1st query and select into or insert into a new/empty table.
then run the 2nd query.
you can automate this using DAO in your form or standard module in access.
For my 2 cents worth, I always find it best to create a view in SQL server and then link to it via Access. This way SQL is doing all the damn work! and Access just views it as a table.
re: Innov
you can link the SQL Server views but then if you query it from Access, Access would be processing it rather than SQL Server. The reason for pass thru queries is for SQL Server to process the data (as a view, stored proc etc)
CaroleTSullivan,
i note you are using ADO in your example. if you want to use ADO rather than DAO you'll need to create two ADO recordsets (1 for SQL server, the other for Access), loop throug the SQL and populate the Access. or you can use the DAO method i mentioned above where no loop is required.
let me know which option you want and i can post some code.
you can link the SQL Server views but then if you query it from Access, Access would be processing it rather than SQL Server. The reason for pass thru queries is for SQL Server to process the data (as a view, stored proc etc)
CaroleTSullivan,
i note you are using ADO in your example. if you want to use ADO rather than DAO you'll need to create two ADO recordsets (1 for SQL server, the other for Access), loop throug the SQL and populate the Access. or you can use the DAO method i mentioned above where no loop is required.
let me know which option you want and i can post some code.
Pass-Thru Queries make the SQL server do all the work--that's the point of them.
For complicated stuff, I use Pass-Thru queries to actually write stored procedures on-the-fly and execute them--all back at the server.
For complicated stuff, I use Pass-Thru queries to actually write stored procedures on-the-fly and execute them--all back at the server.
ASKER
AESMIKE: The Pass thru queries sound like the most straight forward. Can you post an example of how to incorporate the ODBC string within the SQL? That's where I'm stuck. Thanks.
Doesn't go into the SQL
Just place
ODBC;DSN=DSNName
in the "ODBC Connect Str" property of the query
Frankytee, are you sure that a view in SQLServer isn't processed by the server? I'm no expert is SQLServer, but I have numerous examples where using a view vs an Access query SIGNIFICANTLY improves performance.
Just place
ODBC;DSN=DSNName
in the "ODBC Connect Str" property of the query
Frankytee, are you sure that a view in SQLServer isn't processed by the server? I'm no expert is SQLServer, but I have numerous examples where using a view vs an Access query SIGNIFICANTLY improves performance.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
AESMIKE: I tried the pass thru and just am not getting it to work with accepting parameters. It sounds like your two utility functions are perhaps what I'm looking for. May I try them. We have about 50 queries that we run and they are all so complicated unfortunately.....with queries calling queries, calling queries....all which use parameters. That's why I thought writing a stored procedure would be the way to go, but if you think your two utilities will do the trick, I would love seeing them. At this point, I am pulling my hair out and am noticing some blad spot! :-o
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
note for this to work you would need to create an ODBC connection in Control Panel->Admin Tools->ODBC and specify whatever DSN connection, login, password etc.