Results 1 to 7 of 7

Thread: Converting application from MS Access to SQLite database

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Mar 2013
    Location
    San Francisco, CA
    Posts
    487

    Converting application from MS Access to SQLite database

    I have been using MS Access as the back-end database for all my VB.NET projects and now I'd like to convert one of those projects to use SQLite as its back-end database.

    I found an on-line tool that converts an ACCDB file to an SQLite file.

    https://www.rebasedata.com/convert-a...-sqlite-online

    I downloaded the SQLite Browser so that I could take a look at the newly converted SQLite file. The tables, fields, etc. all seem to be properly converted.

    https://sqlitebrowser.org/about/

    My question...is it just a matter of changing the application's connection string from an MS Access string to one that is for the SQLite database?

    I know that I'm at the beginning of a new "adventure" with this project, and hoping that it will not be too onerous. Any advice would be most appreciated

  2. #2
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: Converting application from MS Access to SQLite database

    Yes... but... that's without seeing what your queries looks like. It's possible that you may need to change some of the queries to suit SQLite. Or, you may not. I don't know. But any changes you do encounter shouldn't be a huge deal. But it also depends on what that query does and how it does it. I'd go ahead and change the connection string, and then run through the app and see what queries fail... go from there. See what fials and why, and just fix them as you go.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  3. #3
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,989

    Re: Converting application from MS Access to SQLite database

    I would expect that you likely WILL have to change something about your queries. Older Access doesn't use named parameters, and I wouldn't expect accdb to do so, either. In that case, if you are using parameters, then those might change, and for the better. They might not, too, depending on how you wrote the queries. Another difference might be the delimiters used on dates and GUID fields, if you have either in a query. Still, it shouldn't be a terrible burden.
    My usual boring signature: Nothing

  4. #4

    Thread Starter
    Hyperactive Member
    Join Date
    Mar 2013
    Location
    San Francisco, CA
    Posts
    487

    Re: Converting application from MS Access to SQLite database

    Do I need to download/install anything else to use SQLite in my app (other than converting my ACCDB file)? NuGet, GetHub, etc.? I’m hoping that for a laptop with 1TB of SSD memory, the SQLite “footprint” is pretty small.

  5. #5
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Converting application from MS Access to SQLite database

    If you have been using an ACCDB file then you will be using System.Data.OleDb types and the ACE OLE DB provider to connect to the database. If you want to just change your connection string then you will need a SQLite OLE DB provider. Ideally though, you'd switch from OleDb to a dedicated SQLite ADO.NET provider, which would mean installing an appropriate NuGet package. There are packages available from both SQLite and Microsoft.

  6. #6

    Thread Starter
    Hyperactive Member
    Join Date
    Mar 2013
    Location
    San Francisco, CA
    Posts
    487

    Re: Converting application from MS Access to SQLite database

    Looking at NuGet packages for SQLite, and there are several to choose from. Other than the obvious 32-bit vs. 64-bit criteria, which one should I install?

    https://www.nuget.org/profiles/SQLite

    My application is a 64-bit Windows Forms project.
    Last edited by Mark@SF; Apr 16th, 2021 at 06:29 AM.

  7. #7
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Converting application from MS Access to SQLite database

    Quote Originally Posted by Mark@SF View Post
    which one should I install?
    The first one.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width