Calling a background stored procedure in Access

access| Stored Procedures | Background Access is an excellent front-end development tool for Client/server, easy to learn, user-friendly, easy to develop, and flexible with other database interfaces. However, it is slower to deal with large amounts of data. Shenzhen power supply Bureau currently has 600,000 users, the following 6 sales offices, through the 64K DDN draw connected, if there is a sales office to 10,000 users to deal with, it will take 2-3 hours, seriously affecting the development of other businesses. Therefore, when there is a large amount of data to be processed, it cannot be processed on the client side and must be processed on the server side. However, most connections between access and the server end are connected through ODBC, which increases the difficulty of invoking a background stored procedure. The author through the practical work in the long-term exploration, according to different business needs, you can use the following three ways to call the background stored procedures.

First, Access to the background to submit jobs, this job corresponds to a stored procedure. In SQL Server 6.5, the task Manager is used to create the corresponding task; In Oracle 8.0, the managing job Queue is used to create the appropriate job. In Access, insert a job in the Job_list table, which typically runs every night. These jobs correspond to businesses that typically need to deal with very large amounts of data, while real time is low, and the typical example in our business is a large amount of electricity calculation every night.

Second, set up some triggers (Trigger) on the server side to activate these triggers in Access. In SQL Server 6.5 and Oracle 8.0, this is done through the Create Trigger. In access, different triggers are activated by inserting, modifying, and deleting records, depending on the business. In one example of our business, when a business person modifies a user's meter reading, activating a trigger that calculates the electricity bill can immediately recalculate the user's electricity bill. This way, you can share the same program with the bulk calculation of electricity, do not need to be developed on the access side, there can speed up the front-end response speed.

Three or more of the two methods, which can only be called indirectly by calling a background stored procedure, Access also provides a direct call to send a command directly to the database server using an Access pass-through query.

To establish an Access pass-through query, follow these steps:

(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) Click the Close button in the Show Table dialog box.

(4) On the Query menu, point to SQL statement conditions, and then click the Delivery command.

(5) On the toolbar, click the Properties button to display the query property sheet.

(6) In the Query property sheet, set the ODBC connection string property to specify the database information to connect to. You can enter the connection information: "ODBC;DSN=NTSERVER0; Uid=sa; pwd=;D Atabase=bms, or built with the build button.

(7) Set the return record property to no because the stored procedure does not return a record.

(8) in the SQL Pass-through Query window, enter a pass-through query: exec statistics. Statistics is a stored procedure in a SQL Server database.

(9) Close the query, save the query as: Stat_query.

The way to run a pass-through query is:

(1) Use Microsoft Access's macro OpenQuery.

(2) Use the following event procedure:

Private Sub Statistic _click ()
Dim dbs as Database, QDF as QueryDef
Dim TMQ as Dynaset
Dim strSQL as String

Set dbs = CurrentDb
' Open the pass-through query built above Stat_query
Set QDF = dbs. OpenQueryDef ("Stat_query")
Qdf. Execute
Set dbs = Nothing
End Sub

This approach is completely fine in SQL Server 6.5 and cannot be implemented in Oracle 8.0, with the error message: ORA-00900 Invalid SQL statement. Since this method does not require an intermediate table to queue or activate a background stored procedure, its application is wide, and any business that needs to handle large amounts of data can be put on the server side, such as statistical reports.

Through the above three methods, solve the access to a large number of data processing slow weakness, greatly increased the Shenzhen power Supply Bureau data processing capacity.