Linking to tables in Firebird or other db

[Jennifer Carr]

I have an Access 2003 application that is split into two .mdb files for
the interfaces and data. This app is distributed on a CD to be used on
computers that have no internet access. When someone needs an updated
dataset the backend .mdb is refreshed from a load of Oracle tables and
sent on CD so that the user only needs to replace a single file. The
front end links to the tables in the back end.

The problem is that the amount of data required is steadily growing and
has now surpassed the 2GB file size limit. I need to leave the front
end in Access as this is a big nasty dog of an app written by someone
else and I don't have time to tackle a rewrite right now (as much as
I'd love to). What I'd like to find, ideally, is an open-source
database that can be distributed as a file or set of files that Access
can link to. As I have no control over machine configurations I'd
prefer to stay away from creating DSNs, installing software or
registering DLLs if possible. I've been playing with Firebird and it
looks like it would work admirably if I could work out how to get
Access to link to its tables but haven't had much luck so far. I'd be
grateful for any ideas or suggestions.

[Randy Harris]

"Jennifer Carr" <js****@gmail.com> wrote in message
news:11*********************@g43g2000cwa.googlegro ups.com...

I have an Access 2003 application that is split into two .mdb files for
the interfaces and data. This app is distributed on a CD to be used on
computers that have no internet access. When someone needs an updated
dataset the backend .mdb is refreshed from a load of Oracle tables and
sent on CD so that the user only needs to replace a single file. The
front end links to the tables in the back end.

The problem is that the amount of data required is steadily growing and
has now surpassed the 2GB file size limit. I need to leave the front
end in Access as this is a big nasty dog of an app written by someone
else and I don't have time to tackle a rewrite right now (as much as
I'd love to). What I'd like to find, ideally, is an open-source
database that can be distributed as a file or set of files that Access
can link to. As I have no control over machine configurations I'd
prefer to stay away from creating DSNs, installing software or
registering DLLs if possible. I've been playing with Firebird and it
looks like it would work admirably if I could work out how to get
Access to link to its tables but haven't had much luck so far. I'd be
grateful for any ideas or suggestions.

I don't know for certain if it will meet your needs, but you might want to
take a look at PostgreSQL. It's open source and quite scalable.

--
Randy Harris
I'm pretty sure I know everything that I can remember.

[Jennifer Carr]

Thanks for your reply, Randy. I wish I could draw diagrams in here to
clarify my structure, but I'll try to verbalize it better. I need
something that can be distributed as a standalone data file that
Microsoft Access can use to retrieve data. The data should live in this
data file and be linked from Access, the same way that you can link
tables from other Access containers or ODBC data sources. I've also
been looking at MySQL and PostgreSQL but it appears, unless I'm missing
something (likely) that neither can be distributed as a standalone file
db. Neither can the desktop SQL Server (MSDE ...or SQL Server Express
now).

The reason I'm looking so closely at Firebird is that those folks do
provide an "embedded" version that can be distributed as a data file
and a .dll, but I can't work out how to make that work in Access
without registering the .dll on the machine.

I'm extremely hesitant to try to install anything on user machines
because I don't have access to or control over them. I know that some
of them are still running Win98, which locks out MSDE as it requires,
at minimum, Win2000. Some of them are also locked down administratively
so that the users can't install software or have access to machine
configuration, which means that trying to create a DSN or register a
DLL could be tricky.

I welcome any and all suggestions. Gratefully.

[Lyle Fairfield]

I don't, of course, understand your situation completely. I am guessing
you need something (for a backend) that is more efficient for storage
than Access so that it can fit on a CD.
If that's the problem and if I were trying to solve the problem I would
consider DBF / X-Base / FoxPro / FoxBase / Clipper / DBase files.
The older versions of these with which I worked were very simple fixed
length , header dependent text files. As such they were extremely
compressible with ZIP. They also had many indexing options including
maintaining indexes as separate files. I would look at distributing the
data without indexes and, if I could, indexing the tables later.
Access will, TTBOMR link to most dbf files.
Having an all MS solution might be an advantage; I don't know.
I'm not so much recommending this as pointing it out as a possibility
that you did not mention.
I don't know Firebird and am not trying to suggest that it wouldn't be
good.

[Larry Linson]

Access can link to ODBC-compliant databases provided you have ODBC drivers
for that database. I've used Access as a client to Informix

The new MS SQL Server 2005 Express does not have the 2 GB limit, and
Microsoft has said it would be free at least for the next year. The machines
using it, however, have to have the latest .NET framework installed.

Another alternative is to split the data/backend DB into multiple databases,
but you cannot establish a relationship between tables in two different
MDBs.

Larry Linson
Microsoft Access MVP

"Jennifer Carr" <js****@gmail.com> wrote in message
news:11*********************@g43g2000cwa.googlegro ups.com...

I have an Access 2003 application that is split into two .mdb files for
the interfaces and data. This app is distributed on a CD to be used on
computers that have no internet access. When someone needs an updated
dataset the backend .mdb is refreshed from a load of Oracle tables and
sent on CD so that the user only needs to replace a single file. The
front end links to the tables in the back end.

The problem is that the amount of data required is steadily growing and
has now surpassed the 2GB file size limit. I need to leave the front
end in Access as this is a big nasty dog of an app written by someone
else and I don't have time to tackle a rewrite right now (as much as
I'd love to). What I'd like to find, ideally, is an open-source
database that can be distributed as a file or set of files that Access
can link to. As I have no control over machine configurations I'd
prefer to stay away from creating DSNs, installing software or
registering DLLs if possible. I've been playing with Firebird and it
looks like it would work admirably if I could work out how to get
Access to link to its tables but haven't had much luck so far. I'd be
grateful for any ideas or suggestions.

