Questions about creating SQL Server BE and MS Access FE connection with Linked table.

[DTO.]

Dear friends,

I created a database and the tables in SQL Server 2012 to make them linked table in my Front End Access 2016. My application will be used in multi user environment.
I have VBA codes and macros in my Front End Access which controls the forms and bring data from other applications to form fields as well. My queries will remain also in my Front End MS Access.

At this point, after linking the table I have following questions below.



1- Where should I establish relationships between those two?
In MS Access or in SQL Server, or in both the same relationships?

2- I read something and took some main information about DAO and ADO connection types. However I did not understand which connection type I did? How can I understand it? Because I did not choose anything about ADO or DAO while doing linking table process between MS access and SQL Server?

3- After I linked the table, the linking address is like below;
ODBC;DSN=Nametext;Trusted_Connection = YES;APP = Microsoft Office .............................
However On the internet examples, I also saw the linking address like below
ODBC;DRIVER=SQL Server;SERVER=Localhost;Trusted_Connection = YES;APP = Microsoft Office .............................

So what is the difference? and Could we say one of them is proper way and the other not?

4- During the external database (SQL Server) table linking process to MS Access, I needed to choose some option like below to find my MS Access. And there were various options in the list like below, which one shoul be choosen? I choose the second one. But I have no idea what would be difference if I choosed others.
* Microsoft Access Jet Engine
* Microsoft Access Database Engine
* Microfoft Access OLEDB


5- I have some options while creating the SQL Server connection with my FE which are like below; which one should be choosen here? what is the difference?
SQL Server
SQL Server Native Client 11.0

Many thanks in advance.

[ranman256]

1. in FE, but I rarely use them.
2. dont bother with dao/ado, you dont need them with linked tables.
3. just link them
4. the sql doesnt care where access it, access looks for the sql tbls

[CJ_London]

don't agree re 1 -relationships are created in the back end, not the front end.
re 2 access default is DAO, ADO can only be used via VBA

re 5 - use the one recommended for your version of sql server - you may need to google to find out

re 4 - JET is the (very) old .mdb file

[Minty]

Agree with Ajax, Relationships can't be created on SQL linked tables in the Access FE (unless you use a pass through query) they have to be created in the BE.

Re 5 the driver, Definitely use SQL Server Native Client 11.0 rather than the older one. It is much quicker and more robust.

3. If you use a DSN the driver is specified in it, so it's 6 of one 1/2 dozen of the other, makes no odds.

[isladogs]

Both Ajax & Minty have given you excellent advice which I agree with completely

As both have already stated, relationships MUST be done in the BE.
See my article for more details about their purpose & how these are used: Relationships & Referential Integrity

[ArviLaanemets]

About point 3:
I have lately using datasource file instead of datasource. The file contains minimally 4 parameters for connection, is situated in some network share, and is linked to all front-ends (you have an option when creating the connection). This way you haven't manage datasources in computers of every user anymore, and whenever you need to make some changes (e.g. you move your database to different SQL server), you have to edit the datasource file, and in next session all users can connect the database without any problems.

NB! The datasource file is a plain text file - so never determine password there! I myself have domain group defined for every SQL database I made, and only users who are members of this group have access to this database.

