Different ways of connecting Access to Azure SQL Server (1 Viewer)

BiigJiim

Registered User.
Local time
Today, 01:59
Joined
Jun 7, 2012
Messages
114
Hi,

I am setting up a new application with an Access FE and an Azure SQL Server BE. In the past I have connected Access FEs to SQL Server BEs using either
  • ODBC linked tables with bound forms, or
  • unbound forms with VBA to open connections to populate unbound controls, update records etc.
However, I have never really understood the pros and cons of each. When is one more suitable than the other? For example, ODBC seems so much quicker to use when designing the FE, but I wonder if there is a performance disadvantage?

Can anyone please give some tips or point me in the right direction if there are any articles/guides covering this?

Thanks for reading and for any help,

Jim
 

Minty

AWF VIP
Local time
Today, 01:59
Joined
Jul 26, 2013
Messages
9,371
ODBC Linked bound forms with a restricted recordset would be my choice of attack.
Unbound forms do have their uses, but it gets hard in a multiuser environment to make sure edits aren't going to conflict.

I'm not sure I have seen any articles that provide decent advice on this tbh.
We use Azure a lot, and generally unless it's staging tables for imports etc. 99% of our forms are bound.
 

BiigJiim

Registered User.
Local time
Today, 01:59
Joined
Jun 7, 2012
Messages
114
ODBC Linked bound forms with a restricted recordset would be my choice of attack.
Unbound forms do have their uses, but it gets hard in a multiuser environment to make sure edits aren't going to conflict.

I'm not sure I have seen any articles that provide decent advice on this tbh.
We use Azure a lot, and generally unless it's staging tables for imports etc. 99% of our forms are bound.
Thanks Minty. I’m not too worried about write conflicts in this application, as records will be allocated to individual users, so only one use will be able to access a record at any one time.

Given the ease of ODBC though, I am wondering why it is ever more appropriate to use unbound forms.
 

Minty

AWF VIP
Local time
Today, 01:59
Joined
Jul 26, 2013
Messages
9,371
Given the ease of ODBC though, I am wondering why it is ever more appropriate to use unbound forms.

Sometimes it can make your life easier.

I have one particular application where I use a lot of small unbound popup forms to provide an update to underlying complex forms that can't easily be made editable. The pop up displays the underlying data, allows them to make changes that have to be saved via a save button.
This writes the data back to the table and the complicated form is then refreshed.

The end-user wanted it this way to prevent accidental updates from being made when someone simply viewed the underlying data.
I did suggest some alternatives, but they preferred this approach.

All of this can be achieved with a bound form, but when the form is updating more than one table it can get messy.
 

Isaac

Lifelong Learner
Local time
Yesterday, 17:59
Joined
Mar 14, 2017
Messages
6,678
Occasionally I find situations where the bound form is just overkill - and requires me to 'do' all kinds of little clean-up efforts in order to make sure that Access (which tries to save things CONSTANTLY and at the slightest provocation) isn't doing so (which confuses and frustrates the end user, who of course doesn't expect a record to be finalized until some visibly final indicator/moment). Whereas I can avoid that by simply not using a bound form and sending one simple update by using currentdb.execute (I'd still use an odbc table, so to me it's not "odbc bound" vs. "unbound", it's just "bound" vs. "unbound" - odbc having nothing to do with the discussion about bound vs. unbound................Although,,,yes, you're making it harder on yourself than needed for unbound by not even linking the table in the database. You're using the hardest possible unbound scenario).

Having said that, 90% bound forms - easier for larger data sets
 
Last edited:

BiigJiim

Registered User.
Local time
Today, 01:59
Joined
Jun 7, 2012
Messages
114
Thanks guys. Sounds like bound forms and ODBC linked tables is the way to go. I just remember the first SQL/Access application I picked up from a SQL developer was completely unbound forms, connections and not a linked table or pass-through query in sight! But it was a long time ago, maybe performance was more of an issue then.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 01:59
Joined
Feb 19, 2013
Messages
14,696
I go with bound forms most of the time but do bind them to disconnected recordsets when required or desirable rather than using an unbound form. Still need to undertake a specific update when required and still have to check for other user updates if the form is required to be editable.

the update process is similar to what used to be required for syncing replicated databases
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 20:59
Joined
Feb 19, 2002
Messages
36,874
I am wondering why it is ever more appropriate to use unbound forms.
When the BE is on your own local server, there is no reason to use unbound forms. Bound forms should use queries as their RecordSource and the query should select a single record for main forms. The old-fashioned way of binding a form to a table and using filtering to get to the row you want can be very slow when the BE is ODBC. Two important reasons for converting to a real RDBMS are to allow the server to do the heavy lifting with record selection AND to reduce network traffic by sending the minimum data required for the current operation. People who take an old Access app that has been working great for years with Jet/ACE are dumfounded after a straight convert to find out that their lively app is now slow as molasses. And so they pan Access and go to unbound forms. The better solution (because it uses the features of Access) is to create efficient forms. All my main forms have one or two search options and the RecordSource query uses those as criteria. This makes the forms open empty rather than to the "first" record. the user enters his criteria and presses tab if there is only a single search field or uses the button if there are multiple and the data he wants and ONLY that data is retrieved and loaded. When the search can be complex, I generally create a search form that give the user lots of options. The code runs a count query and if a single row is returned opens the single record edit form. Otherwise it opens a list form from which the user then chooses the record he wants to update.
search.PNG
 

Users who are viewing this thread

Top Bottom