Ian Wallace
asked on
Linking MS Access To Azure SQL Tables
Hi,
I have been using MS Access with MS SQL for years and have a robust routine in place for linking tables when the MS Access database is opened (It needs to do this as the user can run the database in live or test mode which connect to different SQL databases).
I've recently picked up a project where the client want to use Azure SQL as the data source but when I try to connect I get the message Run-time error '3170': Could not find installable ISAM.
I've tried several methods of linking the table and get the same message every time - apart from one - If I create an ODBC connection to the Azure SQL database and then link through the ODBC it works, but asks me for the user name and password every time I want to do anything with it.
I have to use SQL authentication as the majority of the users will be running the Access database on their home PCs so don't have Active Directory user accounts.
Any help would be appreciated!
I have been using MS Access with MS SQL for years and have a robust routine in place for linking tables when the MS Access database is opened (It needs to do this as the user can run the database in live or test mode which connect to different SQL databases).
I've recently picked up a project where the client want to use Azure SQL as the data source but when I try to connect I get the message Run-time error '3170': Could not find installable ISAM.
I've tried several methods of linking the table and get the same message every time - apart from one - If I create an ODBC connection to the Azure SQL database and then link through the ODBC it works, but asks me for the user name and password every time I want to do anything with it.
I have to use SQL authentication as the majority of the users will be running the Access database on their home PCs so don't have Active Directory user accounts.
Any help would be appreciated!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi John,
Thanks for your response, the link you provided definitely worked and I now have the table linked but I now have another issue.
I can add data to my linked table but when I try to edit the data, I get the following message;
Any ideas?
Thanks.
https://www.experts-exchange.com/questions/29215266/Linking-MS-Access-To-Azure-SQL-Tables.html#a43284853 https://www.experts-exchange.com/questions/29215266/Linking-MS-Access-To-Azure-SQL-Tables.html#a43284853
Thanks for your response, the link you provided definitely worked and I now have the table linked but I now have another issue.
I can add data to my linked table but when I try to edit the data, I get the following message;
Any ideas?Thanks.
https://www.experts-exchange.com/questions/29215266/Linking-MS-Access-To-Azure-SQL-Tables.html#a43284853 https://www.experts-exchange.com/questions/29215266/Linking-MS-Access-To-Azure-SQL-Tables.html#a43284853
You need to make sure the linked table contains a primary key, and I always add a Timestamp (rowversion) column to every table. This is used by SQL Server to help with write conflicts.
This should help with the occurrence of the above error.
This should help with the occurrence of the above error.
ASKER
The table has a primary key defined.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Sorted - thanks for your help gents!!
Here you can see how you can avoid saving the password with the linked table:
https://www.microsoft.com/en-us/microsoft-365/blog/2011/04/08/power-tip-improve-the-security-of-database-connections/