How to use access to call the background Stored Procedure

However, it is slow to process a large amount of data. When a large amount of data needs to be processed, it cannot be processed on the client, but must be processed on the server. However, access and server are mostly connected through ODBC, which increases the difficulty of calling backend stored procedures. Through long-term exploration in practical work, I can use the following three methods to call the background storage process according to different business needs.
1. Access submits jobs to the backend. Each job corresponds to one stored procedure. In SQL Server 6.5, tasks are created through task manager, while in Oracle 8.0, jobs are created through managing job queue. In access, insert a job into the job_list table, which is generally run once every night. The businesses corresponding to these jobs generally need to process a large amount of data, but the real-time performance is relatively low. In our business, a typical example is a large amount of electricity computing every night.
2. Create some triggers on the server and activate these triggers in access. Both SQL Server 6.5 and Oracle 8.0 are implemented through create trigger. In access, different triggers are activated by inserting, modifying, and deleting records based on different services. One example of our business is that when the business personnel modify the user's electric meter reading, the trigger for calculating the electricity bill is activated and the user's electricity bill can be recalculated immediately. In this way, it can be shared with batchcompute Program You do not need to re-develop on the access end to speed up front-end response.
3. the preceding two methods can only be used to indirectly call the background stored procedure. Access also provides a method for directly calling. You can use access to transmit queries and directly send commands to the database server.
The procedure for creating an access transfer query is as follows:
(1) Click the "query" tab in the Database window, and then click the "new" button.
(2) In the "new query" dialog box, click the "design view" option, and then click the "OK" button.
(3) In the displayed table dialog box, click the close button.
(4) On the "query" menu, refer to "SQL statement conditions" and click "pass.
(5) on the toolbar, click "properties" to display the query Attribute Table.
(6) In the query Attribute Table, set the "ODBC connection string" attribute to specify the database information to be connected. You can enter the connection information: "ODBC; DSN = ntserver0; uid = sa; Pwd =; database = BMS", or use the "Builder" button to generate the connection.
(7) because the stored procedure does not need to return records, set the "Return records" attribute to "no ".
(8) In the "SQL pass query" window, enter the pass query: exec statistics. Statistics is a stored procedure of the SQL Server database.
(9) Close the query and save the query as stat_query.
You can run the following query methods:
(1) Use the Microsoft Access macro openquery.
(2) Use the following event process:
Private sub statistics_click ()
Dim dbs as database, qdf as querydef
Dim tmq as dynaset
Dim strsql as string
Set DBS = currentdb
'Open the pass query stat_query created above.
Set qdf = DBs. openquerydef ("stat_query ")
Qdf. Execute
Set DBS = nothing
End sub
This method is completely fine in SQL Server 6.5 and cannot be implemented in Oracle 8.0. The error message is: ORA-00900 invalid SQL statement. because this method does not require an intermediate table to queue or activate the background storage process, it has a wide range of applications. Any business that needs to process a large amount of data can be put on the server side for processing, such as statistical reports.
Through the above three methods, access has solved the weakness of slow processing speed for a large amount of data, and greatly increased the data processing capability of Shenzhen Power Supply Bureau.