SQLite Forum

.net 4.7.x framework and EF 6.x with SQLite.

.net 4.7.x framework and EF 6.x with SQLite.

(1) By Larry (mudoch) on 2021-02-05 16:21:05 [link] [source]

Hi I'm new here, Run into a bit of a need vs. function issue.

Need: build a library (DLL) to manage addresses callable from VBA.

Issue: Not understanding how to use SQLite with .net framework 4.7.2 and EF 6.x. and it works when called from VBA.

I found on the internet some samples using .net Core 3.x and got it working using Nunit 3.x tests. But then lookups failed when called from the VBA application. The error was "provider not found". After 3-4 hours of trying to find a solution on the internet I put data storage/management on hold and reverted to SQL Express to flush out the other elements of the library.

Now I'm at the point where I really need to look closely at the data management of this project and of course someone wiped out the remote repo with that code in it. They didn't like the branch not getting any activity.

So I'm looking for some samples using .net framework 4.7.2 and EF 6.x that I can build the data management elements. In the end I want to be able to deploy with an .MSI file everything needed and without the need for SQL Express and the access rights that go with it.

A little background information on the project...

I started this last spring and dropped the desired SQLite for SQL Express because of the errors I ran into.

The solution needs to look up Country, City, State, Postal Code, and Box dimensional information. It'll be a .net 4.7.x framework library called from VBA via COM interface.

Specifically the DB has 10 tables, 1 table with 957,000 rows in it. The others have less than 100 rows each. Using text files such as XML or CSV to load in memory tables at startup takes a bit too long plus the other memory related issues. None of the tables get updated much, maybe ounce a month if that.

For maintenance reasons the goal is to have the main(large table) in it's own DB as this is global to all users at all customers and the other tables in a second DB which is global to all users but customer specific.

(2) By anonymous on 2021-02-05 18:16:16 in reply to 1 [source]

Need: build a library (DLL) to manage addresses callable from VBA. ... called from VBA via COM interface.

Some options for you:

  1. Windows 10 comes with winsqlite3.dll which is a DLL that can be used to create and access SQLite databases. (dated version of SQLite3.DLL)

  2. Use SQLite For Excel Version 1.0 (you can change the VBA to load the current version of SQLite3.DLL)

  3. Download the ODBC driver and use it with ADODB (dated version of SQLite3.DLL statically linked). The Excel Object Library has a CopyFromRecorSet method which makes it easy to retrieve from SQLite3 database & show in worksheets. Example here.

  4. Write your COM using System,Data.SQLite (has an older version of SQLite3.DLL statically linked). Likewise if you choose Microsoft.Data.SQLite.

  5. Write your own!

I'd use option 3 as a spontaneous choice presenting the least compromise unless you have the knowledge to choose option 5 (you can wrap your code as COM) and purpose build the nuances of your requirements.