Visual Basic 6.0: What are some of the ways to access/share Access data through a "server"?

Question

I plan to write a program in Visual Basic 6.0 (let's call it the client) that can be installed on different computers. What I want to do is have the installed clients able to access and share the same data (Access) from the same source: that is, an online server, or as a last resort, my computer. If it helps, I don't mind starting simple: The people who will be using the client program will be my family who shares the same workspace as with my computer. I have never done anything net or server-related, and I'm not sure where I should start.

Can someone please help me out or point me in the right direction? Thank you in advance!

Answer 1

1: You create a program to access the database locally. 2: You broadcast that data with simple HTML or winsock connections, to your clients.

VB6 does this easily and fine, without the headache of .net and it's 7-unique versions that require your clients to all open-up all sorts of additional security holes.

Your local program is the only program that needs to talk directly to that connected (non-networked) database or flat-file (text) database. This is the foundation of any web-server.

For better ease of access, use any web-server with PHP built-in. It has an internal SQLite included, as that is the primary core that helps make PHP what it is. It extends those abilities to you, through PHP. (However, now you are back to opening-up, at least one computer, to exposure again.)

Best possible solution, in my opinion, is to use the INET control and WAMP (Windows Apache MySQL and PHP) all in one package. You get the bonus of true MySQL, and SQLite in one.

Answer 2

RDS

We used to have some good infrastructure for this very thing in the Windows DNA era Remote Data Service.

Sadly, users often failed to take the trouble to secure it properly. This lead WinDiv to disable it by default. Then along came .Net and the DevDiv boys piled on, calling it "deprecated." Windows 8 wounds the poor misunderstood neglected beast even further.

Among other things this gave your a standard server that could use Jet MDBs as the data store without sacrificing ADO as your data access library at the client. Both DCOM within LANs and HTTP/HTTPS across WANs are supported transport protocols.

This provided a generic middle tier for datastores including Jet MDBs, SQL Server, and nearly anything else.

I'd completely ignore ACE as irrelevant to any real purpose, it buys you very little over standard Jet.

C-S DBMS

In the wake of the destruction you can always roll your own alternatives. However where RDS required more than casual programming background and sloppy technique, creating your own alternative is even less of an option for the average guy. So much so that almost everyone suggests using a clent-server DBMS instead.

What its fans don't admit is that this can be almost as bad. If not done properly it introduces security woes of its own and can lead to huge volumes of network traffic that result in unusable performance.

Middle Tier

In the end you need to adopt a development pattern where the database is front-ended by a middle tier that limits access and the operations it exposes where data is transferred with as little chattiness and volume as possible. This usually means targeted rather than full-table queries and the use of cached disconnected data and batch-update techniques that only submit changed items back to the server.

Because of firewall realities this generally means some sort of a web service, though this can be as simple as blobs of XML, JSON, or arbitrary text shipped back and forth over HTTP. There is no need for SOAP and in the long run SOAP turned out to be a poor idea in itself.

Oh Well...

So we're left with a good deal of careful "engineering" required. Sadly if people hadn't treated RDS in such a cavalier fashion much of that wouldn't be necessary today. What would be nice is some cross-platform alternative that did not depend on the whims of Microsoft for ongoing development and support.

There just isn't a good way to do what you ask without significant planning and development.

Answer 3

You cannot place the database on your server unless users are part of you local area network (LAN). You thus cannot do this “over the internet” with a file based database as I will shortly explain why.

I also strongly suggest that you consider using something newer then VB6. The last version of VB6 was 1998, and that is VERY long time ago of 16 years ago! I don’t know of anyone considering to start developing new software using such an old system. We taking about a system that was released BEFORE windows XP!

Today there are free editions of vb.net. Not only is the free edition of Visual Studio far better the VB6, but it also supports x64 bit computing. And vb.net is far better for consuming web services etc. And learning + using such a system then opens the doors for you to use the SAME language to build web sites (asp.net + vb.net), or say write software for windows phones.

I can no more recommend using VB6 today then suggesting that you go back and adopt windows 98 (which pre-dates windows XP and is the SAME timeframe as VB6).

Having noted the above, the following advice applies equally well to vb.net or VB6.

You can certainly use the Access database system with VB6 or vb.net.

As for sharing you database with multiple users?

Since we talking about a file based system as opposed to a server based database, then you do not connect to such a data base but MUST be able to open the file. This distinction between connecting vs opening the file is MOST important. You don’t connect to a word file, you OPEN the word file.

Same goes for Access - you don’t connect, but OPEN the Access file.

Since Access is file based like when opening word, or PowerPoint, you thus require the WINDOWS File and NETWORKING SYSTEM.

This means that you cannot place the Access data file on a web server or in the cloud and have the client program installed on each desktop use that data. The simple reason for this limitation is that you opening a file and the client program needs the ability to read and write PARTS OF the file.

This removes the ability to use cloud drive systems like OneDrive/SkyDrive, or one of the many web based “cloud drive” sharing systems. And this means you cannot use SharePoint document folders either.

The reason why you cannot use such drive sharing systems is they don't use the windows networking system. Such web systems can ONLY download from the web site a WHOLE file.

When using Access databases the client program needs the ability to read bits and parts of the file (track and sectors). So full use of the remote hard drive is required. You need DIRECT USE of the remote hard drive.

So web based file systems such as "FTP" or http etc. will not work. The client program needs to OPEN the file (not download the whole file). So a web site URL will not work or web shared folder will not work.

It is in theory possible to extend the windows file and networking system OVER the internet. This kind of windows file extension is called a VPN (Virtual Private network) and in general when used over the internet is too slow and not reliable enough.

In closing:

You cannot use the Access Database engine (ACE) over the internet.

You CAN use Access database engine (ACE) on a local area network with a command shared folder. That shared folder MUST be part of a windows network share and requires full use of the windows networking system.

Since you ONLY using the Access database (engine), and not the wonderful forms + reports available in Access, then LITTLE reason exists to use Access as the back end database.

If your application is typical used as a single user standalone application installed on one computer along with the database file, then Access most certainly makes sense as a choice.

However, if you need multi-user, then again Access can be a legitimate choice for a few users at the same time.

However if any kind of data sharing needs to occur over the internet? Then choosing a free edition of SQL server for the database makes the most sense.

So Access for the database system ONLY makes senses for a local area network, and now with free SQL server, then even for workgroups then again SQL server likely a better choice.

In fact even for single user applications, the compact edition of SQL server is likely a BETTER choice with vb.net. the compact edition of SQL server is a file based system that is NOT multi-user. The result of this approach is then you can use the SAME software and in cases where you want to use cloud based SQL (SQL running on Azure), or need mutli-users, then the SAME application without code changes could be used for all 3 cases (single user, multi-user on your LAN, multi-user with database in the cloud).

However I really cool way to share such data is to use Access 2010 as your development tool, and then use the low cost office 365. This setup will let you hook up many users, is multi-user and the office 365 Account only costs $6 - (The $6 includes up to 500 invited users for free).