Create/Deploy a VB App with Local Database

Morning Everyone,

Been asked to create a VB App with a local database. To be honest, I have developed applications with links to centralized databases or local flat files but never an application with a local database. I don't want to go down a specific path and find I made a poor choice. There are so many DB options out there and I am hoping if I describe the need, someone may suggest option that fits the need.

The app needs to be offline. Reference data in the local database for combo boxes and look-ups. Hold data entered during the course of the day. At the end of the day, when the user is in a more friendly environment with access to the internet, they can sync the local database with outward facing SQL. The local DB would have several tables to hold reference data as part of standard normalization. A couple of the tables may have up to 10,000 records but I am trying to narrow down that scope to speed up syncing. The key entry data may only be 30 to 40 records per day with up to 150 fields varying from text, to memo, to numeric values. If possible, I would like to encrypt the database. At minimum, password protect the database and encrypt the hard drive to try and protect the data.

I will need the app and database to deploy as a single exe or msi. So easy a user can do it with just a double click. My proposed users are not tech savvy. Double click may even be a stretch in some cases. Application will reside on Windows 10 Pro OS laptops or tablets. No android, iOS, or chrome books. When I update the application, I will update the database as well. I can overwrite the database for updates. Records do not need to be maintained locally so that eliminates a lot of coding to upgrade in place.

I will need the ability to either export the data directly to a SQL server or export in a way I can transfer the data to a SQL and run an import process.

I am currently using Visual Studio 13. I have looked at SQL Express, MySQL, and SQLite. I also was reading about a built in SQL in Visual Studio but I have not gotten to far into it or even determined if it is built into 13 or a newer version I don't have.

With that said, anyone have a suggestion that may fit the bill or get me close? Insight appreciated.

Douglas

SQLite
As for Password-Protection and/or encrypting, i'd refer you to Olaf Schmidt ("Schmidt" being his Username here), since he has written a substantial framework for use with SQLite (which is for free).

Quote:

---

Originally Posted by Zvoni

SQLite
As for Password-Protection and/or encrypting, i'd refer you to Olaf Schmidt ("Schmidt" being his Username here), since he has written a substantial framework for use with SQLite (which is for free).

---

Yes, SQLite would be a good choice for a "locally encrypted temp-storage, which takes up a days work"...
But the OP plans to do all that stuff in VB.NET (whereas my support-Classes for SQLite are all COM-based, for primary usage in VB6).

Though the "official .NET-Provider for SQLite" comes with the same "compiled-in encryption-plugin" as my COM-wrapper does,
so there should not be any problems at that front.

The rest of the task:
- SQLite-interaction
- determining differences to the online-DB
- uploading those differences to the Remote-Server in question...
- ... plus "merging" these diffs with an SQLServer-DB when they arrive "up there"
- and finally: "providing an easy to use deployment-package for the user"

All of the above should better be solved with the tools and classes of the .NET-framework ...
(using my COM-framework for this, would make sense only for VB6/VBA/VBScript-Users).

So I cannot be of much help in this...

HTH

Olaf

Zvoni and Olaf,

Thank you very much for the input. I was kind of leaning away from SQLite because I didn't see much in the description and documentation on using VB/VB.net. Most of my reading to date mentioned C a lot. I've done some C programming but it is not my strongest. Your writings above give me lots of hope that I can make this happen. I am primarily a VB6 programmer. I only look at VB.net to try and stay current. I will definitely look at Olaf's work. And, thanks to Olaf's input, look more into .net as well for this project.

I was hoping someone would have some good input. Really appreciate your insight. Thank you both.

Douglas

Quote:

---

Originally Posted by DMoody007

I am primarily a VB6 programmer...
...thanks to Olaf's input, look more into .net as well for this project.

---

If you're more familiar with ADO-usage (and ADO-Recordsets) in VB6, than you are with "ADO.NET, LINQ or .NET-DataTables",
then the RC5-wrapper-classes for SQLite have less of a learning-curve (the cRecordset is nearly fully compatible to an ADO-Rs).

