Wireless Access (1 Viewer)

[bob fitz]

How well would an Access frontend work with an SQL Express 2019 backend if the linking had to be done wirelessly?

I've developed many split apps but I've never had to use anything other than Access for both ends.
SQL Express would be a new adventure . Should it be fairly straightforward?

Any advice, tips, ideas, or comments would be very much appreciated.

 

[CJ_London]

makes no real difference, the principles are the same, Using express means if the wireless fails for some reason unlike access, express will not be left in an uncertain condition so significantly less risk of BE corruption.

I would investigate using passthrough queries and stored procedures for data processing to keep data processing on the backend which will be more efficient and opens up using TSQL which is a richer language than ACE sql.

And the usual rules apply to minimise network traffic (forms based on one or a few records, not whole tables etc). Main impact here is subforms - use criteria, not filters (which is what the linkchild/master fields do although you can still use them to autopopulate the FK). Also if certain recordsets such as for combos/listboxes don't change, consider opening and storing in memory when the app is opened, then just assign to the recordset when required.

You will have higher maintenance costs - indexes need to be rebuilt on a regular basis if there are frequent deletes which is more work than the ACE compact/repair action - you'll need to write your own code to rebuild the indexes on a table by table basis for those tables that are affected.

 

[Pat Hartman]

The wireless connection is not likely to cause corruption as it does with a Jet/ACE BE. There is also no need to use pass-through queries or unbound forms. The problem will be, if the connection drops, Access will not be able to reconnect gracefully so the user will need to shut down and reopen the app.

The big thing is how stable is the LAN. You won't know until you try it. Wireless LANs alsu tend to be slower than wired so the app will be slower than on a wired connection so you need to be using good client/server techniques. In order to make the server do the heavy lifting,, you need to bind your forms to queries with selection criteria. Unlike the "Access" technique of binding forms to tables and then using filtering, you want the query to do the "filtering" so you never bring those extra records over the LAN. This is extremely important with a wireless LAN.

Forms that have simple criteria use combos or textboxes and the criteria references those so that forms open "empty". The user selects from the combo or enters the search string and the form is required selecting the ONE record.