About 'Driver=SQL Server' and 'Driver=Specific version':
Having general driver specified allows any valid user to get the connection to work. When you use specific version, the connection probably will work only when this version of driver is installed. This means, that when different users have different drivers, you have to tune every FE separately (or you have to create separate datasource files for every driver used in your network, and to link different FE's to different datasource files).

[Minty]

About 'Driver=SQL Server' and 'Driver=Specific version':
Having general driver specified allows any valid user to get the connection to work. When you use specific version, the connection probably will work only when this version of driver is installed. This means, that when different users have different drivers, you have to tune every FE separately (or you have to create separate datasource files for every driver used in your network, and to link different FE's to different datasource files).

I cannot get this to work when using an Azure server as a BE data source.

I have to specify a specific driver e.g. ODBC Driver 17 for SQL Server
It would be incredibly useful to not have to create a driver specific connection string.

[ArviLaanemets]

When I'm creating a new connection manually, I have 2 last in this list available for SQL server (an image added)
The one with version number may differ for different users. Do you have 2 drivers there, or only one?

A detailed description how I proceed - I create a text file which contains info like:
---------------------------------------------------
[ODBC]
DRIVER=SQL Server
DATABASE=SQLDatabaseName
APP=Microsoft® Windows® Operating System
Trusted_Connection=Yes
SERVER=SQLServerName\SQLServerInstance
---------------------------------------------------
, and save it as MyConnectionFileName.dsn to some shared network resource.

Now when creating the new connection for MS access FE, I select External Data > New Data Sources > From Other Sources > ODBC Database and then on Tab File Data Source I continue with 'Look in ..., locate my dsn-file, and select it. Now I have all tables/views in this database listed, and I can link any of them. I can also use several connection files to connect FE to several different databases (this was the reason I find out about this option, as I had a FE with one registered datasource, and 3 additional databases connections created directly in Access FE. When all BE databases were moved to another server, i searched for a way to edit those additional connections, stumbled on connection file mentioned somewhere, and the rest is history.)
I'm not sure abouth Azure though - does it have some specifics!?

When you need to create the connection for Excel, you can also use the connection file, but there is no point to do so, as Excel simply writes all this info from connection file into connection string, and doing so overwrites connection file path. So you can simply enter this info directly.

[Minty]

I have several saved File DSN's for different client databases.
I haven't tried changing the Driver description in the file, simply in the tdf.connection string.

We don't really want end user client's having to find and specify an ODBC source.
We do have routines for checking what drivers they have loaded and using them to create a machine specific connection string.
It will also prompt then with a download resource for the latest driver version, it's shame there isn't an automated method of doing this.

[isladogs]

Have you tried DSN-less connection strings. It doesn't solve the driver issue but it cuts out the need to create a file DSN

[Minty]

Have you tried DSN-less connection strings. It doesn't solve the driver issue but it cuts out the need to create a file DSN

Hi Colin, Yes that's what we use for all our remote SQL backends.

Most of our systems require IP Whitelist connectivity and/or Active Directory to the server for connections to work, so we simply save the EndUser account password (if required) with the connection string, as it doesn't pose any security threat.

The drivers are a pain though, different systems have used different drivers over the years, and that's why we save a Driver specific DSN file on our dev machines to ensure when we relink we use the right driver for the clients set up.
It saves passing a file to the end user as well.
If you could just use Driver="TheLatestDriverWotYouHave" it would be soooo much neater.

[DTO.]

I thanks to everybody for the valuable comments.

I am abit confused as I will do this process first time in my life. I want my users to reach smootly and directly to the BE SQL Server database wihen I distribute my FE to each of the people.
My users and me all connecting from same internet network.


I have two questions at that point.

1- In linking process should I choose windows authentication or SQL authentication? Will it affect the other users' access to BE?
1- While I was creating the connection from External Data part of Ribbon in MS Access, There is two tabs; machine data source and File data source. How should I proceed here?

I will be really glad with detailed guidance.

Thanks

[isladogs]

1. Use Windows authentication - using SQL authentication would require users to enter password details in order to proceed
2. Using Machine DSN would need to be done on each machine - not a good idea! Use File DSN so it only needs to be done once ... or IMHO better still use DSN-less connection strings

[DTO.]

1. Use Windows authentication - using SQL authentication would require users to enter password details in order to proceed
2. Using Machine DSN would need to be done on each machine - not a good idea! Use File DSN so it only needs to be done once ... or IMHO better still use DSN-less connection strings

Sir isladogs, thanks a lot for answer.

I have tested this so far with another user in another machine.
I have sent him dsn file and ms access. DSN File is located in the same address in his computer. When he click the linked table, it gave the message that SQL Server access failed. From what I think, it is because in SQL Server under security area, none user except me was defined and I did linking process with windows authentication as you offered. How can we solve this problem?

Now do every users have to be defined also inside the SQL server ?

[ArviLaanemets]

Let your system admin to create a domain user group for your DB users, and add all those users to this group. This group is used for this DB only;
In case you also need the access to this database (e.g. for testing or debugging, etc.), let admin add you to this group also, or to create a special group for you (an other IT staff when they also need the access). When a separate group is created, then this group may be used for other DB's too;
In SQL Server, create login(s) for this/those domain groups;
Allow access for defined login(s) to your DB. Set reading/writing rights for login(s) according what is needed for members of given group.