Access to SQL server from Microsoft Access application

We have an Access applikation located on a share which uses a filedsn to get access to our SQL server.
We would like users to use their own credentials when accessing the SQL server.
How can we achieve this without the need of creating ODBC's on every user PC?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
A file DSN does not prohibit the use of the credentials of the user.

But first of all: What do you mean with "their own credentials"? Windows Authentication or SQL Server Authentication?

Then, using one shared Access front-end is a bad practice, cause it may lead to increased corrupted front-end database files. Each user should have its own, local copy of the front-end.

And last but not least: Use DSN-less connections. See
Dale FyeOwner, Developing Solutions LLCCommented:
I prefer to use DSN-less connections.

No points please.
ColoplastAuthor Commented:
Sorry, "Users own credentials" yes that is Windows authentication.
I will try this DSN-less connection, but it's 100% clear for me where to enter this?
Can you clarify?
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<Sorry, "Users own credentials" yes that is Windows authentication.>>

 That's a setting in SQL Server.   You have two choices for security with SQL: SQL Logins or Trusted Connections.

 You want the latter, which means SQL will assume that since they are logged into Windows, it should trust the connection.

 This is different than a DSN or DSN-Less, of which you can use either.

With a DSN-Less connection, all that says is that you've supplied all the information in the connection string and don't need a DSN to complete the connection.

 If you are using SQL Logins, then that means a username and password must be setup in SQL Server and supplied as part of the connection string (either via a DSN or without) in order to get a connection.

ste5anSenior DeveloperCommented:

Public Function TableLinkSqlAsTable(ASourceSchema As String, ASourceName As String, ADestinationName As String, APrimaryKeyFields As String) As Boolean
  On Local Error GoTo LocalError
  Const CONNECTION_STRING As String = "ODBC;Driver={SQL Server Native Client 11.0};Server=ServerNameOrIP;Database=yourDatabaseName;Trusted_Connection=yes;"
  Dim ix As DAO.Index
  Dim td As DAO.TableDef
  Dim Count As Integer
  TableLinkSqlAsTable = False
  ASourceName = ASourceName
  If ADestinationName = "" Then
    ADestinationName = ASourceName
    ADestinationName = ADestinationName
  End If
  QueryDelete ADestinationName
  TableDelete ADestinationName
  Set td = CurrentDbC.CreateTableDef(ADestinationName)
  td.SourceTableName = ASourceSchema & "." & ASourceName
  CurrentDbC.TableDefs.Append td
  If APrimaryKeyFields <> "" Then
    For Each ix In td.Indexes
      If ix.Primary Then
        CurrentDb.Execute "DROP INDEX " & ix.Name & " ON " &  ADestinationName & ";"
        Exit For
      End If
    Next ix
    CurrentDb.Execute "CREATE INDEX PK_" & ADestinationName & " ON " & ADestinationName & " ( " & APrimaryKeyFields & " ) WITH PRIMARY;"
  End If
  Set td = Nothing
  TableLinkSqlAsTable = True
  Exit Function
  DebugError "TableLinkSqlAsTable", ASourceName
End Function

Open in new window

{SQL Server Native Client 11.0} must  match the installed SQL Server driver. Use odbcad32 and the driver page to get the correct version.

QueryDelete ADestinationName and TableDelete ADestinationName are helper methods to delete existing objects of the same name, otherwise the linked table could not be created.

You use it as TableLinkSqlAsTable "dbo", "myTable", "LinkedTableName", "" or TableLinkSqlAsTable "dbo", "myView", "LinkedViewName", PKCol1, PKCol2".

The primary key fields parameter is to tell ACE what columns form the primary key, when linking views as tables. Cause ACE needs this information to allow updates on views.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.