I have an Access FE and SQLSE BE. We did nave it in the cloud for around 4 years, but currently, it is on a local server.
The dB was developed for us to have multiple clients, that would send us their data in a CSV file. We import the data, do the calculations and sent the reports back to the clients.
The Main table has about 1.5m records and the employee table has about 8,000 records.
I keep reading about DNS-less connection between the Access FE and the SQL BE. I watched the videos by Stephen Bishop and can now display records in a new form without using an ODBC connection.
But I want to move the queries from Access to the BE. Now, how do I use a SQL query/SP/View as the record source for a report? Or a form?
There are tons of videos/examples on how to connect Access/SQL for beginners, but I can't find any more advanced videos/dBs on HOW to accomplish this.
Where this is headed.... The Big Dog (the guy with the check book) wants to have the clients do all of the work (data entry and pulling their own reports).
At some point, the FE/BE would be moved back to the cloud. There would be a VM for SQL server and each client would have their own VM,
The dB was designed to have multiple client's data in the same dB - now I have to secure the data, so NO client can do anything to see any other client's data.
Would that be no linked tables or queries in the Access FE? And the FE made into an accde.
Thoughts, directions, warnings, questions, help??