At 12:23 AM 24/11/2004 +0800, you wrote:

>Hi
>
>I am curious to know what is involved in converting Access 97 apps to FB?
>
>How are VBA procedures "handled"? (I realise that such a question is
>rather ambiguous, but I am hoping for some useful feedback).

By you.

"An Access application" consists of a database and client application code
that are (usually) very tightly bound together. (Of course, people do
write applications for Access databases using Delphi, VB, etc., that avoid
this binding).

"A Firebird application" is one or more client programs that connect to one
or more Firebird databases and converse with them. The application code
never steps inside the database.

Inside a database, you can define program code as stored procedures and
triggers. These modules belong to the database, not to any specific client
program, and are available to all client apps, even those you haven't
written yet.

There are tools available to convert the database schema and to pump the
data -- although that would be a starting point rather than an end point
for converting from an Access schema. You can browse the Downloads |
Contributed sections at www.ibphoenix.com for links to these.

Data types in Access are quite idiosyncratic; object identifiers may need
very specific attention; standard SQL and Firebird itself have a pretty
broad collection of reserved keywords (like Type and Date) that need
special treatment if you want to keep them.

Your mission, should you choose to accept it, is to break out your Access
application cleanly into the three layers (client code, database and server
code) and re-architect it accordingly.

>What, if any, are the pitfalls of Access' sql compared to FB's
>implementation?

Access's SQL is not a standard implementation. Rather, it is an SQL-like
layer over Access's own Jet DB engine. It is pretty quirky. One thing
you've probably noticed is that Access requires a lot of nested brackets in
search clauses to implement the underlying Jet logic.

Firebird doesn't allow nested queries of the Access style SELECT....FROM
(SELECT....(FROM SELECT....). Most named (saved) subqueries can be
replaced by views, although Firebird has the selectable stored procedure
that is sometimes a more efficient way to produce these heavily nested sets.

Access also allows macro calls to behave like function calls....clause
ordering is non-standard...and so on...how much work you'll have to make
Access's SQL honest depends a lot on how much these "features" have been
employed. That said, it's a reasonably straightforward task. LOL.

For your procedure code, the PSQL language is a pretty straightforward
structured language that's easy to get into for a BASIC programmer - once
you get used to strict data typing!

Function code - that which just takes a set of typed constants or non-SQL
expressions as arguments and returns a result - can be rebuilt as external
functions ("UDFs") and used in SQL expressions. A lot of this kind of code
is already available in existing UDF libraries. When you need something
particularly idiosyncratic, you can roll your own.

Reporting is an interesting area. It's possible to define Access reports
that use Firebird as the back end, with ODBC and/or ADO in between. They
won't be the same Access reports you already have defined, since the
generic interface layers strip things down to a fairly low common
denominator and the specific ODBC or ADO drivers then re-enable some of the
native functionality of the DBMS. How you do reports really depends on the
client development tools you plan to use.

Clients for Firebird databases should be prepared to be very good at
handling exceptions. Moving from an ISAM architecture to one that is
purpose-designed for concurrent multi-user requires a fairly major
mindshift to accommodate the concept of transaction isolation. You can
never assume that a request is going to happen for certain - so your client
code has to be pretty good at intercepting and interpreting failures and
deciding what to do about it.

Because of client/server's distinct layering, you'll need to find a better
way if your application code depends on creating and destroying tables and
indexes on the fly. Firebird doesn't (currently) support temporary tables.

>Are there any "guides"/"tutorials available for such conversions?

Not really, although some of the conversion tools do provide help files to
assist you with your schema, to some degree. You're not looking at a
find-and-replace exercise. Really, just arm yourself with a good basic
reference on standard SQL, be prepared to abandon your existing query code,
except as a guideline, and ask here when you get stuck. The better SQL
references will describe what the various constructs are meant to achieve
and provide examples of use.

./heLen