[Jennifer Carr]

Yeah, 'fraid so. The culprit here is over 1.6GB and continuously
growing so anything I do in that direction would only be a temporary
measure.

[Jennifer Carr]

Interesting suggestions, Lyle, thanks! I'll have a closer look at them
when I get to work this morning.

Currently we .cab the .mdb to distribute it on a CD and that works
pretty well. ZIP would work as well. My biggest concern is to get
this data out of the .mdb in a format that Access can still use without
having to rewrite the app (lots and lots of embedded queries and form
dependancies).

Lyle Fairfield wrote:

I don't, of course, understand your situation completely. I am guessing
you need something (for a backend) that is more efficient for storage
than Access so that it can fit on a CD.
If that's the problem and if I were trying to solve the problem I would
consider DBF / X-Base / FoxPro / FoxBase / Clipper / DBase files.
The older versions of these with which I worked were very simple fixed
length , header dependent text files. As such they were extremely
compressible with ZIP. They also had many indexing options including
maintaining indexes as separate files. I would look at distributing the
data without indexes and, if I could, indexing the tables later.
Access will, TTBOMR link to most dbf files.

[Tim Marshall]

Jennifer Carr wrote:

I have an Access 2003 application that is split into two .mdb files for
the interfaces and data. This app is distributed on a CD to be used on
computers that have no internet access. When someone needs an updated
dataset the backend .mdb is refreshed from a load of Oracle tables and
sent on CD so that the user only needs to replace a single file. The
front end links to the tables in the back end.

I work with oracle extensively and find Access to be a perfect front end
platform for it. I've also designed a number of apps in the past that
do exactly what you describe here, ie, take a "snapshot" of the Oracle
tables so that the data is in MS Jet (the Access native database
engine). Without knowing the nature of your Oracle tables or their
size, it seems to me the 1.62 GB mdb size is pretty scary, but here's a
simple thing you may have done already:

Do you compact the data mdb after the Oracle data is "refreshed"? How
the "refresh" is done can account for a very large mdb bloat.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[Steve Jorgensen]

On 12 Dec 2005 14:33:36 -0800, "Jennifer Carr" <js****@gmail.com> wrote:

I have an Access 2003 application that is split into two .mdb files for
the interfaces and data. This app is distributed on a CD to be used on
computers that have no internet access. When someone needs an updated
dataset the backend .mdb is refreshed from a load of Oracle tables and
sent on CD so that the user only needs to replace a single file. The
front end links to the tables in the back end.

The problem is that the amount of data required is steadily growing and
has now surpassed the 2GB file size limit. I need to leave the front
end in Access as this is a big nasty dog of an app written by someone
else and I don't have time to tackle a rewrite right now (as much as
I'd love to). What I'd like to find, ideally, is an open-source
database that can be distributed as a file or set of files that Access
can link to. As I have no control over machine configurations I'd
prefer to stay away from creating DSNs, installing software or
registering DLLs if possible. I've been playing with Firebird and it
looks like it would work admirably if I could work out how to get
Access to link to its tables but haven't had much luck so far. I'd be
grateful for any ideas or suggestions.

When I have had such problems in the past, I've generally found a way to split
the data across multiple MDB files. There's no relational integrity between
tables across the back-end file split, but if the split is designed carefully,
the potential consequence of that can be kept small.

[Randy Harris]

In your original post, you mention that your Access back end was exceeding
2GB. Do you have huge tables? Could you simply use more than one BE mdb
file?

--
Randy Harris
I'm pretty sure I know everything that I can remember.
"Jennifer Carr" <js****@gmail.com> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...

Thanks for your reply, Randy. I wish I could draw diagrams in here to
clarify my structure, but I'll try to verbalize it better. I need
something that can be distributed as a standalone data file that
Microsoft Access can use to retrieve data. The data should live in this
data file and be linked from Access, the same way that you can link
tables from other Access containers or ODBC data sources. I've also
been looking at MySQL and PostgreSQL but it appears, unless I'm missing
something (likely) that neither can be distributed as a standalone file
db. Neither can the desktop SQL Server (MSDE ...or SQL Server Express
now).

The reason I'm looking so closely at Firebird is that those folks do
provide an "embedded" version that can be distributed as a data file
and a .dll, but I can't work out how to make that work in Access
without registering the .dll on the machine.

I'm extremely hesitant to try to install anything on user machines
because I don't have access to or control over them. I know that some
of them are still running Win98, which locks out MSDE as it requires,
at minimum, Win2000. Some of them are also locked down administratively
so that the users can't install software or have access to machine
configuration, which means that trying to create a DSN or register a
DLL could be tricky.

I welcome any and all suggestions. Gratefully.

[Jennifer Carr]

Folks, I just wanted to pop back in to say thanks for your thoughts and
ideas, and to let you know that I did find a resolution for this issue.
After much throat clearing on my part the rest of the team has agreed
that dropping support for Win98 and installing another ODBC driver on
the user machines isn't thaaaat bad an idea, so I knocked together a
prototype using the Firebird standalone and it does the job perfectly.
Access links to the tables in Firebird just like any other ODBC
datasource. The nice thing is that the ONLY thing I have to install is
the driver and since Firebird has a 64TB database size limit I'm
thinking this is a long-term solution. *grin*

Again, thanks much for your time and thoughts. I greatly appreciate it.