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.
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
Last edited: