Windows 11 
Access 2019
I have been trying to solve this issue for a few days. I'm hoping someone can confirm that I'm not bonkers...or maybe I am.
I am not in the IT department...I'm just part of the team who uses MS Access.
Project: Data migration from MS Access to onprem SQL test Server.
Goal: Scalability, Power BI, yada yada yada...all the goods.
I'm going to try to run through this the way it happened.
on Prem SQL Server
Test database created.
Server security set for Active Directory domain name\Security Group [domain\group]
Test db set for domain\group
GRANT SELECT UPDATE INSERT DELETE to [dbo.[table]] given to [domain\group]
MS Access
on a shared drive
we have to tell each other when we're in/out.
feeds into several excel spreadsheets that we can edit using Power Query
onprem SQL Server
Server security set for Active Directory domain name\Security Group [domain\group]
Test db set for domain\group
GRANT SELECT UPDATE INSERT DELETE to [dbo.[table]] given to [domain\group]
MS Access
connectin string set to allow windows authentication
copied db
new data source
create links to onprem SQL using DSN-less for Ms Access data entry direct into SQL.
save save save...all saved.
Open MS Access > some form
Create a record
save
SELECT [column with new info]
Totally worked!
EXCEPT...when my team tries to open that same MS Access file on the shared drive, or anywhere they copy it to, they get this error:
ODBC--connection to 'ODBC Driver 17 SQL Server [server]' failed.
I have asked IT support to fix the error and I've been told that they don't allow access to db. Right, but this is access to my data, on a test database, on a solution that by all accounts, is very typical.
I just wanted to test the DSN-less on a test server on a test db. What am I doing wrong that this isn't viable and why is my team not able to access? How should the ODBC--connection to 'ODBC DRiver 17 SQL Server [server]' failed be addressed? What about a service account? I am using a connection string.
Reply With Quote
