MS Access forms bound to SQL Server tables accessed over ADO ?

Bernard Thouin
Bernard Thouin used Ask the Experts™
on
Hi

Is it possible to replace:
  • an Access form bound to a linked table residing in SQL Server, i.e. using implicitly DAO
  • by an Access form bound to a table using an ADO connection
without having to write a lot of VBA code for:
  • loading the data in the form fields
  • updating fields of existing rows
  • Inserting new rows
  • deleting existing rows
?

I have only:
  • a large experience in using linked tables and ADO in Access
  • experience in using VB.Net with corresponding forms and datasets and datatables

Thanks for help
Bernard
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Top Expert 2014
Commented:
You can use the Recordset method of a form:

Dim rst As New ADODB.Recordset
rst.Open "SELECT * FROM SomeTable", YourConnection

Me.Recordset = rst

You'd also need to set a few other properties, like adUseClient, in order to edit. See this article:

https://docs.microsoft.com/en-us/office/vba/access/concepts/activex-data-objects/bind-a-form-to-an-ado-recordset
Bernard ThouinIT Analyst and developer

Author

Commented:
Thanks Scott, that solves my problems :)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial