Hi Just at a very early learning phase. My first task at hand is convert all the .mdb to SQLite.
I have installed the studio & .exe.
I still have not find the fastest to get the task done.
Once done I want to use msaccess ODBC to connect to the SQLite tables. Appreciate any a helping hand get started in my new project for very small biz.

Thanks for the sharing.

I will be using ODBC to access SQLite, I hope this way it is multi users, if not SQLite has a serious draw back. Even mdb BE is multi users enabled.

My first task at hand is to convert existing mdb with data to SQLite which I can then try my hand ODBC which I have downloaded from SQLite.org. Any advice.

SQLite isn't designed to be multi-user. That was never it's goal. It does work in multi-user - I've slapped it on a network share and happily used it, building many ERP-style applications, but if I came here crying that records had been corrupted, no one would have sympathy - I'd be using it incorrectly.

I've been using the system.data.sqlite.dll library (or wrapper?) to access SQLite in .Net and it works very well. That's not ODBC though.

Chris your views on multiuser issue jot me to plod carefully. Do you think I can use the ..dll library in msaccess by passing ODBC Since you design ERP style app , any open source ERP application to suggest..Thanks for your response.

Using ODBC to access SQLite is like driving a sports car by telling your deaf aunt which pedals to press and where to steer. It might be useful just to get your data into SQLite in the first place but if you intend to use ODBC permanently you might want to question why you want to use SQLite at all.

Also, see the last quarter of this

https://www.sqlite.org/whentouse.html

from "Situations Where A Client/Server RDBMS May Work Better" onwards.

Simon your view raise interesting questions in the pipeline to explore. Thank you.

Just to answer your answer first. I am a struggling tiny dinosaur using msoft access from Xp era. I like it as a RAD because I can easily debug the vba script & SQL codes. Moreover I am comfortable with VB6 & .net.

Of course I am looking for a open source RAD to replace msaccess 03 doing away with runtime. Any suggestions with a gentle learning curve.

Visual Studio just needs system.data.sqlite.dll, which would be shipped with your .exe. It'd need .Net runtimes, but these are usually installed on users' PCs now as they're so common.

your views on multiuser issue jot me to plod carefully

How many multi-users will you have? As mentioned, I've used SQLite in a multi-user environment just slapped on a Windows server and never had issues. Obviously you'd need to go through testing before releasing into production and believing some weirdo like me...

If you're used to VB6 though, you'll have no issues.

Chris, Just 2 or 3 users at a time, hence SQLite should fine. I work within a LAN for accounting or payroll or other admin tiny database with low activiy. The BE can even be the rustic Jet mdb without client/server & things work fine.
From my need would you be able to say SQLite is ideal my tiny msaccess FE connecting using ODBC. Much appreciate your help.

You'd have no issues. I had 30 users quite happily. I'm sure you can have hundreds, but the more 'writers' you have the more chance you'll have of a database becoming locked. Obviously you don't encounter that in a client/server environment.

Personally, I don't see the benefit of using MS Access as the front end. Why move to SQLite if you're still going to use MS Access? Is it a huge complicated front-end, or just a small number of forms?

Chris we probably belong to few generation apart as you did not have the legacy of using msaccess.

Now we have users numbers put aside I come back to ODBC as the way I know how to link to tables in other database such msSQL server. Your reference to another route using system.data.sqlite.dll gets my attention but still fuzzy how to go about replacing ODBC used in msaccess. Any suggestions.

Simon you may also further advise since to feel ODBC is like putting the pedal all over the place. Do you also subscribe to Chris .dll

as you did not have the legacy of using msaccess

We used .mdb databases extensively in my previous job, although we did use VB6 (and .Net) to talk to them. Primarily as MS Access was part of a premium Office suite, so VB was used to save time.

In my next role they used MS Access extensively to log store equipment with multiple forms to access one huge database table instead of separate tables. Just urgh.

It's not my use of MS Access which is rusty, but the use of ODBC.

Only now you've mentioned SQL Server. For the sake of maintenance, choose one database store - either SQLite or SQL Server - and stick with it. As an ex-IT Manager, it was a nightmare having systems on different systems, systeming in different ways.

Oh about the rustic part.

Certainly due to the size of my projects, at the back of my mind I have already chosen SQLite; I love the direct file system access like .accdb without the server process.

Then it begs the question. In ODBC the tables in the database is link to the Front End thus allow FE to do the query, etc. It really help if you tell me how & what really happen when you use the system.data.sqlite.dll library in .NET to connect to the SQLite. Without hands-on I cannot visualize the process. Much appreciate if you can describe this process to me. Thank you.

