Microsoft Visual Basic 6.0 - Remote Database connection at run-time

How to programmatically manipulate Database connection using VB 6.0


vby6 home
Visual Basic 6.0

Keep track of a MS-Access 2003 Database connection from within your vb 6.0 app.

Introduction

Microsoft Access Database 2003 works great with Microsoft Visual Basic 6.0 if you knew what technology to use to establish a stable connection. ADO, RDO and DAO are techs. that are used to successfully establish a connection to a MS-Access Database.

Deciding which tech. to use is the trick, let's briefly descripe each one's use, shall we!

Stands for ActiveX Data Object introduced by Microsoft in October,1996 and it is a part of the MDAC package and it allows software writers to work (Connect, Manipulate and manage) with various Database types (MS-Access, SqlServer, MySql and .... etc)

Stands for Data Access Objects and it used to work with Databases, introduced in Dec,1992 as VT Objects. Version DAO 3.5 worked fine with ODBC to bypass Data to the Jet Engine. Version 3.6 was the last developed DAO by Microsoft and they say "It won't be available in the future 64-bit OS.

Stands for Remote Data Objects and it used to work with Databases only through ODBC, introduced by Microsoft to enable software developers to work with Databases through Visual Basic 4,5 and finally 6 was the last supported language.

Why we want to track

Microsoft Access Database is a *.mdb file or *.accdb stand-alone file and it has its own physical form as MS-Windows OS file, it could be deleted and cut to another directory. While working with a Database file that your application is based on, you always need to define the *.mdb or *.accdb path (I.e : C:\Dir\file.mdb) in the application to it could be accessable while transferring Data from and into the Database file. Once the Database connection is set to some path and the project was packed as EXE then there is no way to change the Database path to another path, unless you developed a routine to accomplish this task at the Run-Time.

Creating Database Manager

A Database manager from winthin your EXE is a very useful way to always be able to change the Database path or the Database name to any other path or name for any reason while working with your application as EXE, it gives you options and power not to be restricted to just one path or just one name. Also, very important, if you decided to move your Database file to a shared directory on a Network to be used by some users, you will be able to accomplish this task easily now, without the need to re-code the source. We will create a Visual Basic Windows Application example to demonstrate this idea practically, the example will be limited to the use of MS-Access 2003 Database and how to create a Database manager for it locally and on a local Network using ADO 2.8.

The source code will be available for downloading directly in the end of the lesson on Mediafire.com link, but it's important to understand what is going on.

Application Project

We will create a visual basic 6.0 Windows application project that :

    1. Connects to MS-Access Database *.mdb from any location (Locally or through Network)
    2. Connects to MS-Access Database *.mdb at any name given (Changing the Database name at Run-time)
    3. Enables us to work with *.mdb file at Run-Time more dynamically (Be able to use the same connection as your default connection)

The project design :

remote access from vb6

How does it work
It works like that :

We create any MS-Access Database file *.mdb and place it anywhere locally or in a Network and try to connect to it, change the name of the Database and re-connect to it. The beautiful thing about this is that whatever changes you make to the Database, the application will always accept these changes and keep these changes as the default Database settings so you won't need to repeatedly set the connection settings. We will be able to do that using FSO or File System Object library help ... How ?

We will create a log.txt file and keep it in the project main folder and use it to always manipulate the connection string and variables at the Run-Time.

Code Highlights

I'm using two libraries :

1) Microsoft Scripting Runtime
2) Microsoft ActiveX Data Objects 2.8

- Code, to check if the log.txt file exists or not while loading the application, if found then <Read>, if not then <Create it> and <Write to it> :

- Code, to write the connection string to the log.txt file after connecting :

Download

Here is the download link to the project example

Mediafire.com link