What Database Is Right For Me?

Hello all,

I've been searching and searching for a database that suits my needs but nothing seems to do the job, or atleast the way I have understood the system it doesn't work for me.
Pretty much my band and I have been using an Excel database stored on the OneDrive, from there i had another workbook that created set lists of from our database for us, alot of data in it like, Title, Artist, Instruments, Notes. It had automated scoring that after a certain amount of not being played would start to degrade the score, colour system all the good stuff.


Problem is Excel is becoming increasingly unstable with more and more data, recieving memory errors, plus I am looking at doing more complicated coding and it made sense that I make a proper program. I've used VB.net before and VBA is similar but you all know that.

So now I am translating the basic functions over, but I need to work out how I am passing data around before I can begin doing stuff with it of course. I have had a look at SQL it requires extra installation which i don't want, Access for what ever reason even though the full program is installed, it believes a .dll file is missing that allows the connection to visual studio, even though it isn't. I was set on running out of a .xml file but while it seems to work in an address book format, reading and writing programmly as a database didn't seem to be working.


I would like to be able to load the database in a few different ways. I have a TCP connection set up for a host/slave on the program i am making, so it needs to be accessable on high demand from multiple devices on the same network for when we are performing or practicing together. But at the same time we don't even live in the same town, so I was hoping to put the database on something like the OneDrive where multiple instances could access it, (using a timer and a time stamp of sorts to see if the database has been updated by someone else). So at the moment I believe the best one would be SQL and port forward. But my network doesn't portforward well, nor do I have a computer to dedicate as a server. Plus i would like to distribute the software later on as a all in one service, but i don't want people to have to go through the hassel of setting up there own server, so i want it to be intergrated.

I am aware this would mean alot more coding for me, but i think the outcome will be worth it. To sum up the program when we are performing will have 4 (in my case) clients that will be telling the host instance what song to load the settings for the song. Then when we are practicing individually have 4 instances of offline hosts accessing the database sometimes at the same time. And i'd like to make it as user friendly as possible. In my case i am in a band and a duo, so i would be apon load up choosing what database i want to use.

I know that is alot of information but from what i have seen in my searches the smallest of information could changes the type recommend greatly sometimes. And you can probably see why .xml files as a database made sense if it wasn't so clunky.

I learn best by reverse engineering, so if there is a demo project for visual studio that you know of somewhere that i can look at, let me know, but obviously any help at all would be much loved and of course let me know if you need more information, really just need a recommendation for what way to head.

Thanks in advanced

Bensley

SQLite.
the engine (sqlite.dll) is probably already installed on your machine, so you'd just need the ODBC-driver. Plenty of sources on the net.
Use DB-Browser for SQLite to design your Database, use whatever Language(IDE) you like to write code.
regarding multiple-client access. there is pro and con for SQLite.
As long as you don't have multiple clients trying to WRITE-access the same row, SQLite handles concurrency reasonably well.
So if your multiple clients access it at the same time just with Reading-Requests you should'nt have any problems.

BUT: to make something clear: A dedicated DB-Server is still the right way to go. If you'd like to do that via portforwarding (which still means exposing it directly), or via webservice, is up for debate.
There are a lot of Web-Space-providers offering "ready-to-use"-packages incl. DB-servers (MySQL)

Quote:

---

BUT: to make something clear: A dedicated DB-Server is still the right way to go. If you'd like to do that via portforwarding (which still means exposing it directly), or via webservice, is up for debate.

---

The only good way to achieve what you want is a dedicated Database server which everyone connects to, and those connections should really be done via a web service.

You have a choice of databases but if your used to using Microsoft technologies then i would recommend SQL Server as it will feel more familiar to you. Don't use Access for something like this it isn't really designed for large scale multi user environments.

You can buy web hosting which includes database support so you dont have to host the database yourself (which would require you to buy a server and configure it so that it is secure for web traffic), and then really what you want to read up on is creating a Rest Web Service.

A web service is like a layer between you and your database, so your application make calls to your web service, which talks to your database and sends data back to you over the web.

Data is sent back and forth via HTTPS eliminating any need for port forwarding or any of that stuff, the computer you have the client software on only needs to have a internet connection.

Here is a tutorial to give you a start

https://dzone.com/articles/step-by-s...ul-web-service

In my opinion, using SQLite for that scenario (to support a "handful of band-members") is a good choice
(since this DB-format is also recognized on any modern mobile-device, not only on Desktops).

I'd also suggest, to develop this "Sharing-App" in a way that supports "Offline-mode" (no inet-connection required, when "working on your own")
(using a local SQLite-based DB which contains the "settings for different arrangements and songs and tracks")

The problem then problem boils down only to: "how to synchronize" (the local DBs of all "band-members, when they work in an online-session").

And I wouldn't use OneDrive (or other such filebased-online-services) for that
(because they have too large SyncRun-delays - which are in the minute- and not in the second-range you probably aim for).

Better would be a WebService which receives, stores, and later hands-out "only the Diffs" of the local DBs from a kind of "persistent Queue"
(which could be stored in a "DB-like-instance", or even in plain-files on said Online-WebService).

Such services can be used (these days) even for free, when you stay within certain vendor-limits -
e.g. "AirTable" (https://airtable.com) offers (already in its "free plan") an unlimited number of "Bases" (though restricted to 1200 Records per Base).

These 1200 Records maximium offer an already large enough "online-queue" to "sync against".

I could show you an example, how such "local Offline-SQLite-DB with online-Syncing" could look like (using the free Airtable-Service) -
but you'd need to switch to VB6, when you want it "copy&paste-able")...
Since you seem to have a lot of code in VBA already, a switch to VB6 should be less painlesss anyway (due to full language- and COM-compatibility).

HTH

Olaf

Thank you all for your responses. The two main options so far being talked about, SQLight and an online database both are good options, unfortuantly Schmidt, hit the nail on the head, an online database is perfect, but turning up to a gig begging for internet in my case isn't the best look, and my drummer doesn't always have internet connection to access an online database. And SQLight from the research I have done in to it, it doesn't like 2 threads (or computers in this case) writing to it, you could add a work around that if it is busy it just waits and trys again to work around that.

I would really love it if you had an example of a "local Offline-SQLite-DB with online-Syncing" That sounds perfect, a read only offline mode or when it comes online it is able to sync it's changes and negotiate what is the most recent data for descrpiacies would be more ideal, i don't know if possible though.

So if you do have an example of that i would love to see it, only need basic connection to understand it, if not somewhere i can start researching it. Thanks for the suggestion to go with VB6, i know it is closer to VBA then VB.net, but i believe, and correct me if i am wrong, VB6 is a no longer receiving updates, and with the sort of stuff i will be doing, I need the extra power of Vb.net, and I am not sure if VB6 handles MIDI very well, I know VBA while can send, cannot receive MIDI, due to callback issues.

I believe going this way, while for my personal use just means i have to sort out a server host, if i wanted to distribute the software, so that the user wouldn't have to set it up, i would either need to strike a deal with a host company to allow my users on the site or would have to create my own host in the future. I'm just thinking about it now.

Thanks all again,

Bensley

Quote:

---

Originally Posted by Schmidt

I could show you an example, how such "local Offline-SQLite-DB with online-Syncing" could look like (using the free Airtable-Service) -
but you'd need to switch to VB6, when you want it "copy&paste-able")...

---

Hey Olaf, i just read your message again and realised you said you have a version i could look at in VB6, if that is still on offer i'd love to look at that, I can do conversions between the two, it'll give me an understanding of how the communications works.

Thanks,

Bensley