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

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Ralph, thank you for your insightful response. I would be grateful if you
would read my replies and questions below and offer further unreserved
opinions.

But first, here is a clarification of my current plan...

My reasons for adding support for MS SQL Server:
1) To get experience with MS SQL Server and client/server development.
2) So I can call this a client/server application (Access is not a true db
server, so cannot use the terms "client/server" or "2-tier" when referring to
this app).
2) To give my application more "professional appeal".
3) Scalability.

My rationale for using a file-based database location/selection method:
1) Current implementation already file-based (and uses ADO -- originally used
ODBC API, but modified it to enhance performance).
2) I currently only plan to support these two databases (and only on Windows).

3) It will get the job done (both Access and SQL Server db's can be located
this way).
4) ODBC comes with a performance hit.
5) My original efforts to deliver/install a fileDSN for Access using the VB
application and deployment wizard did not work. As I recall, I was unable to
get ODBC to recognize the file-DSN (probably just needed to register the file-
DSN). I changed to a DSN-less connection model (which ADO supports).

My plan is to define DEFAULT parameter name/value pairs for SQL Server, as I
did for Access.
In addition, I will provide an interface for user to add/update/delete
Connection String parameter names/values as needed. If they goof up, they can
restore defaults.

The only way user will be able to use MS SQL Server with my application is to
go through my interface for creating a database from a template file and
establish it as an available db.
Here is the process:
1) Specify the database type (Access or SQL Server).
2) Select "Add and Existing Database" or Create a New Database".
3) If "New", specify:
Database Type: "Microsoft Access" or "Microsoft SQL Server"
Database Name
Database Location (working directory), currently limited to browsing for
existing directory
QUESTION: Since Microsoft changed name from "MS SQL Server" to "MS SQL", what
should I call it? I prefer the old name. Sybase renamed their "SQL Server"
product to "Adaptive Server", so no more name conflict. IMHO, MS should
revert to original product name for the sake of clarity.

The db server must already be running to access a SQL Server db from an app
session.
User gets a logon dialog prompting them to select a database from drop-down
list or browse for a db file. If db server is not running, user must start it
or connection will fail and they will get the logon dialog. That's as far as
I've gotton so far...

Ralph wrote:
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.
I agree, but being a solo consultant, I have no peers to confer with, nor do
I have time to attend local user group meetings.

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.
I was wondering what the pros/cons of using OLEDB might be, but have not
investigated.
This MSDN article on MDAC indicates that OLEDB is intended for working with
disparate data sources (e.g., RDBMS, flat files, email...) via the C++ Active
Template Libraries (working with COM objects).

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.
Thus far, I have only sold this application to consulting clients -- I
install and configure the system for them, but they can refer to the online
help should they decide to add/change databases in the future. I figure the
DSN-less, ".mdb" or ".mdf" only solution will work in this case, and should I
decide to offer this product for commercial sale, I should be prepared to
make revisions.

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.
That's a great idea. Thus far, I've limited my object-oriented VB programming
to working with existing classes (forms, Automation, etc.). I like the idea
of designing my own class libraries for those cases where they are warranted.
Can you recommend a good resource? I started reading a book on object-
oriented programming in VB6, but never finished it. I need to see some real-
world implementations.

hth
-ralph

Thanks, Ralph!

--
Message posted via VBMonster.com
http://www.vbmonster.com/Uwe/Forums.aspx/db-access/200703/1

.

Relevant Pages

  • Idiomatic Expressions to Operating System Architecture: Transforming Rigids
    ... <Subject: Re: Idiomatic Expressions to Operating ... <endpoint during a single session * DATABASE + by ... <statements is a new feature of SQL Server 2005. ...
    (sci.math)
  • SQL Server ODBC Driver Ignores Authentication Setting
    ... set their ODBC connections with SQL Server Authentication, ... Microsoft Data Access Components 2.6 RTM, ... authentication to log into the database. ...
    (microsoft.public.sqlserver.security)
  • Re: Linking tables access - sql server 2005
    ... Another advantage of this method is that you don't need an ODBC setting on the local computer, ... Create a linked table to SQL Server without using a DSN ... Name of the table that you are linking to on the SQL Server database ... Function AttachDSNLessTable(stLocalTableName As String, stRemoteTableName As String, stServer As String, stDatabase As String, Optional stUsername As ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Need ODBCDirect-ion
    ... Just as a side note, ODBC, or so called "jet" direct is simply a way to ... You need experience with sql server. ... "Upsizing to Microsoft SQL Server" White Paper Available in Download ... Choosing Database Tools White Paper Available in Download Cente ...
    (microsoft.public.access.modulesdaovba)
  • Re: Trusted Connection
    ... HOWEVER, for one of my users, I will create an ODBC DSN ... for my test database ON THEIR COMPUTER, ... So, when I set up the ODBC DSN connection for this user, ... >> Our SQL Server was set up NOT by me, ...
    (microsoft.public.sqlserver.security)