Issue Connecting Access 2003 to SQL Azure file dsn

[Lightwave]

Have tried to set up a link from MS Access 2003 to SQL Azure as per FMS Luke Chung directions

http://www.fmsinc.com/microsoftaccess/cloud/link-to-azure-sql-database.html

When I run the link everything seems to work ok - I see the database I see the tables and I can link to the tables- I even get the right number of records back.

When I open them however I just get the following

#Deleted in every field value of every column (but the right number of rows)

Anyone got an idea of what might be wrong. Looks like I am almost there.

 

[HiTechCoach]

You may be running into comparability issues.

Access 2003 is most compatible with SQL Server 2000 and older.

When you create the SQL database on Azure you will need to be sure that you only use the data types that were support in SQL Server 2000.

For example, the datatype Big Int is not support in Access 2003.

 

[CJ_London]

see this link

https://blogs.office.com/2017/03/06/new-in-access-2016-large-number-bigint-support/

you may have to upgrade to 2016 if you cannot change the bigint datatype in azure - it is primarily due to indexing, so check your primary/autonumber fields in particular.

 

[Lightwave]

Thanks guys obvious when you say it all my tables have bigint Primary Key

 

[HiTechCoach]

Thanks guys obvious when you say it all my tables have bigint Primary Key

Change then to INT to be compatible with Access.

 

[Lightwave]

Just to let readers know - changing the primary key to Int rather than Big Int in SQL server solved this issue. Obviously you want to know about that ideally when you start to design your SQL Server database!