that's because you need a sever to run . database is just that a file. without a database server which is the traffic control and transition sending/ receiving. for server you'll want to see xamp, lamp or bitnami which are packaged server database and language in that case php in the forementioned that work website as the client access and as other mention you need to design the code for user interaction.

access is not multiuser, its just the windows locks the read and writes and adds them, if a user corrupts the database its gone, as since no server there are no backups maintained nor rollbacks transactions , there no way to restore. file control as in mdb is not same as database server

as for any shell script like powershell or vbscript can have user interaction and connect to any odbc database connetion

The easiest solution to converting from Access to SQLite would be dbMigration.NET on https://fishcodelib.com/.

It's free and I've used it myself recently for a migration from Access and dBASE to SQLite.

When you're done you should also look at Database.NET on the same site.

Thank you bro for the conversion link

You can try my app sqlite-gui.

Run app, create empty database then in main menu > Tools > Import > via ODBC: create DSN to your mdb file, select multiple tables and press "Import table(s)". That is all.

P.S. I suppose this feature can be buggish because the app is not very popular and I'm not sure that anyone tried it.

Mr. Fuzmic: "Hi Just at a very early learning phase. My first task at hand is convert all the .mdb to SQLite. I have installed the studio & .exe. I still have not find the fastest to get the task done. Once done I want to use msaccess ODBC to connect to the SQLite tables. Appreciate any a helping hand get started in my new project for very small biz."

I have done this the following way (I started .accdb files, not .mdb, but don't think this should make a difference - if it does save your .mdb file to .accdb)

I created a new SQLIte database file (call it NEW.SQLITE3) and used CREATE TABLE (call NewTable) SQLite based on the structure of the old Access table. Note that SQLite data types are different from Access (and most other DBMS's) so read up on that; in general it's no biggy (be see IMPORTANT>>> below).

I used the werner http://www.ch-werner.de/sqliteodbc/ to create an ODBC to the new SQLITE database.

In Access, I used the "external data->new data source->from other sources->ODBC database->link to data source by creating a linked table" to get to NEW.SQLITE3, it will probably be a "Machine Data Source" depending on how you did the ODBC.

I linked to NewTable. This appears as a linked table in the Access object explorer.

Opening NewTable in datasheet view from Access, you can paste between your OldTable (sored in the Access .mdb file) and your NewTable.

IMPORTANT>>> Access may report that a record in your SQLite database can't be modified because it's locked, if you are modifying it from an Access datasheet view. This can occur when Access detects a difference in one of the fields of a record, which can occur for DOUBLES. This is because of the non-standard way SQLite stores DOUBLES, i.e. they are not IEEE 754 binary. The MSAccess/JET engine detects a difference between the double on disk in the ODBC'd SQLite database and the double in memory and thinks the record changed! The only way I 've been able to get around this is by storing all doubles as strings.

In Access, I used the "external data->new data source->from other sources->ODBC database-> [snip]

What's the benefit though? This seems like performing 900 steps instead of 1 if you were just using the Access .mdb file.

JRobin Thanks for sharing. I will go Hands on to get the feel as to where it take us

the non-standard way SQLite stores DOUBLES, i.e. they are not IEEE 754 binary

Can you substantiate that claim?

SQLite stores all values in network byte order (big-endian), always, regardless of the byte order of the machine on which it is running. This is so that SQLite database files will be cross-platform between machines with different byte orders.

I don't know, but I'm guessing MS-Access stores values little-ending since MS has traditionally been an Intel-focused OS and Intel CPUs are little-endian.

So maybe one needs to byte-swap when moving from MS-Access to SQLite.

Indeed, that page explicitly says:

So-called "REAL" or floating point values are stored in the IEEE 754 Binary-64 format

Modulo the endian'ness Richard just mentioned.

WHo cares what the internal storage format is -- you are not transferring internals, you are transferring externals.

That is, you create the same table in SQLite3 as you have in your silly Jet database. Then youdo the following

while not EOF:
   read a row from Jet Database
   write a row to SQLite3 database

Why on earth would any read the underlying raw record from one place, decode and encode it, so that it can be written raw to the file?

Only a masochist would do that. THe rest of use would use the already existant "raw reading and decoding" built into Jet and the automatic "encoding and raw writing" already built into SQLite3.

This applies equally to ANY source and ANY destination database management system (though calling Jet a Database is like calling the "junk drawer" a filing system.

I have heard about VbRichClient for years, but have never tried it.

It would give you msaccess and sqlite from VB6. I'll check it out later today. It was last updated 5/29/23.

https://www.vbrichclient.com/#/en/About/