Connecting to Azure SQL database in Access 2010

Question

I've recently purchased a new computer and I had an OBDC connection set up to link to a sql database in azure through access. I am trying to set it up on my new computer but I'm unable to define the specific database. I'm able to make the connection but it only allows me into the master database. I've added my IP address in the configure servers but still can't get things to work. Any help would be greatly appreciated.

thanks

Lynn

Answer 1

Hello LynnConundrum,

I am trying to involve someone familiar with your question. We will get back to you shortly.

Thanks,
Syed Irfan Hussain

Answer 2

Hi,

The following guidelines apply to SQL Database connections using ODBC:
• When using SQL Server Native Client from SQL Server 2008 R2 or SQL Server 2008, the connection string must include the server name as part of the user name (user@server).

• You must use TCP/IP as the protocol when connecting to an Azure SQL Database.

• You cannot create a table in the master database, so therefore you must create a user database to create a table.

• You cannot execute a use database command to switch to your user database. You must disconnect and connect directly to the user database.

• You must have a primary key or clustered index on your table to be able to insert data.

Ref: https://msdn.microsoft.com/en-us/library/azure/hh974312.aspx?f=255&MSPPError=-2147217396

http://blogs.office.com/2010/06/07/access-2010-and-sql-azure/

https://support.microsoft.com/en-us/kb/2028911/

Hope this helps you.

Girish Prajwal