Basically you can take a bespoke approach or take more time and go generic.
The steps are simple but note that if you want to display the data grid control with multiple rows etc. you might want to write the rows to an access table local to the front end.
- create a stored procedure to return the data you want to display on the form and assign appropriate permissions (either return output parameters or a row set as you prefer).
- build the form without a data source.
- open a record set, in the forms vba module, against the stored procedure created above. See https://support.microsoft.com/en-us/help/168336/how-to-open-ado-connection-and-recordset-objects
(use a client side cursor in the recordset if you want to be able to move freely back and forth through the rows).
This example shows you how to connect the record set to the server and then read the stored procedure. If you are displaying one record at a time you could even use a simple ado command and return the data as output parameters from the stored procedure instead of rows.
- Populate the form by
— check record set has row(S)
— if yes then write the field values in the rs to the forms text boxes etc. Or if using a data grid write them to the local table. See recordset methods and properties rs.movefirst, rs.movelast, rs.eof, rs.close, rs(“fieldName”), rs.fields etc.
— close the record set.
- save the updated data back to sql server
Important: binding forms sucks totally for performance and is buggy/confusing as hell in respect to handling data change events etc. Not wanting to sound controversial but, in my experience, only people not comfortable writing code would argue for binding to remote data sources. It may appear confusing but once you get your head around it it’s easy as you like.
Also Important: you will be working in a disconnected state, drop the record set completely (too many open connections etc. can slow things down), when the user saves the record you will need to write the data changes back to sql server with vba. I tend to do this by executing stored procedures with input parameters by passing the inputs as ado parameters in an ado command object (note that you need to create a reference to the ado library in the Forms code module, and that the full library prefix is adodb. E.g. adodb.recordset, adodb.command, adodb.connection).
That also means handling data validation at the frontend.
There are loads of examples on google and if you’re new to frontend code, vba etc., once you’ve nailed it you’ll find it extremely easy.
FYI I worked somewhere for years where we had the resource to test every approach, this is indeed the best in respect to performance and stability.... However, this process description is relatively high level, there are many ways to achieve a good end result using the above. Note also that you can reproduce a data grid in an access form using rows of text boxes etc. But, the data grid, when bound to a local table provide much more functionality in respect to sorting, filtering, changing column widths etc.
Have fun ;o)