up vote 1 down vote favorite
1

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...

link
92% accept rate
feedback

3 Answers

up vote 2 down vote accepted

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.

link
"Rememeber, in sql server a int = long in ms-access" -- in SQL DDL, and INTEGER (and the synoym INT) is the same for both SQL Server and the Access database engine. LONG is a synonym for INTEGER for the Access database engine only. – onedaywhen Jul 2 '09 at 12:35
I said that the question of integers and longs are equal and in terms of MS access. I didn’t make any reference to jet or jet DDL statements at all. I simply stated that when you’re talking about long values in MS access, you’re generally talking about an integer in SQL server. It doesn’t help anybody here to state that integer is the same for jet/sql server ddl. However its most significant to point out to people that long values in MS access applications (code etc.) are integer values in SQL server world. You seem to be forgetting that you can build an access application and not use jet. – Albert D. Kallal Jul 2 '09 at 20:51
If you meant VBA6 or the MS Access table designer or whatever then please be specific. If you just say 'MS Access' then this can equally apply to the Access database engine and therefore SQL DLL. Note that 'Jet' specifically refers to the Access database engine before Access2007, whereas the SQL DDL keyword applies to the Access database engine, a term MS uses to refer to Jet and ACE collectively. – onedaywhen Jul 3 '09 at 8:39
feedback
up vote 0 down vote

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.

link
Access has linked tables. – THEn Jun 24 '09 at 21:05
1  
In that case, you will need to refresh/reopen the tables to see the updates in effect. If the SQL server shows the data updated, it is Access that has problem refreshing it. – shahkalpesh Jun 24 '09 at 21:19
feedback
up vote 0 down vote

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?

link
actually myVar is a integer value. – THEn Jun 24 '09 at 20:58
feedback

Your Answer

 
or
required, but never shown
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.