Changing datatype, is it that complicated?

[alec]

I have a form created with Access'97 and VBA. It's been in production since 3 yrs ago.
Currently I need to do a little enhancement that is: In one of the textbox for credit course, the datatype that is used to be set for an integer in SQL is now need to be changed to float since user want to be able to enter 1.5 credit.

I've changed the datatype into float by went into the SQL Server ( enterprise manager ), saved it.
Then I re-linked the associated views and the tables from Access,changed the format to format=fixed and decimal=2, changed all the necessary variables to double.

When I tested the application, I enter 1.5 in that textbox and saved it, unfortunately it is saved into 1.00 instead of 1.50 and I got a message saying:
"The microsoft jet database engine stopped the process because you and another user are attempting to change the same data at the same time"

I know for sure there is no other user? and why the number is rounded up???????? Anyone can help me please? I've been trying to solve this problem for 2 weeks unsuccessful, Thanks! Alec

 

[dneufarth]

What is the Field Size for the Fixed format field in question? If Integer, try Single or Double.


Dave

 

[alec]

Hi neufarth,
I can't change the field size for the fixed format field. The red note next to it says: "This property can not be modified in linked tables"
I can only change the format, decimal places,input mask and caption properties.

currently the field size for the fixed format field is Long integer. Alec

 

[Mongooses]

Alec - is the table linked to any other tables that this field is updated in? Obviously the reason it's rounding is because it's data type is still integer. Why you are unable to change it to a float (single or double) seems to be that the table is linked to another, if so then you will need to first sever all the links then change the data types in all the tables.
HTH,
JC

 

[dneufarth]

Make a backup of the database.

Delete the linked table from the database.

Add the linked table back to the database.

Hopefully all forms, queries, reports will still work.

Changes from Text to Double in a quickly constructed sample DB with linked table worked for me. Both were Access databases though.

Forms, join fields from other tables may need their data type changed, or at least conversion functions used, to make data types the same.


Dave

 

[dneufarth]

Forgot to mention that the delete/add really wasn't necessary, but perhaps it might be needed for an ODBC DB.


Dave