This article expect you know how to create a Form with a button in Microsoft Access. Like it or not they are still many Microsoft Access users out there today, like a group that I am teaching today. Unfortunately C# has never come into play on Microsoft Office VBA. VBA is still pretty much of VB6 and not very VB.NET alike. People still looking on how to read data from Microsoft SQL Server. I have tested the following code in MS Access 2016 to access SQL Server 2014 as well as Microsoft Access 365 to access SQL Server 2016, it works.
Private Sub Command1_Click()
Dim con As New ADODB.Connection
Dim strSQL As String
Dim rstNwind As New ADODB.Recordset
con.ConnectionString = “Provider=’sqloledb’;Data Source=’TRAINER\SQLEXPRESS’;” & _
“Initial Catalog=’NorthWind’;Integrated Security=’SSPI’;”
strSQL = “SELECT * FROM Customers”
Set .ActiveConnection = con
.CursorType = adOpenKeyset
.CursorLocation = adUseClient
.LockType = adLockOptimistic
Set Me.Recordset = rstNwind
Me.txtCompany.ControlSource = “CompanyName”
Me.txtContact.ControlSource = “ContactName”
Also don’t forget to set the reference to ADO Library.