Update SQL table with bigint field from VBA (MS Access)

Question

enter code hereI have a table on SQL server 2005 with bigint primary key and MS Access 2003 front end (linked tables).

I want to update the record for the table that has bigint as primarykey. I use

CurentDb.execute _
    "Update myTable SET Field1 =1 , Field2 = 'test' WHERE bigintKey = " & myVar

I get Data mismatch in criteria expression.

How I can update a record on MS SQL database that has bigint field from MS Access?

EDIT: It doesn't give me an error when I do

CurentDb.execute _
    "Update myTable SET Field1 =1 , Field2 = 'test' WHERE bigintKey = '" & _
    myVar & "'"

but it doesn't update the record...

Answer 1

Data mismatch in criteria expression usually means that you SQL Syntax is wrong. Try copying the resulting SQL into a new Query in MS Access and running directly from Access.

What is myVar in your example? What is the resulting SQL?

Answer 2

So, you have MS Access DB which is a front end to SQL Server?
OR
Does the Access DB has linked tables to SQL Server?

After you run the DML statement, see if it updates SQL Server by looking into SQL Server (using query analyzer). My guess is that, Access might not be refreshing it.

Answer 3

There not a whole lot of reason to use a Primary key value that has a range from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807.

You best just change that primary key to a int column on the sql side. If you make this change, things will then work just fine. I recommend doing this. Rememeber, in sql server a int = long in ms-access.

Try opening up the linked table in the tables view in access. I find that sql 2005 table with bigint as the PK will not even display correctly (you will see deleted in every column).

I find the link DOES work ok if you remove the PK setting on sql server side. In other words you can have a unique index and also set it up to increment (identity), but do NOT set it as a PK. This not ideal from a documentation point of view, and if there is other tables in enforced relations, then I don’t recommend removing the PK setting (in fact you can't do this).

So, as it stands now I am unable to successfully link to a SQL server table if the PK is bigint. Note that other columns work fine as a bigint. They are simply linked as a text data type for ms-access. I see your code samples correctly assume this is going to be a text column (that is correct on your part). It is ONLY when the bigInt is a PK column does this mess up.

I would change the bigint on the sql side to int (I just can't see the need for such a MASSIVE sized pk). If you must keep the column as bigInt, then simply remove the PK setting on the sql side (if that is possible).

So you can have that bigInt column as an unique index, and it can continue to be a auto increment identity column. However, it CAN NOT be a BigInt PK, that just don’t work.