1

Lately I have migrated my Access 2000 backend data and tables to a 2012 SQL server. In the access frontend I have linked the SQL tables that were migrated. Most of it is working fine except for (now) one form.

In this form the data is being loaded from the SQL server using this query:

SELECT * FROM qryAbonementens WHERE EindDatum is null or EindDatum>=now()

It also used a filter and sort:

((Lookup_cmbOrderNummer.Omschrijving="GJK"))

And the sort: 

Lookup_cmbOrderNummer.Omschrijving

These things may be irrelevant but Ill just post as much as possible.

The data loads in the form perfectly, however when I try to change a record in the form, I keep getting the:

error invalid character value for cast specification (#0)

While checking out posts with the same problem I encountered this post:

MS Access error "ODBC--call failed. Invalid character value for cast specification (#0)"

This made me believe that I was missing a PK somewhere so First I checked the linked table in Access design mode:

enter image description here

Tekst = text, Numeriek = numeric, Datum/tijd = date (sorry for it being dutch).

The same table in SQL looked like this:

enter image description here

They both have PK so I guess this is not the problem.

Though, when looking at both datatypes you can see 2 differences on the InkoopPrijs and VerkoopPrijs fields. In SQL these two are decimals(30,2) and in the design view in the linked access table they are, I guess unknown, and so they are being cast to text values. Perhaps this is the cause of my error message?

The record I am trying to change and which gives the error is this one (but it is on all the records): enter image description here

I've read somewhere that adding a timestamp field to the SQL server could help but I have no clue it also works in my case or how to do this.

Community
  • 1
  • 1
Nicolas
  • 2,277
  • 5
  • 36
  • 82

2 Answers2

2

As you have guessed, the decimal(30, 2) columns are the problem.
They are too large for Access to be used as numbers.

I can reproduce the problem with Access 2010, although I can enter numeric data into the field. But when I enter text, I get the exact same error message.

decimal(18,2) vs. decimal(30,2)

decimal(18,2) works fine (it's the default decimal precision for Sql Server 2008).
Surely you don't have prices in the 10^30 range? :-)

design view

You might also consider using the money datatype instead, although I don't know how well Access 2000 works with that.

Andre
  • 26,751
  • 7
  • 36
  • 80
  • Thank you for your input, however it is not working. I changed the datatype in my SQL server to `Decimal(18,2)` instead of `30,2`. This didn't had any effect though. I am now getting the error: `scaling of numbers has resulted in cutting of values`. This caused that I have a lot or `#error` values now (so I guess I need the 30,2)? Besides that I also get the `while you changed the record, the record has been changed by an other user, if you save your record the changes will be overwritten`. The Save record is being grayed out though.. – Nicolas Sep 28 '15 at 10:11
  • P.S. the access linked table changed from text to `numeric` (`decimal 18,2`) when I changed the SQL length of the decimal to `18,2` – Nicolas Sep 28 '15 at 10:15
  • @Nicolas: where and when do you get the scaling error? Do you actually have numbers in that table with > 18 digits before the decimal point? – Andre Sep 28 '15 at 10:16
  • Right after I reopen my form. But the weird thing is that I don't have any large values ... the biggest is around 5 numbers. – Nicolas Sep 28 '15 at 10:20
  • The scaling error only seems to be happening on dummy values though (not sure though cause they keep popping up). However if I update a normal record than I get the earlier described saving error. – Nicolas Sep 28 '15 at 10:26
  • @Nicolas: Maybe try removing and re-adding the linked table. And if the form is based on a query, edit that, remove and re-add the decimal fields. – Andre Sep 28 '15 at 10:26
  • Try's both but sadly no result. – Nicolas Sep 28 '15 at 10:34
  • I fixed the problem! Check my added post below! Thank you a lot for your help and pointing me in the right direction ;) – Nicolas Sep 28 '15 at 11:28
0

Alright I got it fixed. @Andre451 post about changing the 30,2 decimal values in the SQL server to 18,2 gave me the the record is changed by another user error. This caused me to look differently at the problem and instead of fixing the

error invalid character value for cast specification (#0)

error I looked at the

record was changed by another user error

I came across this post: Linked Access DB "record has been changed by another user"

Here someone suggested to add a TimeStamp field to the SQL table. So I did and now it seems to work again! And it also seems to work with the original (decimal 30,2) value!

Community
  • 1
  • 1
Nicolas
  • 2,277
  • 5
  • 36
  • 82