Calling a background stored procedure in Access

Source: Internet
Author: User
Tags odbc odbc connection
Access is an excellent front-end development tool that is easy to learn, user-friendly, easy to develop, and flexible with other database interfaces (Client/server). 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.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.