Writing applications for different databases

[krill]

Hi chaps! I need some advice

At present I have an application that uses vb 6 as the front end and MS Access as the back end. So far no issue! But my clients are asking if we can write the application to run over multiple databases eg. SQL, Sybase and Oracle (My compeditors claim that there system can). Originally when I wrote this program I thought if I just past SQL statements via ADO back to the database that it would be easier later to port it to another database. But recently I have discover this does work very well, in that SQL statements that talk to Access don't always like talking to other databases such as SQL Server. Especially if you use cross tab queries etc. One chap was telling me if I talk only to stored procedures I shou;d be able to run it over any database, but are stored procedures the same from Oracle to SQL Server and Access doesn't even have them (Do they?). Anyhow if anybody has any thoughts about this please let me know!

Regards


Gerard

 

[cdukes]

What you really need is to ensure that your application is written as a multi-tiers application. The VB front end should know nothing about the source of the data and retrive data from objects. The middle data teir should provide the data access, this code contains all the SQL and any other data Update, Select or delete code. The third teir is then the database.

If you truely want your application to run on any database, you WILL be restricted to the lowest common denominator of all the databases. Thus, something that you can do in access should not be used unless it can be used in all the other databases. You could get around this by supplying the facility if the database is access, but excluding access to the facility in other databases.

As you have seen, there are differences between Access and SQLServer. There are even more between SQLServer and Oracle. You must be very carefull in writting SQL if you wish to use it against other databases.

Oracle does have stored procedures and stored procdures are a good way of making an application cross database, but not all databases have them.

Hope this helps

Chris Dukes

 

[krill]

Is any good sources to research this multi-tiers approach. Is this the way that other software companies writes application for multiple databases, or do they have a separate application geared around each individual database. For example I have one company claiming that there application can run on Access, SQL Server and Sybase.

 

[cdukes]

You can do it either way. If you write a separate application for each database, you have the problem of when you update one of the applications, you must make the same changes to the code of all the other applications. This can be a bit of a maintainance nightmare.

The three tier approach should eliminate this in that the core application is identical for each DB.

The tier approach would generally have a separate DLL for access to each of the databases. The DLL containing all the database specific code.

There are many articles on designing Multi-tierd applications, Look up MSDN on Three-Tier to give you an idea,

Chris Dukes