Upsized Back End to SQL, does Caching need to be removed?

[greggsg]

I inherited an ACCESS application that has a Backend access database on the server and each use opens a Frontend Access database that is linked to the backend for doing their work. This has been running since before 2014. I have created the database in sql server and had to create some new fields for the primary fields so SQL server database can be cleared and updated as needed from access during testing. I found some of the commands in the VBA script were not compatable of SQL and wrote some stored procedures and call them instead of the Access specific manipulates in the VBA. I also wrote some triggers that replicate the access primary keys to keep auto numbers in sync and make sure that they are identified as indexed fields.

I have created automated linking process that will relink the FrontEnd Access application to the desired sql server (Dev, Test, Prod) via a combo box to make testing and deployment easier.

So the plan is that I will "compile" a new frontend for the users to use and it will point to SQL Server instead of server based backend access. today the current backend fails several times a month as it is over 2 gigs in size.

Now that I have provided background, my question is that the current setup creates a local cache when the application runs. I need to know if this local cache function should still be used when using a SQL backend or should it be removed. I would also like to know the pros and cons either way.

Thanks,

Gregg

 

[June7]

SQL is not a database, it is a language. I am guessing you meant SQLServer in your title and other references.

Have you searched for discussions on this topic?
https://stackoverflow.com/questions...nd-query-it-locally-or-have-the-app-query-the

 

[greggsg]

sorry, I know that SQL is a language and yes, I should have said SQL server. and I saw some other topics about forms being linked to tables and such. I have reviewed the link you provided. This discusses a C# application. Mine is an Access application that will now link to a sql server instead of the Access database for its tables. The current application caches from the existing access backend. My question is should I leave this part alone and let it continue to Cache or remove this function from the application and if so, why?

Thanks

 

[theDBguy]

sorry, I know that SQL is a language and yes, I should have said SQL server. and I saw some other topics about forms being linked to tables and such. I have reviewed the link you provided. This discusses a C# application. Mine is an Access application that will now link to a sql server instead of the Access database for its tables. The current application caches from the existing access backend. My question is should I leave this part alone and let it continue to Cache or remove this function from the application and if so, why?

Thanks

Hi. Welcome to AWF!

What do you mean exactly by the app creating a cache? Why does it need to do that? Depending on that reason, then it would be easier to say if it needs to continue doing it with an SQL Server backend.

 

[June7]

I expect the information applies regardless of what is used as GUI.

 

[arnelgp]

i think this is what he meant.

Just leave it as-is.

 

[greggsg]

Hi. Welcome to AWF!

What do you mean exactly by the app creating a cache? Why does it need to do that? Depending on that reason, then it would be easier to say if it needs to continue doing it with an SQL Server backend.

Thanks.

It looks like the original programmer has a routine to cache the table that contains contract data for accounts that have maintenance contracts. in the remarks it states to refresh the cache if desired contracts are missing, as they were created after the last cache was completed. It automatically comes up with a popup if the cache is over X days old asking if you want to reload it.

It basically creates a local hidden version of the contracts table to make local searching easier / more efficient.

My task on this project was to migrate the backend database to SQL server so that the current access application front end works while another development group who actually owns the project creates a web based application to access the data and run the application and then end of life the Access front end.

Since it looks like the cache serves a purpose and I am not sure what will break if I try and remove it, I think its best I leave it in place. I would be worried if it were writing to local cache, but it looks like its read only.

I found a date showing that some of the code in the application goes back to about 2009

 

[GPGeorge]

Thanks for clarifying what cache means in the context of this database application.

"It basically creates a local hidden version of the contracts table to make local searching easier / more efficient."

The cache was probably created to minimize performance problems. Given that, I'd leave it as is for the time being.

You don't indicate where the SQL Server database is located, but if it is remotely hosted, or if it is Azure SQL, then the cache will be a net positive in performance. The less data you have to move back and forth over an internet connection, the better.

If it's an on-site SQL Server, the impact may be lesser, but it still can't hurt to cache slowly changing data, such as contract data, in a local Access table. Local network traffic will be less impactful on performance, but not insignificant.

Based on my experience, I would take the estimated time frame for completion of that web application and double it to be safe. Your users will be using this Access/SQL Server solution longer than anyone now expects. I'd bet on that.

 

[tvanstiphout]

Based on my experience, I would take the estimated time frame for completion of that web application and double it to be safe. Your users will be using this Access/SQL Server solution longer than anyone now expects. I'd bet on that.

Amen to that. Do not fall in the trap of temporary solutions. They may be around for a long while. I agree: keep the local cache in place. It works. Don't mess with it.
The new-ish Cache settings in Options form are not for your scenario. Leave it alone.

 

[theDBguy]

Since it looks like the cache serves a purpose and I am not sure what will break if I try and remove it, I think its best I leave it in place. I would be worried if it were writing to local cache, but it looks like its read only.

Thanks for the additional information. I agree with the others, keep it in place for now. Good luck!

 

[greggsg]

Thanks Tom and George.

The SQL Server sits somewhere on the corp network, I have no idea where. Most of the users are remote and connected via VPN, like I am. Currently there are 15 users on the system and the Access data base is about 2.1 gigs after being compacted.

I agree what the development of the replacement front end will take a long time to complete, especially if SQL Server backend resolves the current speed issues as well as failures that occur during their current use of the application.

 

[The_Doc_Man]

For a while, one of my apps was on a rather bizarre network setup for the U.S. Navy, in which we had a shared BE on a server in Norfolk while we were in New Orleans. This shared BE was in a little corner of a disk hosting data from most of the USA East Coast region - more particularly, LANTFLEET, the Navy's Atlantic Ocean fleet headquarters. Performance was abysmal because of (a) distance and (b) number of users hitting that server.

I found several tables that were essentially lookup-only tables and copied them into the FE, then managed date of last update for each of the remote master copies and tested the date on the local copies - which I had to keep separately, of course. If the master copy had a newer update date then things would pause for a spot-refresh. Since these tables rarely, if ever, changed content, the refresh didn't happen that often.

That remote-to-local table change got me back over 30% improvement of the speed for some of the longer queries. Eventually we got assigned to a local server that had some security issues regarding encrypted e-mail, but that otherwise performed like a champ. I was able to reverse the local-copy tables with no performance penalties.

 

[Isaac]

Do not fall in the trap of temporary solutions. They may be around for a long while

What a good, succinct way of saying what I would have made into a long, wordy sermon if it were me.

+1

And this is why I hate it when websites allow the OP to rank/rate an Answer -
If I'm an OP, I'm the last person who is qualified to rate the Answer, b/c I'm the person who didn't know how to do it in the first place!
And often the OP will "prefer" the one which is either easiest to implement or whichever one they happen to try first.
Speaking of temporary solutions.

"Hey, it compiles. Ship it!"

 

[Pat Hartman]

"Hey, it compiles. Ship it!"

Back in the 80's I was cursed with having Julie "no test" (the name given her by the other team members) on my team. Her opinion was if it compiles, it must work. Of course, her stuff never worked

I'm going to guess that the original reason for the cache was because the form was bound to a table rather than to a query that selected 1 or a very small number of records. And the process of refreshing a million rows or whatever, made the form so slow, the programmer had to "do" something and settled on the wrong answer.

Now that you have converted the BE to SQL Server, it is verrrrrrrrrry important that you let the server do the heavy lifting. You do not want to fetch all the records in a table across the network, especially over a VPN which is slow, and then filter them locally.

Each form should have minimal filtering options and be able to select the ONE record the user wants to work with. In the cases where the selection got complicated, I made search forms which built SQL behind the scenes to filter the data and severely limit the rows fetched from the server.