Providing Linked Table for MS Access FE from SQL Server BE (1 Viewer)

SORM2

New member
Local time
Today, 10:41
Joined
Jan 16, 2021
Messages
25
Hello Everyone,


I am struggling for creating a ODBC data source and then link my tables from SQL server.

(And please keep in mind that I will distribute FE MS Access with Email to all users after linking process has been done. So they need to be able to make data changes as linked with SQL Server - All users are working under same network). My IT department has no experience about using FE Access with BE SQL before. So they could not help me in this issue. And as I do not have experience as well, I could not direct them to make any request)

I will tell step by step what I did so far.

1- I have completed MS Access as a compact application (BE+FE) Therefore, it is now available to be used.
2- I requested my IT department of my company to enable me to reach SQL Server. They made it enabling me to connect through SQL management studio with Windows authentication. And they defined my username in security field of SQL Server.
3- I have also created all tables completely from the beginning with all indexing and PK features and then did their relations in SQL Sever as well under the specific Database which IT department defined for me.

Now I need to link those tables which I created in SQL Server to my Front End Access. However; I do not know how should I do it so that when I send my linked FE Access to my users, they can directly reach the BE without any operation. I will have 200-250 users totally. And all users have driver SQL Server in their ODBC Data sources. (I had SQL Server Native Client.11 - But I checked all other users driver they have only SQL Server - By the way IT provides for us SQL Server is 2012 - MS access is pro 2016 32 bit)

My direct questions to you now will be like below;
1- As I understood, I need to first create a data source which my Access can connect from external data place in ribbon. But which ODBC data source below should I create for my access? (I have added this as screenshot you can see)
* User DSN
* System DSN
* File DSN


2- When we come to MS Access again, from external Data tab in ribbon choosing ODBC database section gave me two option to choose as below;
* File Data Source
* Machine Data Source
Which one should be choosen? (I have added this as screenshot you can see)

I will appreciated for detailed help.
 

Attachments

  • Access Data Source Ribbon.png
    Access Data Source Ribbon.png
    130.5 KB · Views: 29
  • ODBC Data Source 32 bit.JPG
    ODBC Data Source 32 bit.JPG
    38.2 KB · Views: 66
Last edited:

Galaxiom

Super Moderator
Staff member
Local time
Today, 17:41
Joined
Jan 20, 2009
Messages
12,327
Use a FileDSN with the SQL Server driver. This will create a DSN-less connection in Access with the Connection String stored on each linked table.
 

SORM2

New member
Local time
Today, 10:41
Joined
Jan 16, 2021
Messages
25
Use a FileDSN with the SQL Server driver. This will create a DSN-less connection in Access with the Connection String stored on each linked table.
Thanks Sir Galaxiom. I have created file dsn. But I am connecting SQL Server Management Studio with windows authentication. So I tested today sending Access and dsn file to one user. Dsn file was located in the same address. then we opened the MS Access and clicked linked table. Warning pop up said that SQL Server access is failed. I think it is because user is not defined under SQL Server's security area. Would be the second alternative to get password to enter with SQL Authentication instead of Windows authentication ? So that every other user would directly connect my Login and password. If it is how I thought it will be working, then I will request IT department SQL Server authentication.
 

Minty

AWF VIP
Local time
Today, 08:41
Joined
Jul 26, 2013
Messages
8,600
I would recommend using Windows Auth if you can, as it removes the need to save the password in the access system.

You shouldn't need to send the file as the connection should be saved with the table def in the access file.
The file is only needed by you when you create a new linked to a table.
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 17:41
Joined
Jan 20, 2009
Messages
12,327
I have created file dsn. But I am connecting SQL Server Management Studio with windows authentication.
You are connecting to the SQL Engine Service. Management Studio also connects to the engine. MSSMS is actually a type of front end, just focused on managing the tables etc.
So I tested today sending Access and dsn file to one user. Dsn file was located in the same address.
The users does not need the DSN file. As long as you use the SQL Server ODBC driver to create the FileDSN you just need to use it to create the link in Access tables.
then we opened the MS Access and clicked linked table. Warning pop up said that SQL Server access is failed. I think it is because user is not defined under SQL Server's security area.
Yes. The user needs to have Connect permissions. You will need the help of your database administrator. Controlling user permissions can be one of the most complex aspects, especially when you get into Views and Stored Procedures and querying across multiple databases, or (God have mercy on your soul) across multiple servers. You are lucky to have an admin that is helpful. Most of them have a nervous breakdown when anyone mentions Access.:eek:

