Solved Link to back-end BE tables only when required (VBA Code??)

[Nagesh]

Hi,

I need help to design a database, where in users from different locations input (populate) details into the table (duplicate) in the front end (FE) database. Eventually to append these details into the BE (backend) database.

Main reason is to improve the speed at which users can input details into the database without having to connect to the network drive. Connecting to BE database (even using windows explorer to ensure that network drive is connected) is observed to be very slow, especially in the current scenario where in many users are working from home.

I want to know whether user can link to tables in BE database at the end of the session, only to append details.

• Initial entry of data into database to be made easy (fast)
• To link to BE database through VBA code only to append data
• After data is appended to the table in BE database, the same can be deleted from the FE table
• To view reports, users can be made to connect to BE tables (can be slower)

Current design is with standard model with split database (tables in BE and forms/queries/reports in FE)
I am receiving many complaints from users about slow connection.

Is there any other way to resolve this?

 

[arnelgp]

how is the FE/Be communicate? through corporate network (same)?
have you considered using Persistent connection (google it) to the BE?

 

[CJ_London]

you can do as you want to do but there could be issues

1. be clear users are only entering new data, not amending existing
2. be clear that each users data entry does not cross over in any way with another user and does not need to reference existing data (other than static lookups)
3. when updating the server BE, primary keys and foreign keys many need to be changed

rather than include the BE tables in the FE, keep them in a separate local BE

In terms of improving performance, there are a number of things you should be doing - forms should not be based on a table or a query without criteria,may also apply to combos and listboxes if they have a large number of records. Instead they should have criteria to limit the number of records to be brought through. Note that using the where parameter of openform, or setting the form to data entry only does not reduce the number of records brought through - they merely apply a filter to the recordsource based on all the records brought through. Example. Say you have a form based on a table of 10000 records and on that form is a search combo based on the same table - that is 20,000 records to be brought through. But if you know the search starts with C applying that to a criteria in your form recordsource and search combo means very simply only 800 records are brought through.

And of course indexing is very important

If you are only doing data entry - try setting your form recordsource to something like

SELECT *
FROM myTable
WHERE False

no records will be returned so form should open quickly, might be a bit slow on the save, but worth trying

 

[Nagesh]

how is the FE/Be communicate? through corporate network (same)?
have you considered using Persistent connection (google it) to the BE?

Yes, BE is situated in company's server and FE is linked to BE through mapped networked drive.
Thanks for the suggestion. I will try using 'persistent connection' and revert back

@CJ_London

Will try your suggestion if 'persistent connection' does not work well.

 

[CJ_London]

depends how your app works, but you might find you already have a persistent connection - only time you would lose it is if user only has a form open that is not referencing the BE

 

[arnelgp]

persistent connection is not about a linked table is opened through form.
it is about the Backend database being Added to the the #Default Workspace# (workspace pool).

 

[CJ_London]

don't want to get into an argument, but one of the ways you can do it is by opening a form linked to a table, leaving the form hidden and otherwise not used.

 

[Pat Hartman]

If people are working from home, how are they connecting to the BE? If you are using a VPN, you will find Access to be painfully slow. Converting the BE to SQL Server will improve the performance but will almost certainly require changes to your forms. If you are not now using client/server techniques, you will need to modify your forms to be bound to a query and the query will need criteria to limit the rows returned significantly. One record on the main form and multiple related records on the subforms, works best. If you don't do this, you will possibly find that performance using SQL Server is worse than with a Jet/ACE BE. Keep in mind, a major point of switching to SQL Server is to let the server do the heavy lifting and if your forms are bound to tables or to queries without selection criteria, you are forcing the server to simply retrieve all rows and let Access manage them locally. It's a different way of thinking much like changing how you think when you move from Excel to Access although it is more intuitive.

There are two very good methods of sharing an Access app over a WAN and neither require any changes to the application.
1. Citrix
2. RDP (Remote Desktop)

Either can be run from a server on your WAN which allows both local LAN and remote WAN users to access the same BE or you can host using a third party service bureau but that would require ALL users to be WAN and possibly cause issues if the Access app needs to use Word or Excel automation because you would have to also move the files the app needs to the host provider.

As CJ suggested, your original request is not simple or straight forward. It is fraught with danger. I would convert the BE to SQL Server before I would try what you are suggesting UNLESS you really are ONLY going to insert new data. Once you get to updating, it becomes a nightmare to synchronize. But best is Citrix or RDP hosted by your IT department.

 

[CJ_London]

I've just uploaded a modified report I provided to a client which may give your some ideas (some already mentioned here). https://www.access-programmers.co.u...home-with-an-access-database-over-vpn.313634/

 

[Nagesh]

persistent connection is not about a linked table is opened through form.
it is about the Backend database being Added to the the #Default Workspace# (workspace pool).

Hi Arnelgp / CJ_London
I have now tried 'persistent connection' (form open hidden with connection to linked table) and it has improved the speed considerably.
I in process of getting feedback from other users and will revert soon.

 

[arnelgp]

Goodluck sir!

 

[Nagesh]

Goodluck sir!

Hi
Just got update from few 'work from home' users
Before using the persistent connection method, time taken to open a form : 20 to 25 seconds
After implementing 'persistent connection', time taken to open a form : 4 to 6 seconds

Thanks !!