Also deployment-wise will you encounter less problems with a VB6-based approach...
(because your solution can be "shipped in a ZIP", without requiring any installer- or registry-interaction,
whereas the SQLite-provider for .NET is not officially part of the preinstalled .NET-framework,
and so you would have to figure out, "how to deploy a NuGet-package" along with your .NET-based GUI).

HTH

Olaf

Love ADO! You definitely sold me on the approach of VB6. More comfortable there anyway.
Been reading about SQLite off and on all day and finding some coding examples in VB. It is looking very promising for my project.

I was looking at SQLite Maestro as an administrator. Cost wasn't much but seemed to have a lot of functionality with a GUI that someone else could follow if I was hit by a bus. Also helped with Encryption and Syncing. Any thoughts on it?

Really appreciate the insight. Your proving to be a valuable asset.

Douglas

@SQLite Admin-Tool: I'm using SQLite-Browser which is Freeware
https://sqlitebrowser.org/

Maybe not as "powerful" as Maestro (No idea, never used Maestro), but it serves my needs

I will take a look. Thx.

Quote:

---

Originally Posted by DMoody007

Been reading about SQLite off and on all day and finding some coding examples in VB.

---

There's a larger Tutorial-Zip for RC5-SQLite-usage on my site, which I'd recommend to study when you're new with this stuff:
http://vbrichclient.com/#/en/Demos/SQLite/

But also this Forum here contains a many examples ...
(quite a lot of hits, when you use the Google-based Forum-SearchBox in the TopCenter of this page, with the search-terms: [rc5 sqlite]).

As for a decent (editable) Grid, I'd not use the VB6.DataGrid (which was used for visualizing in the Tutorial above),
for new Projects anymore...

There's a better (MS-dependency-free) editable Grid out there in the meantime:
http://www.vbforums.com/showthread.p...25#post5236525

Here is a Demo, which shows hot to use it in Binding-Mode with SQLite:
http://www.vbforums.com/showthread.p...te-Recordsets)

Quote:

---

Originally Posted by DMoody007

I was looking at SQLite Maestro ...
Any thoughts on it?

---

FWIW, I'd not make myself dependend on such helper-tools (I'm not using any).

The tutorial shows, how easy it is to make your own Grid-based Viewer for "all Table- or View-Defs currently in the DB".

And for creation of new Tables (and their Fields), you can use the Helper-functions on the cConnection-Object:

Code:

---

'MyTable-definition (containing all the different types, RC5-Wrapper supports)
With Cnn.NewFieldDefs
    .Add "ID Integer Primary Key" '<- AutoID-def
    .Add "SomeTextField Text" 'no Len-description needed (SQLite has variable length-String-Fields)
    .Add "SomeIntField Integer" 'this type is also universal (from byte-range-integers up to 64Bit ones)
    .Add "SomeRealNum Double" 'real numbers are always stored as 8Byte-DoubleValues
    .Add "SomeBool Boolean" 'the Boolean type (Bit would be recognized as well)
    .Add "DateAndTime DateTime" 'a Full-Date (with time-part)
    .Add "DateWithoutTimePart ShortDate" 'a ShortDate (with only the Date-part)
    .Add "SomeBlob Blob" 'the Blob-Field-Type (fed from normal ByteArrays)
End With
Cnn.CreateTable "MyTable"

'MyOtherTable-definition
With Cnn.NewFieldDefs
    .Add "ID Integer Primary Key" '<- AutoID-def
    .Add "AnotherTextField Text"
End With
Cnn.CreateTable "MyOtherTable"

---

So, the above shows, that these defs look quite similar to UDT-defs ...
(with the difference, that the name of the Entity comes below the FieldDef-block).

The SQLite-DBManager-Apps offer no real advantage IMO (compared to such self-written defs in language-code).
After you've learned the handful of DataTypes, then you're usually faster with typing (and occasional "FieldDef-block copying") in the IDE,
than with "GUI-based Field-Definition via Manager-App" (where you have to type the FieldName at least, too)...

There's also the advantage (when you create the Schema in VB-Code, e.g. in a modDBSchema.bas),
that you can create a new DB (including its Schema) on the fly (no need to ship with an "empty DB") -
but there's also the advantage with "DB-auto-versioning" (when you add Fields to certain tables later).

