How to solve #Delete records when linking BIGINT MS SQL Primary Key in MS Access?

In MS Access, you will definitely encounter a #Deleted record tags in your MS SQL linked table when the table contains BigInt primary key as shown in the image below:

b1

To solve the problem, you need to create a view and cast the BigInt primary key to a VarChar then link the view and not the table as shown in the image below.

b2

Now link the view:

b3

Thats it!

If you find this blog helpful, you can make the donation by clicking the Paypal button below.

Advertisements
About

Software developer living in the Philippines.

Posted in Microsoft Access
3 comments on “How to solve #Delete records when linking BIGINT MS SQL Primary Key in MS Access?
  1. Simon P says:

    The above works but if you then use the view as one of several tables in an Access query it will not let you update or change any information in the “non view” tables. So the whole query becomes view only.
    My problem was the external SQL table is from some third party software that has used the bigint as the primary key, I appear to have got around this by creating an additional field in the table (the software allows user defined fields) and this field is a copy of the primary key field but in Varchar, removing the primary key indexing, then linking Access to the table where there is no primary key so that Access asks you to define which field is unique and I specify the new field and then once it is linked in Access reinstating the Primary Key on the original field. While the ODBC driver thinks it knows which is the primary key it works however do not relink or refresh the link as you have to do it all again.

  2. Juan Carlos says:

    Gracias Harold Glenn , este fue el unico post que encontre relacionado al tema de access. en mi caso tuve q cambiar los valores de la tablas de bigint a int, pues todavia estaba en la etapa de pruebas đŸ™‚ . gracias

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Author

Harold Glenn P. Minerva
Software Developer / Tech Enthusiast
Living in the Philippines

View Harold Glenn Minerva's profile on LinkedIn

Instagram

Software Engineer - Seasonal and Range Trading Software. Magenta Trader is a powerful stock market visualization software that increases your probability of trading success.

Software Architect and Founder - Easyfis.com is a multi-tenant cloud-based Software-as-a-Service (SaaS) business app that caters to micro, small and medium trading businesses.

CTO and Co-Founder - We give your company the leverage by providing innovative software solutions products such as Point-of-Sales (POS), Financial Information System (FMIS), Payroll and DTR (HRIS), and many more.

%d bloggers like this: