Deployment approach

[Noel]

Any thoughts on the best, most efficient, least susceptible to corruption approach would be greatly appreciated.

The MS Access application is being designed as a multi-user (network) calendar app that uses data from a foxpro application. Specifically foxpro free tables (dbfs) that I'm retrieving and importing into MS Access. In addition, the application will supplement the foxpro data by managing related data (not being managed in the foxpro app) within access.

The foxpro application is the principal application since it's the company's legacy application that is provided by the office's corporate group in Minneapolis (i.e. off site) and we can't replace it.

So, if there are data changes, the changes are made in the foxpro app (which has updates uploaded to the corporate office), then the user will need to click a button in Access to retrieve the dbf and import it again.

Right now, my approach is to have a frontend (FE) and backend (BE), where the FE is the main application that the users will work with. All the tables will be linked to the FE via the BE. When the data is changed in foxpro, the user will click a button in the FE that opens the BE exclusively. The startup form in the BE has a button that, when clicked will retrieve the dbf (fso copy), import it (transferdatabase using ODBC) and then update (docmd.openquery)the resident MS Access tables. (I.e. Only one person can run the update procedures via the BE at a time).

Some concerns I have:
-if the BE is opened exclusively, will the linked tables be affected? I.e. can other users continue to operate the FE without any problems?
-when the update procedures are being performed (i.e. TransferDatabase method, update/append/deleted queries), will the linked tables be affected?I.e. can users continue to operate the FE without any problems?
-I don't see anyway around the TransferDatabase method, but for updating the resident tables, I'm using primarily docmd.openquery methods. Would some of the slower processes (update queries) be quicker if I were to update by code?
-any other concerns I should be thinking about? Specifically with regards to 1) maximizing application speed and 2) minimizing concurrent user issues.

Thanks - Everything has pretty much been built, I'm just wanting to maximize user enjoyment.

Noel

 

[DJkarl]

Some concerns I have:
-if the BE is opened exclusively, will the linked tables be affected? I.e. can other users continue to operate the FE without any problems?

In my experience yes, the links will be affected. Additionally you won't be able to open the database exclusively unless everyone has their FE versions closed.

when the update procedures are being performed (i.e. TransferDatabase method, update/append/deleted queries), will the linked tables be affected?I.e. can users continue to operate the FE without any problems?

I believe the users will get an error message if they try to access the links while the database is opened exclusively.

-I don't see anyway around the TransferDatabase method, but for updating the resident tables, I'm using primarily docmd.openquery methods. Would some of the slower processes (update queries) be quicker if I were to update by code?

For maximizing speed you could use the RunSQL or Execute methods for running queries and you might see some performance benefits. The biggest thing for update queries is to turn off transactions, this saves me huge amounts of time when these queries run.

 

[Noel]

thanks DJKarl

re: having to close the FE in order for the BE to be opened exclusively - The opencurrentdatabase does that for you without all the users having to exit. The tests I've run seemed to work.

Thanks for the RunSQL and Execute ideas.

Noel

 

[Noel]

sorry DJKarl, I forgot to ask

When you say turn transactions off, you don't mean setwarnings false, right?

Thanks.

NOel

 

[Noel]

hmmmm

I guess I didn't test thoroughly. The FE does infact complain when another computer has an instance of the FE open plus has the BE open exclusively; due to the fact that the tables are linked.

What's an approach where the FE can continue to operate while the BE is in use?

What if I have another db (call it a "middle end (ME)" totaling 3) that holds the tables, whereby the FE's tables are linked tables via the ME and the BE does all the processing into the ME (i.e. make tables into ME, update linked tables in the BE via the ME). This should still allow for exclusivity of the update procedures (ie the BE), and the FE users can still access the tables so long as the BE processes are not doing any updates to the particular table? Right?

Hmm. Anyone have any other approaches?

Thanks.

Noel

 

[boblarson]

When you say turn transactions off, you don't mean setwarnings false, right?

Thanks.

NOel

No, when you use CurrentDb.Execute you can choose to use transactions which is supposed to roll-back the data to the pre execute state, if the process fails for any reason. If you turn them off, then all changes made to the data by the SQL before the error occurs will remain committed.

 

[DJkarl]

When you say turn transactions off, you don't mean setwarnings false, right?

Thanks.

NOel

Although Bob Larson answered this one already I'll just say the reason I turn them off is I'm dealing with data sets too large to roll back, so with transactions on Access tries to keep track of everything until it runs out of memory then errors and tries to finish without transactions, this is a lot of uneeded overhead by turning them off I save a lot of time.

For example I have an update query that was taking between 5 - 6 minutes, by turning off transactions it reduced the runtime to about 30 seconds.

 

[boblarson]

Although Bob Larson answered this one already I'll just say the reason I turn them off is I'm dealing with data sets too large to roll back, so with transactions on Access tries to keep track of everything until it runs out of memory then errors and tries to finish without transactions, this is a lot of uneeded overhead by turning them off I save a lot of time.

For example I have an update query that was taking between 5 - 6 minutes, by turning off transactions it reduced the runtime to about 30 seconds.

And that's okay as long as you understand the risks (and in some cases as you say, it just isn't going to work with Transactions due to memory limitations and the fact that Access runs on the local machine and not a true server).

 

[Noel]

thanks folks

I'll try out turning transactions off since I'm working with duplicate data (foxpro origin) anyway.