asked on

MS Access 2016 OLEDB connection to SQLEXPRESS

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.

ASKER CERTIFIED SOLUTION

Scott McDaniel (EE MVE )

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Gustav Brock

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 Fye

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.