Hello everyone, I'm seeking guidance on establishing a dynamic connection within Access 2021. Our goal is to retrieve tables from SQL Server. We're looking to create a system where clients input their company code (Master DB) to link to their respective databases (Client's DB). Following this, they'll provide their login credentials. Any insights or methods to achieve this seamless dynamic connection would be greatly appreciated.
Dynamic connection Access 2021 with SQL Server using code (1 Viewer)
- Thread starter wardah
- Start date
- Local time
- Today, 17:17
- Joined
- Feb 19, 2013
- Messages
- 16,614
Need to be clearer about the actual requirements if you need anything more that basic advice
Who’s sql server? Yours or theirs? Where is it located? On a server? On the web? How are you distributing the front end? Is the front end a .accdb or .accde? How are front end updates being handled?
there are a number of threads on linking to sql server - here is a recent one
Who’s sql server? Yours or theirs? Where is it located? On a server? On the web? How are you distributing the front end? Is the front end a .accdb or .accde? How are front end updates being handled?
there are a number of threads on linking to sql server - here is a recent one
Create DSN-less connection to SQL Server
I am referring to Method 1 in Create DSN-less connection to SQL for linked table - Microsoft 365 Apps | Microsoft Learn The beginning of the module is as follows '//Name : AttachDSNLessTable '//Purpose : Create a linked table to SQL Server without using a DSN '//Parameters '//...
www.access-programmers.co.uk
Our SQL Server resides on a cloud server, primarily dedicated to managing our collective data. The distribution of the front end involves using an .accdb file. To ensure consistency and functionality, we're exploring various methods to handle front-end updates efficiently. Any suggestions or best practices in managing front-end updates within this context would be valuable.
To establish a connection with the client's database, we utilize information stored in the master database. Based on the company code provided by the client, the respective client database is connected.
To establish a connection with the client's database, we utilize information stored in the master database. Based on the company code provided by the client, the respective client database is connected.
Gasman
Enthusiastic Amateur
- Local time
- Today, 17:17
- Joined
- Sep 21, 2011
- Messages
- 14,308
We used to use something similar in my last place of work. They had a lot of clients and each could only see their data.
Now whether that was all in one BE or in multiples, I do not know.
I suspect all in one, as I used to point out bugs/make requests. To have to copy that multiple times would be a pain, but here I am only surmising.
I believe it was hosted on a windows server and we just connected via a browser, so everything was on the server?
Now whether that was all in one BE or in multiples, I do not know.
I suspect all in one, as I used to point out bugs/make requests. To have to copy that multiple times would be a pain, but here I am only surmising.
I believe it was hosted on a windows server and we just connected via a browser, so everything was on the server?
- Local time
- Today, 12:17
- Joined
- Feb 19, 2002
- Messages
- 43,275
1. If the data is sensitive in any way, commingling it is dangerous and poor practice.
2. The FE and BE each need version tables.
3. When the FE opens, it compares the two version tables. If they are not compatible, then the app should refuse to open.
4. How you distribute the FE depends on whether the users are on the same LAN or are distributed. If they are on the same LAN, the simplest solution is to create a batch file that copies the FE from the master folder on the server to the local PC and then opens it. You create a shortcut to run the batch file. This method gives each user a fresh copy of the FE every time they open the app. If the users are distributed, you can use an FTP site from which they can download the updated FE. You have to create the procedure to do this for them as part of the batch file. Depending on what kind of FTP software you are using, it could be as simple as using a mapped drive.
2. The FE and BE each need version tables.
3. When the FE opens, it compares the two version tables. If they are not compatible, then the app should refuse to open.
4. How you distribute the FE depends on whether the users are on the same LAN or are distributed. If they are on the same LAN, the simplest solution is to create a batch file that copies the FE from the master folder on the server to the local PC and then opens it. You create a shortcut to run the batch file. This method gives each user a fresh copy of the FE every time they open the app. If the users are distributed, you can use an FTP site from which they can download the updated FE. You have to create the procedure to do this for them as part of the batch file. Depending on what kind of FTP software you are using, it could be as simple as using a mapped drive.
