However, it is slower when processing large amounts of data. When there is a large amount of data to be processed, it cannot be processed on the Client side, but must be processed on the Server side. However, most of the ACCESS and Server terminals are connected through ODBC, which increases the difficulty of calling background stored procedures. Through long-term exploration in actual work, the author can use the following three methods to call the background stored procedures according to different business needs.
1. Access Submits jobs to the background, and these jobs correspond to stored procedures. In SQL Server 6.5, the corresponding task is established through the Task Manager; and in Oracle 8.0, the corresponding job is established through the Managing Job Queue. In Access, insert a job in the job_list table, which usually runs once a night. The corresponding businesses of these jobs generally require processing a very large amount of data, and the real-time performance is low. A typical example in our business is the calculation of large amounts of electricity bills every night.
2. Create some triggers (Triggers) on the Server side and activate these triggers in Access. In SQL Server 6.5 and Oracle 8.0, both are implemented through Create Trigger. In Access, different triggers are activated by inserting, modifying, and deleting records according to different services. An example in our business is that when a business person modifies the user's electricity meter readings, he activates the trigger for calculating the electricity bill and can immediately recalculate the user's electricity bill. In this way, the same program can be shared with batch calculation of electricity bills, and there is no need to redevelop it on the Access side, which can speed up the response speed of the front-end.
3. The above two methods can only be said to indirectly call the background stored procedure. Access also provides a direct call method, which can use Access to pass queries to directly send commands to the database server.
The steps to establish an Access delivery query are as follows:
(1) Click the Query tab in the database window, and then click the New button.
(2) Click the Design View option in the New Query dialog box, and then click the OK button.
(3) Click the Close button in the "Show Table" dialog box.
(4) On the Query menu, point to SQL Statement Conditions, and then click the Pass command.
(5) On the toolbar, click the Properties button to display the query attribute table.
(6) In the query attribute table, set the "ODBC Connection String" property to specify the database information to be connected. You can enter connection information: "ODBC;DSN=ntserver0;UID=sa;PWD=;DATABASE=BMS", or use the "Generator" button to generate.
(7) Since stored procedures do not need to return records, the "Return Record" attribute is set to "No".
(8) In the SQL Pass Query window, enter the pass query: exec statistics. Statistics is a stored procedure for SQL Server databases.
(9) Close the query and save the query as: stat_query.
The methods to run the pass query are:
(1) Use Microsoft Access's 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 stat_query created above
Set qdf = ("stat_query ")
Set dbs = Nothing
End Sub
This method is completely fine to implement in SQL Server 6.5, but cannot be implemented in Oracle 8.0. The error message is: ORA-00900 invalid SQL statement. Since this method does not require an intermediate table to queue or activate the background stored procedure, its application scope is very wide. Any business that needs to process a large amount of data can be placed on the Server side for processing, such as statistical reports.
Through the above three methods, Access's weakness of slow processing speed for large amounts of data has been solved, and the data processing capacity of Shenzhen Power Supply Bureau has been greatly enhanced.
1. Access Submits jobs to the background, and these jobs correspond to stored procedures. In SQL Server 6.5, the corresponding task is established through the Task Manager; and in Oracle 8.0, the corresponding job is established through the Managing Job Queue. In Access, insert a job in the job_list table, which usually runs once a night. The corresponding businesses of these jobs generally require processing a very large amount of data, and the real-time performance is low. A typical example in our business is the calculation of large amounts of electricity bills every night.
2. Create some triggers (Triggers) on the Server side and activate these triggers in Access. In SQL Server 6.5 and Oracle 8.0, both are implemented through Create Trigger. In Access, different triggers are activated by inserting, modifying, and deleting records according to different services. An example in our business is that when a business person modifies the user's electricity meter readings, he activates the trigger for calculating the electricity bill and can immediately recalculate the user's electricity bill. In this way, the same program can be shared with batch calculation of electricity bills, and there is no need to redevelop it on the Access side, which can speed up the response speed of the front-end.
3. The above two methods can only be said to indirectly call the background stored procedure. Access also provides a direct call method, which can use Access to pass queries to directly send commands to the database server.
The steps to establish an Access delivery query are as follows:
(1) Click the Query tab in the database window, and then click the New button.
(2) Click the Design View option in the New Query dialog box, and then click the OK button.
(3) Click the Close button in the "Show Table" dialog box.
(4) On the Query menu, point to SQL Statement Conditions, and then click the Pass command.
(5) On the toolbar, click the Properties button to display the query attribute table.
(6) In the query attribute table, set the "ODBC Connection String" property to specify the database information to be connected. You can enter connection information: "ODBC;DSN=ntserver0;UID=sa;PWD=;DATABASE=BMS", or use the "Generator" button to generate.
(7) Since stored procedures do not need to return records, the "Return Record" attribute is set to "No".
(8) In the SQL Pass Query window, enter the pass query: exec statistics. Statistics is a stored procedure for SQL Server databases.
(9) Close the query and save the query as: stat_query.
The methods to run the pass query are:
(1) Use Microsoft Access's 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 stat_query created above
Set qdf = ("stat_query ")
Set dbs = Nothing
End Sub
This method is completely fine to implement in SQL Server 6.5, but cannot be implemented in Oracle 8.0. The error message is: ORA-00900 invalid SQL statement. Since this method does not require an intermediate table to queue or activate the background stored procedure, its application scope is very wide. Any business that needs to process a large amount of data can be placed on the Server side for processing, such as statistical reports.
Through the above three methods, Access's weakness of slow processing speed for large amounts of data has been solved, and the data processing capacity of Shenzhen Power Supply Bureau has been greatly enhanced.