We help IT Professionals succeed at work.

MS Access 2016 OLEDB connection to SQLEXPRESS

156 Views
Last Modified: 2019-09-15
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

Infotrakker Software
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
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
CERTIFIED EXPERT
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.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.