question

JerryBarrett-6482 avatar image
0 Votes"
JerryBarrett-6482 asked SadiqhAhmed-MSFT edited

MS Access Table Link Issue after SQL db Migration to AZURE

Hi

I have an Access App that used linked tables through odbc from a local SQL Express Server, which works great :)

The goal is to provide this app with data in the cloud so that users can be somewhat mobile.

I eventually got my trial AZURE SQL database setup and after some ups and downs managed to migrate the database from the local SQL Express Server to my AZURE SQL Database with the AZURE Migration Assistant App. Happy Days :)

In the Access App I changed the ODBC connection string to match that of the AZURE ODBC and the Access App reports an active connection, more happy days. :)

However, things stop working there. the DSNLess conection to the linked tables fail, which previously worked fine to the SQL Express Server.

In the Access App I tried a manual external data link to the AZURE db and successfully got the tables list from AZURE, but my tables are missing from the Link Tables list. While I can see them and run queries on the on AZURE and AZURE Data Centre. they are not visible to Access App External Data list of tables.

Im so close to this working, if anyone has any clues to help over this last hurdle Id be appreciative :)

Many Thanks
Jerry

azure-sql-databasesql-server-migration
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

JooLucasFAndreatta-4135 avatar image
0 Votes"
JooLucasFAndreatta-4135 answered JooLucasFAndreatta-4135 commented

You need s add service Migration. On your table SQL.

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi and thank you for responding, I don't know what your referring to with the service Migration but I have managed to figure out the problem.

The user account AZURE sets you up with allows you to see the master set of tables, but doesn't allow you to view the tables in the target database it creates during setup.

By adding a new user and adding some permissions on the database I want to connect to with Access appears to have worked, I just needed to adjust he ODBC login details to the new user and voila the tables appeared in the MS Access Link Table - Happy Days.

Thank you
Jerry

0 Votes 0 ·

thank you. have a great day!

0 Votes 0 ·

For you too, successeful!

0 Votes 0 ·