Access Solution Inquiry

[JeRz]

Hi All,



I have inherited an Access 2010 database that is used to track customer service inquiries. It is primarily used by only 2 people. Previous to this, an Excel spreadsheet was used. Also, the database is split with the back-end being housed currently on a network shared folder. The main issue is that the connection of both individuals is via VPN and will always be this way. They work off site at a customer's location and are never in the office. This is obviously causing slowness. My biggest concern is the possibility of corruption.

I haven't been able to look into what options are available as a spreadsheet for this amount of data, and, coupled with the possibility of data entry loss doesn't seem feasible. I am not sure that I will have access to SQL or a SharePoint to house this. Providing I do not, can anyone offer up any suggestions? Providing I can gain access to a SQL server, I am not versed in it at all. Can I still use the front end Access interface while the back-end is housed on a SQL server?

I apologize if this is not in the correct place. I wasn't sure where to place it.Please let me know if any additional details are needed. Thank you all for any advice you can offer!

[ItsMe]

Yes, you can remotely connect to SQL server via Access. However, Access was not built to do so. In order for it to work, you need to carefully design your queries and connections. Dropped connections and failed updates need to be closely monitored.

[NTC]

For a VPN connection approach both the front end file (FE) and the common back end data file (BE) sit in the local LAN/server so that the speed between them is fast. The distance (due to the WAN) is between the FE and the user's screen. Assuming this is your set up, and as long as there is a separate front end for each user - OR - you are absolutely certain they both will not attempt to use the same single FE at the same time; then there will be no corruption risk. The slowness is purely an image refresh issue and while I am sure annoying it is not due to Access per se. One should experience the same screen refresh time with any Office application such as Word or Excel. In which case you want to explore options with your VPN provider on speeding things up.

If however the FE is sitting on the user's PC itself - and the WAN is between the FE & BE - then you should change the configuration as this is not supported in Access 2010.

[ItsMe]

Also, if you are moving to SQL server, then you can take advantage of Stored Procedures, Triggers, Pass-Through queries etc. You do not necessarily have to make your Users suffer if they can manage certain processes with a snapshot of the data.

[CJ_London]

Can I still use the front end Access interface while the back-end is housed on a SQL server?

yes - access is often used that way, however you may need to make changes to the way it handles data - e.g. use sql server stored procedures

using sql server does not guarantee a performance improvement since they are still connecting via VPN. SharePoint may be quicker - depends if the users have to connect to the internet via vpn to gain access (in which case no real improvement) or can connect directly via the internet. Your IT dept should be able to advise.

The best option is to use citrix or terminal server. both backend and front end are on the server (each user still has their own copy of the front end) so all the data handling is dealt with on the server, all that is transmitted across the network is basically screen refreshes back to the user. Many of my clients are set up this way.

You can also tune your existing front end to minimise the amount of data transferred across the network. Most common is forms which just have unfiltered data as a recordsource with all the searching done by filtering.

Instead do not create the recordsource until the user has specified some criteria, then specify the recordsource with criteria.

And make sure all fields regularly used in joins and criteria are indexed - with the exception of fields which contain predominantly nulls or only 2 or 3 potential values (such as yes no) since indexing will not be effective in these cases.

Also avoid the initial * when using 'like' for string comparisons - by using it, you prevent access from using indexes - so instead of

me.filter="clientname like '*" & txtSearch & "'*"
me.filteron=true

use a default recordsource of
SELECT * FROM myTable WHERE true=false

which won't return any records, but will prevent #Name appearing in the controls.

and instead of me.filter=.... use
me.recordsource="SELECT * FROM myTable WHERE clientname like '" & TxtSearch & "'*"

So instead of transfer say 10k records across the network to populate a form, you are only transferring a few

Most times, users will know the name or at least how it starts - if they are looking for 'Smiths Ltd', they are not going to enter 'mit', they'll enter 'smi'. If they don't know how the name starts, then train them to enter a * first - '*mit' - and/or include a controltip.

Also avoid using domain functions (Dlookup, etc)

Look at how websites work - they are very parsimonious with transferring data to the webpage

[JeRz]

Thank you everyone!