goldieretriever

<div>
I would use ODBC - can be created on the fly, for example for a table in Azure SQL: 
 

<pre><code class="code-10 nolinks" id="code-20-42730910-1">? LinkTable(&quot;yourazurehost.database.windows.net&quot;, &quot;databasename&quot;, &quot;userid&quot;, &quot;password&quot;)


' Link a table.
Public Function LinkTable( _
 ByVal Hostname As String, _
 ByVal Database As String, _
 ByVal Username As String, _
 ByVal PassWord As String)

 Dim db As DAO.Database
 Dim td As DAO.TableDef
 
 Set db = CurrentDb
 Set td = db.CreateTableDef()
 
 td.Connect = ConnectionString(Hostname, Database, Username, PassWord)
 
 td.SourceTableName = &quot;ServerTableName&quot;
 td.Name = &quot;LinkedTableName&quot;
 
 db.TableDefs.Append td

End Function


Public Function ConnectionString( _
 ByVal Hostname As String, _
 ByVal Database As String, _
 ByVal Username As String, _
 ByVal PassWord As String) _
 As String

' Create ODBC connection string from its variable elements.
' 2016-04-24. Cactus Data ApS, CPH.

 Const AzureDomain As String = &quot;.windows.net&quot;
 Const OdbcConnect As String = _
 &quot;ODBC;&quot; &amp; _
 &quot;DRIVER=SQL Server Native Client 11.0;&quot; &amp; _
 &quot;Description=Some description;&quot; &amp; _
 &quot;APP=Microsoft Access;&quot; &amp; _
 &quot;SERVER={0};&quot; &amp; _
 &quot;DATABASE={1};&quot; &amp; _
 &quot;UID={2};&quot; &amp; _
 &quot;PWD={3};&quot; &amp; _
 &quot;Trusted_Connection=No;&quot;
 
 Dim FullConnect As String
 
 If Right(Hostname, Len(AzureDomain)) = AzureDomain Then
 ' Azure SQL connection.
 ' Append servername to username.
 Username = Username &amp; &quot;@&quot; &amp; Split(Hostname)(0)
 End If
 FullConnect = OdbcConnect
 FullConnect = Replace(FullConnect, &quot;{0}&quot;, Hostname)
 FullConnect = Replace(FullConnect, &quot;{1}&quot;, Database)
 FullConnect = Replace(FullConnect, &quot;{2}&quot;, Username)
 FullConnect = Replace(FullConnect, &quot;{3}&quot;, PassWord)
 
 ConnectionString = FullConnect

End Function
</code></pre>
Of course, adjust &quot;DRIVER=SQL Server Native Client 11.0;&quot; to the driver you are using.
</div>

<div>
You might also want to download my <a href="https://www.experts-exchange.com/articles/32503/A-more-efficient-Linked-Table-Manager.html?headerLink=workspace_article" rel="ugc">Linked Table Manager for Access and SQL Server</a>It builds the DSN-less connections for you and also rebuilds the DSN-less connections for pass-through queries. 
 
Works in Access 2007-2019.
</div>

<div>
<div class="content wysiwyg-content">
I'm looking to see if there is a way to connect Access 2016 to SQL Express database using an OLE DB connection. 
What I need to do is 
&nbsp;- import data into the SQL Express tables 
&nbsp;- create a form to update the SQL data 
&nbsp;- create queries with calculated fields 
&nbsp;- write reports 
 
As of now I have the database created on my local C drive (C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\DATA\mydb.mdf) but in the future it will reside on the network with multiple users who will have access to the database and I don't want to have to create and ODBC connection.
</div>
</div>

I'm looking to see if there is a way to connect Access 2016 to SQL Express database using an OLE DB connection.
What I need to do is
 - import data into the SQL Express tables
 - create a form to update the SQL data
 - create queries with calculated fields
 - write reports

As of now I have the database created on my local C drive (C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\DATA\mydb.mdf) but in the future it will reside on the network with multiple users who will have access to the database and I don't want to have to create and ODBC connection.

MS Access 2016 OLEDB connection to SQLEXPRESS

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.

Microsoft Access

SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.