Access error after changing primary key data type

Posted on 2013-10-18
Medium Priority
Last Modified: 2013-10-25
I changed an autonumber (int) to bigint

I now get an error on this line: -
Set Rst = Dbs.OpenRecordset(Sql, dbOpenDynaset, dbSeeChanges)

"Data type missmatch"

Does anyone know how to solve?
Question by:HKFuey
  • 3
  • 2

Expert Comment

ID: 39582238
Are you passing any variables that work with the SQL variable?  It sounds like you have a variable declaration that is INT and you are now attempting to assign a BIGINT.

It could also be the actual SQL query with joins that is causing the issue.  I would recommend taking a look at the query that is being used by the SQL variable.


Author Comment

ID: 39582293
Hi Tom,
The code was working until I removed the autonumber and changed int to bigint on the Key field.

I had a look at the table in access and just get lots of #Deleted, I tried changing from bigint to int then relinked the table and still got lots of #Deleted

Author Comment

ID: 39582342
OK I'm sorted.

I restored the database, removed the Autonumber without changing the datatype and it is now OK.

Accepted Solution

ButlerTechnology earned 1500 total points
ID: 39586604
I believe that BigInt is treated as character data when it is link to MS Access.

Author Closing Comment

ID: 39599778
Change back to int sorted it

Featured Post

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

When we develop an application in Ms Access 2016 we should also try to protect the queries, macros and table links. I know I may not have a permanent solution but for novice users, they will not manage to break your application. Below is the detail …
What to do if a split doesn't fit? Or a bunch of invoice lines must be rounded while the sum must match a total? It takes a little, but - when done - it is extremely easy to implement.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question