Currency data type in SQLite for Access

Aug 19, 2013 at 9:07 PM
I'm testing out the SQLite for Access sample. It correctly converts from Access to SQLite (currency to decimal). I then test converting this same file from SQLite back to Access and this field comes back as Long Integer.

I've looked at the code and the data mapping maps 'decimal' to 'double' in Access, yet this is coming back as Long Integer. Upon further testing, I find that if the data in the first record is a whole number i.e. 18.00 it'll come in as Long, and therefore truncate the values in subsequent records. If the data in the first record has a decimal place like 18.2 then it'll correctly map to double. Is there something I'm missing? Why does it use the data rather than the mapping?
Aug 19, 2013 at 9:20 PM

I have no insight or experience with the extensions in the SQLite for Access download.

I'm guessing he uses the datatype of the data returned from SQLite to base the types on. SQLite is a bit funny, in that it doesn't really have column types, just a type for every data value. So when you get a query result, there's no (easy) way to know how the column should be mapped. So this problem is in a sense inherent in using SQLite.

Aug 20, 2013 at 3:07 PM
I thought it had datatypes - i.e. NULL, Float, Integer, Text, and BLOB. Anyway, I think I have a solution. I'll modify the code to create the tables in Access with the correct datatypes (specific to my application), and then just append the values from SQLite, and not let SQLite guess. It's not a generic solution, but will work in this case. Many thanks.
Aug 20, 2013 at 3:26 PM
Edited Aug 20, 2013 at 3:26 PM
SQLite has datatypes, but for the values rather than the columns. The assigned column types just express a preference for some kinds of conversions.

Your solutions sounds like a good one.