emulating a stored procedure

[toddbailey]

Hi All,

Is there a way to emulate a stored procedure in a excel to access link?

allow me to expand on this:


I have a excel sheet with vb/a code that does a lot of data processing and causes a lot of network traffic due to the volume of data being processed, once the datasets are ready, 1/2 a dozen worksheet tabs are populated.

Processing time can take several minutes to an hour.
Once the data is prepared, loading into Excel take a matter of a minute or 2

I need to rethink the data prep stage.

If I were using sql server or any other industrial strength database engine, I'd put the processing logic on the database server and leave the front end to only retrieve the final data sets for display.

I was wondering if I could follow a similar design in Access, in this case having Excel call a series of vb/a module, perhaps providing parameters or storing the parameters in a table. Then having the vb/a modules execute on the server that houses the access database file, Or is access just a file and all the processing is done on the machine that opens the file?

I'm looking for ways to minimize network traffic and all the issues related to it.

Ideas so far:
1. Copy the entire access file off the network and store it on the local machine, and all database i/o is then done locally.
The problem with this method is the master database file is over 500 mb in size.

Another thought, a local database, containing only the required tables. When the user hits the go button, the code could query the local database and if out of date by x hours, the tables would be reimported from the master.

While this idea makes the most sense to me, I not sure if there is a way to have excel do a table import in access, other than a delete * from table or drop table and a series of select into statements.

There are about 2 dozen tables containing a few hundred of thousand records. So a lot of uncompressed data would be brought in, Yes I can use where clauses to filter the data but then there is the added processing cost of filtering the data.

so the questions are:
Can I run vb/a modules from machine A with the database on server B and where is the code executed at?

or Do I need the database locally to eliminate net traffic

The easy answer is to move the database to Sql server and use stored procedures. That way I can make a easy ado call to execute the stored procedure with a few parameters, and avoid impacting the user's machine with unnecessary processing loads and traffic, once the data is prepared, automatically send an email to the user and they can open excel and import only the data sets needed.



thanks

 

[DJkarl]

If you plan to use Access and Excel to do this you are going to utilize network bandwidth unless the both the front-end and backend are off the network. Unlike SQL Server that has a service running which does all the heavy lifting on the server side, Access has no such service running, the MSACCESS.EXE is running on your machine (unless you're running Citrix or remoting into a server).

 

[toddbailey]

I knew this to be true back on the good old days of access 2000 and older but had hoped that maybe MS had enhanced Access to provide a client/server model with perhaps the purchase & installation of an add on package. Since this is not the case, the only option is to have a current copy of the database and excel file on the local machine. I implemended this with a few lines of code, that checks if the folder or database file exists, creates if necessary, asks if it's ok to copy/over write and the whole process is done in a few minutes. Network problem eliminated.
Not the ideal solution but workable

anyway, Thanks for sharing

 

[Simon_MT]

What about the data prep being on the Server i.e. a FE on the BE Server [location]. Then use the a FE locally. No network traffic. Are you using a persistent connection to the BE - having a BE table also open for the FE instance of Access?

Simon

 

[toddbailey]

This is why I was inquiring if Access had a means of stored procedures, the closest I've found is stored queries. In my case they don't provide much added value, as there is a lot of cost calculations and conditional processing being done.

If access provided stored procedure capability OR if I could call a vb/a module for a remote client, I could put 90 % of the logic on the backend server and the front end could become a lite weight client.
But Access in it's current form doesn't have a client/server model, so even if I could call a query or code module, it's still executing on the client.