to link the tables in MS Access FE from SQL Server and then share FE for multi user environment

ODSCm

New Member
Joined
Dec 18, 2020
Messages
13
Office Version
  1. 2016
Platform
  1. Windows
Dear friends,

I am struggling for creating a ODBC data source and then link my table from SQL server. 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 not 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 define my username in security field of SQL Server
3- I have 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 Database which IT defined for me.

Now I need to link this table to my Front End Access. However; I do not know how should I did 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.

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 one should I choose
* 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 --> File Data Source , Machine Data Source. Which one should be choosen?

I will appreciated for detailed help
 
Last edited:

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

ranman256

Well-known Member
Joined
Jun 17, 2014
Messages
1,969
i use USER dsn.
i make the dsn's on the PC.
Then the FE app can grab data from the BE.

have all the BE tables linked BEFORE you deliver the FE. Then just give everyone a copy of the FE.

(link with MACHINE source)
 

Watch MrExcel Video

Forum statistics

Threads
1,127,505
Messages
5,625,199
Members
416,080
Latest member
blemon

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top