Much, much easier to use Windows Authentication. On a Windows Domain the users should be members of a Group. That Group is given permissions on the SQL Server and database. This way you can have different permissions for different groups of users and individual users don't need to be added to the database. SQL Server can give separate view, insert, update etc permissions as fine grained as on individual columns.

You are definitely doing the right thing using SQL Server. You don't really have much choice with so many users and it will take you longer but its power and versatility is unbelievable. You can start with just linked tables but so much more of the work can be done on the server itself. I've been working with it for many years and still learn new stuff all the time.
 

SORM2

New member
Local time
Today, 10:41
Joined
Jan 16, 2021
Messages
25
You are connecting to the SQL Engine Service. Management Studio also connects to the engine. MSSMS is actually a type of front end, just focused on managing the tables etc.

The users does not need the DSN file. As long as you use the SQL Server ODBC driver to create the FileDSN you just need to use it to create the link in Access tables.

Yes. The user needs to have Connect permissions. You will need the help of your database administrator. Controlling user permissions can be one of the most complex aspects, especially when you get into Views and Stored Procedures and querying across multiple databases, or (God have mercy on your soul) across multiple servers. You are lucky to have an admin that is helpful. Most of them have a nervous breakdown when anyone mentions Access.:eek:

Much, much easier to use Windows Authentication. On a Windows Domain the users should be members of a Group. That Group is given permissions on the SQL Server and database. This way you can have different permissions for different groups of users and individual users don't need to be added to the database. SQL Server can give separate view, insert, update etc permissions as fine grained as on individual columns.

You are definitely doing the right thing using SQL Server. You don't really have much choice with so many users and it will take you longer but its power and versatility is unbelievable. You can start with just linked tables but so much more of the work can be done on the server itself. I've been working with it for many years and still learn new stuff all the time.
Galaxiom also Minthy, I really thanks a lot. Now at least I know I am in the right direction and I can defend my argument confidently against IT guys.
But before that I need some more clarifications from you at some points.

Please confirm me if I understood this two different connection process properly.
First option - windows authentication: (If we dont consider windows group) if I choose this one, then I need to define every MS Access user inside SQL Server - under Security tabs.
Second option - SQL Server authentication: If I choose this one, then I will request my IT department to give me password and login informations. Then I will use them while I make linking table process through file dsn. Afterwards when I send MS Access FE to others, Users will be able to open and use the forms and enter the data wtihout writing any password for themselves again to provide connection.

If it is totally so like above as I understood, then why are we not using the second option because with that there will be no need to define 200-250 users in the SQL Server for IT department. They will just give me one login and password information. Isnt it like that?

As a last;

About windows domain - how are we creating a group and then defining this groups in SQL Server? Can I do this? or Should I just make a request to IT to do this? Because they are really not very helpful when it comes to MS Access as you said.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 03:41
Joined
Feb 19, 2002
Messages
32,221
Using a single SQL Server logon that everyone shares is really poor practice and your SQL Server people should object since it prevents them from knowing who is actually connected to the Server at any particular time. Managing many users is more trouble initially and you have to incorporate adding new SQL users when you add new application users but in the long run is the best course.

Regarding distribution, Either the database itself should be in charge of downloading a new version when one is available or you can use the batch file approach where the users open the app by using a shortcut. The shortcut runs a batch file that is stored on the server. The batch file makes a standard local directory (this handles new users automatically) and then downloads the latest version of the FE overwriting any existing version. This method gives the user a fresh copy of the FE each time he opens the app and is the method I use.

The SQL Server ODBC driver is very old and will not support any data types defined after ~ SQL Server 2005. So make sure your date fields are defined as DateTime rather than one of the newer data types and make sure that you don't have any bigints.

And finally, if you just substituted linking to SQL Server in an app designed to work with Jet/ACE, you will be unhappy with its performance since the app would not have been written using good client/server techniques.
 

Minty

AWF VIP
Local time
Today, 08:41
Joined
Jul 26, 2013
Messages
8,600
To add to what @Pat Hartman has written, if you have a sensibly set up domain then All the general users should easily be able to be added to a new group within the Domain - DatabaseUsers.

That group is then given permissions on the Server, and if you have any new staff they are simply added to the Group in the Domain and they will automatically have Permissions on the database. You could even have different database groups if you had more than one Database and wanted to have more granular control.
 

Users who are viewing this thread

Top Bottom