MS Access 2016 OLEDB connection to SQLEXPRESS

goldieretriever
goldieretriever used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014
Commented:
You'd be better off using DSNLess connections instead of creating an ODBC connection. When you deploy your database to your users, you would (a) connect to the new database, using DSNless connections and then (c) create copies of the database and deploy those.

Here's a few articlea about creating DSNLess connections:

https://support.microsoft.com/en-us/help/892490/how-to-create-a-dsn-less-connection-to-sql-server-for-linked-tables-in
http://www.accessmvp.com/djsteele/DSNLessLinks.html

Note that you must properly create the connection string. Here's a good resource for doing that:

https://www.connectionstrings.com/
Gustav BrockCIO
Most Valuable Expert 2015
Distinguished Expert 2018

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

? LinkTable("yourazurehost.database.windows.net", "databasename", "userid", "password")


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

End Function

Open in new window

Of course, adjust "DRIVER=SQL Server Native Client 11.0;" to the driver you are using.
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
You might also want to download my Linked Table Manager for Access and SQL ServerIt builds the DSN-less connections for you and also rebuilds the DSN-less connections for pass-through queries.

Works in Access 2007-2019.

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