Just my $0.02 on the "DB-Manager-topic".

If you however prefer to use external tools, then at least make sure, to enter the Field-Type-Names as shown above
(especially the DateTime, ShortDate and Boolean-TypeNames need to match, if you want the RC5-wrapper -
to properly "map, detect and autoconvert" these types from and to Variant-Values.

HTH

Olaf

Quote:

---

Originally Posted by Schmidt

There's also the advantage (when you create the Schema in VB-Code, e.g. in a modDBSchema.bas),
that you can create a new DB (including its Schema) on the fly (no need to ship with an "empty DB") -
but there's also the advantage with "DB-auto-versioning" (when you add Fields to certain tables later).

Just my $0.02 on the "DB-Manager-topic".

---

I'll take $0.02 any day of the week. Really good note on the sending of the DB. Had not thought of that. Thank you.

The reference material is also a great help.

Hope you have a great weekend!

Hope I am not breaking a protocol replying back on this thread. Basically reporting back and seeing if you have a suggestion for error that I am getting.

All of your advice worked fantastically for my project. In my development environment, I have the database integrated and the forms are working. Data is loading just fine. All of your recommendations were great sources to help me move right along. Thank you!

I tried deploying my application to another computer to test functionality. I had several errors and manged to work through them like needing to send local copies of the SQLite DLL's and marking the DB as content to include in the export. Also had a few .net issues to work out.

The issue I am currently banging my head on is I think related to relative path. The application can not find the Database. In the root of the application is a folder called _Data with the DB name as myData.db.
In the DEV environment, I can use this line: sqlite_conn = New SQLiteConnection("Data Source={DB};Version=3;")
{DB} = c:\users\...\_Data\myData.db where ... is the full explicit path and it works but I know the path would never exist when installed on another box
{DB} = ..\..\_Data\myData.db - tried this thinking it was defaulting to bin\debug - works in DEV but not installed
{DB} = .\_Data\myData.db - Was recommended in several sources as a good relative path, again works in DEV but not install
I tried to use my.application.info.directorypath to try and build the full path but didn't work.

Looked for the install exe and the db file on the install laptop and found the directories are in totally different places. Hoping you recognize this issue and can point me in the right direction.

Douglas

if your app is machine-centric (all users use the same database):
There is always "c:\ProgramData\MyApp\mydatabase.db"

IF it's user-centric: You could use the Environ-Function (or its API-Sisters) to to get the logged in user(-profile) and construct a path from there:
"c:\users\{Insert Environment-Variable}\AppData\Local\MyApp\database.db"
See also: https://stackoverflow.com/questions/...-on-windows-xp

As a general rule of thumb: Don't place your database in the same (or sub-) folder as your app (e.g. "c:\Program Files (i386)\MyApp\Data\database.db"

Zvoni,

I am open to installing in a more generic space. Is there a way to send the DB with the install and tell it to install in a specific directory? I personally have never tried to ship a DB in an app.

I know I can build it from within the app and specify the directory. Hoping to send the database with records in it to avoid the lengthy sync process to get the records.

Off the top of my head, I am thinking an ugly way is the send over the DB and maybe use FileIO to create a directory and then move it. Hoping there is a more simple/clean way to do it.

Thanks,
Douglas

Doug,
it's been some years since i used the P&D-Wizard, but IIRC you can define install-paths for user-files (like a DB).
You could create a "Hello World"-App which writes to a Textfile. Use the P&D-Wizard on that project, and look if you can find the option the specify a path for the textfile
No idea regarding Inno-Setup and its sisters
OTOH, for filebased databases i'd rather go the route to create the database from code, since you're more flexible that way
(e.g. a dialog "choose the path where to place the db" incl. creating folders and whatnot)

Zvoni,
Been over a decade for me as well. While refreshing my memory on the tools and the process to integrate, I found something else that helped me.
Application.LocalUserAppDataPath This got me within one directory of the db file on the remote workstation.
I installed the application on two different machines running different OS and both found the Database file and displayed results.
You pushed me just enough to get past the last error so Thank you!
Douglas