I'm trying to pull data out of a Microsoft Access Database. The access database seems to be used by others, and isn't owned by me/my team. I'm purely taking data out, so the user I'm setting up shouldn't need write access, but I'm not sure how to set that in the Access connection string.
Integration Runtime on local machine is set up, when setting up the Access DB linked service, I'm using the below connection string:
Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=\\hostserver\filepath\filename.accdb;
I've tried appending "applicationintent=readonly","mode=read","admode=read" and a couple other variations but always getting an error saying "invalid connection string attribute 'mode'". And when not specified, I receive:
ERROR [HY000] [Microsoft][ODBC Microsoft Access Driver]General error Unable to open registry key Temporary (volatile) Ace DSN for process 0x3070 Thread 0x326c DBC 0xb81000b8 Jet'. ERROR [IM006] [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed ERROR [HY000] [Microsoft][ODBC Microsoft Access Driver]General error Unable to open registry key Temporary (volatile) Ace DSN for process 0x3070 Thread 0x326c DBC 0xb81000b8 Jet'. ERROR [HY000] [Microsoft][ODBC Microsoft Access Driver] The Microsoft Access database engine cannot open or write to the file '(unknown)'. It is already opened exclusively by another user, or you need permission to view and write its data.
I figured out how to connect properly, read-only wasn't the issue, and I never ended up figuring out exactly how to specify that. See this link if you're struggling to connect to an Access DB from Data Factory:
How to connect my DataFactory instance to an Access Database?