Re: Supporting a variety of database types in an app?

Tech-Archive recommends: Fix windows errors by optimizing your registry


"tcruise" <u32170@uwe> wrote in message news:6e8e0f370bf11@xxxxxx
I have a VB6 application that currently allows the user to create and
access
multiple Access databases (each database is for a different project), via
an
DSN-less ADO connection.
Users can create a new database or access an existing database. Once a
database has been created/accessed, it appers in a list of available
databases that can be selected from a drop-down list in the Logon dialog.
New
databases are created by specifying a database name and the working
directory
for the new database (a template .mdb file is copied to the specified
location and renamed).

To locate/select an existing database, the user navigates to the location
of
the .mdb file.
The path/filename of each database is stored in a table in a master
database.
The connection parameters used to connect to Access databases are:
DBQ: Path/filename of the .mdb file.
DEFAULTDIR: Path specified in DBQ (no filename).
DRIVER: {Microsoft Access Driver (*.mdb)}
FIL: {MS Access}
MAXBUFFERSIZE: 8192

I want to add the ability to create and access MS SQL databases. I could
use
the database filename extension (.mdb OR .mdf) to identify which type of
database the user has selected, and build the correct connection
parameters
on-the-fly (using a set of default values for each db type), depending on
which type of database the user has selected.

Is this the best way to accomplish support for more than one type of
database
in an application?
I'd especially like to hear from anyone who has implemented a similar
scheme.


This is a topic far wider in scope and complexity than you might initially
realize. And therefore there isn't any single 'best way'. Nor is it a topic
easily answered in this media.

It is possible to create a 'generic' ADO scenario using one or two common
providers (say OLE DB over ODBC) that will work with multiple data sources.
After all, that is essentially what ADO was designed for. But eventually you
will run into 'database' specific issues (wildcards in SQL, date/time
formats, authenication/authorizing, datatypes, etc.) and performance
problems.

The same goes with trying to determine what kind of datasource a user might
be selecting just from the name (or in the case of Vista, from just its
'location'). While local file-based datasources are easier to detect, I
think you will find that some additional user interaction and plain-ole
trial 'n error is in your future.

The first step to maintaining your sanity, is to back up and refactor your
application to support a n-tier architecture. Employ VB's version of
polymorphism by substituting your current data access scheme with
Interfaces. So as to more efficiently handle the database-specific nuances
that are sure to arrive.

hth
-ralph



.

Relevant Pages

  • Re: mail merge with word 2003
    ... field window, insert all the required fields, close the ... When selecting the database you could specify a range name in your Excel ... MS Office Community discussion/newsgroups via Web Browser ...
    (microsoft.public.office.misc)
  • RE: On exit, update a search?
    ... database, I won't use it as this gets quite large. ... > primary key field to appear in the combo box list. ... Test the combo box by selecting a ... > In place of Combo25, put the name of your combo box that you noted down ...
    (microsoft.public.access.forms)
  • Re: SelectedItem.Value
    ... When the page originally loads, the record is displayed using literals and ... turned off on the literals and on for the text boxes and the drop down list. ... > Are you checking that the page has not been posted back before selecting ... >> I have a system to select the correct record from the database and load ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: cant save changes in design view (form)
    ... Allen Browne - Microsoft MVP. ... compile the database, ... After hitting Ctrl G ... ... give me the option of selecting the various contents and the "compile" ...
    (microsoft.public.access.queries)
  • Re: Crystal report problem
    ... EVERY record in the database, not the selections made by the query. ... >> Data Source ... >> Look carefully at the DataSource, UserID and Integrated Security values. ... then you can change them by selecting the source from ...
    (microsoft.public.dotnet.languages.vb)