MS Access to Realsql

Databases
1

hi,
I almost decided about switching to Realsql from MS Access. So far I have tryed to use datatypes accordingly to the data I had to store. So the MS Access db has been designed using currency, date, integer, text, memo datatype and soon. Now, before beginning to adapt the code, what might be the problem, if any, I might have to face? shoud I convert all the data to the sqlite text data type or is it better to use, when possible, the integer and real datatype, too.? Then as for Dates, I converted the dd/mm/yyyy to yyyy-mm-dd. what about the others datatypes such as currency ?
thanks for any suggestions.

2

sqlite is tad different than most db’s in this regard

In Access if you tried to assign a text value to a currency column you’d get an error
Sqlite will let you - its almost typeless (not quite but it seems like it compared to most other DB engines)
See http://sqlite.org/lang_createtable.html

So you’ll want to make sure your code does a bit more work to sanitize values before you put them in the db.
Beyond that you can use whatever types you want and it will handle things quite well.

3

Thanks Norman,
I also took a look at another page from Sqlite that was useful to read too.
http://www.sqlite.org/datatype3.html
As for my task, tomorrow I’ll begin to play a little bit.

4

You might want to take a look at SQL Migrator from LogicalVue http://www.logicalvue.com/products/sqlitemigrator/. It will take an ODBC data source (i.e. your Access Database) and convert it to SQLite. It does all of the heavy lifting. Well worth the money, in my opinion.

5

or MDBLite from SQLabs at http://www.sqlabs.com/mdblite.php . All you need to do is drop the mdb into the container and poof… new database is ready. Just need to save as sqlite file. We use it all the time when doing data migration from Access to SQLite.

6

thank you for your suggestions. I’m going to download both programs to try them, even though I already converted the mdb to sqlite. Now I am working on adapting the code. main issue i gaced so far was with dates format incompatibility , so I had to use something like update table set columndate = substr(columndate, 7) || ”-" || substr(columndate, 3,2) || ”-" || substr(columndate, 1,2) to trasform dd/mm/yyyy to sqlite yyyy-mm-dd format.
next problem was related to some incompatibility between ms access syntax and Sqlite’s such as year or right joins and even with TRUEs and FALSEs.
I still have to face the following issue. I wrote tbe previous ms access ODBC connection string (db.datasource=…) inside the “App” open 's event handler s well as the declaration statements, so that the db could have been opened just once, at the launch of the program. I tryed to substitute the ms access declarations and the connection string with the sqlite declaration but it doesn’t work. any help?