shared access front end

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

JRBB - 12 Oct 2005 19:35 GMT

I am developing an access front-end/back-end system for our agents in China
and US. We have set up a Remote Desktop in Windows Small Business Server
2003. What we are trying to do is have install only one copy of front-end in
the Remote Desktop and have all users login the same front-end (MDB). The
problem I have is the form/report is not display the data correctly if two
users are access the same local table or same form/report. Is there any
solution for this? Or I have to install the front end for each user?

FYI, we are using Access 2003 as front-end and Informix database as backend.

The reason that we have MDB for front-end is the file is too big to create a
MBE files. What is the max size for a MDB to convert into a MDE file?

Can I pass any parameters while opening a MDB?

Signature

Thank you,

JRBB

Reply to this Message

Larry Linson - 13 Oct 2005 01:04 GMT

> I am developing an access front-end/back-end
> system for our agents in China and US. We have
[quoted text clipped - 6 lines]
> if two users are access the same local table or
> same form/report.

Another problem that you are very likely to have is frequent corruption, the
probability of which is significantly increased when multiple users log in
to the same front-end or monolithic database.

> Is there any solution for this?
> Or I have to install the front end for each user?

Yes, that is the strong recommendation that you will no doubt have seen
posted many times in this and other Access newsgroups. That recommendation
has not changed, no matter how much "easier" it seems to share the
front-end.

> FYI, we are using Access 2003 as front-end
> and Informix database as backend.

In Access terms, "multiuser" refers to the environment with a split Access
database linked to tables in another Access database. What you have is
specifically known as client-server... Access client to the Informix server.
As you have also indicated you are using MDB, that implies that you are also
using ODBC, so I suggest you would likely get better answers in the
"microsoft.public.access.odbcclientsvr" newsgroup.

> The reason that we have MDB for front-end is the
> file is too big to create a MBE files. What is the
> max size for a MDB to convert into a MDE file?

I am not aware of such a limitation. Have you Compacted the MDB? How big is
it? The size limit for an Access 2000 or more recent MDB is 2 GB.

> Can I pass any parameters while opening a MDB?

Yes, there are a number of "command line parameters". Search Help on that
term for some details.

Larry Linson
Microsoft Access MVP

Reply to this Message

JRBB - 13 Oct 2005 18:00 GMT

Thank you Larry!

We have 3 MDB files for this project. I have no problem to convert the
smallest MDB (less than 3MB) into MDE, but the other two (3.7MB & 21MB). The
error seems to do with TableID (with maximum of 2048) in Microsoft Jet
database engine version 4.0. Yes, I have all MDB files compact on close.

I had tried to look in "command line parameters", but can't find a solution
yet. As I mentioned earlier, we have 3 MDB. I like to pass vendor# from one
MDB to the other.

Signature

Thank you,

JRBB

> > I am developing an access front-end/back-end
> > system for our agents in China and US. We have
[quoted text clipped - 43 lines]
> Larry Linson
> Microsoft Access MVP

Reply to this Message

Larry Linson - 14 Oct 2005 08:29 GMT

> We have 3 MDB files for this project. I have no
> problem to convert the smallest MDB (less than
> 3MB) into MDE, but the other two (3.7MB &
> 21MB). The error seems to do with TableID
> (with maximum of 2048) in Microsoft Jet
> database engine version 4.0.

I am surprised at this problem... could you be more specific about the error
having to do with TableID? I just cannot imageine a 3MB limit on compiling
to MDE, without this newsgroup being inundated with posts about it.

> Yes, I have all MDB files compact on close.

If you have multiple users logged in to the same front-end, client, or
monlithic database, the "compact on close" can only work for the last user
to log out.

> I had tried to look in "command line parameters",
> but can't find a solution yet. As I mentioned earlier,
> we have 3 MDB. I like to pass vendor# from one
> MDB to the other.

Perhaps you can come up with a method to pass the information in a record in
a shared table. You can't call Access with arguments, quite like it were a
procedure. If there is a need to do so, then perhaps multiple MDBs was not
an appropriate design choice.

Reply to this Message

JRBB - 14 Oct 2005 23:27 GMT

> > We have 3 MDB files for this project. I have no
> > problem to convert the smallest MDB (less than
[quoted text clipped - 6 lines]
> having to do with TableID? I just cannot imageine a 3MB limit on compiling
> to MDE, without this newsgroup being inundated with posts about it.

The error message I got while trying to convert into MDE file:
Access_2578_Q289686:
This error is usually associated with compling a large database into an MDE
file. Due to the method used to compile the database, a considerable number
of TableID references are created for each table. The Microsoft Jet database
engine version 4.0 can only create a maximum of 2048 open TableID at one
time. Exporting a database as an MDE potentially can exceed this limit if
the database has a large number of objects(table, macro, form, report, etc).

There is no accurate method to estimate the number of TableIDd the Jet
database engine uses during the process of compiling a database as an MDE.
However, each VBA module and each form uses one TableID, as a result, if the
datbase has 500 forms, and each form's HasModule property is set to Yes, as
many as 1,000 TableIDs are used.

> > Yes, I have all MDB files compact on close.
>
> If you have multiple users logged in to the same front-end, client, or
> monlithic database, the "compact on close" can only work for the last user
> to log out.

Got it. I think we would have to install Front-End for each user.

> > I had tried to look in "command line parameters",
> > but can't find a solution yet. As I mentioned earlier,
[quoted text clipped - 5 lines]
> procedure. If there is a need to do so, then perhaps multiple MDBs was not
> an appropriate design choice.

Since we are not going to share Front-End, I think I cold with a shared table.

Thanks Larry,

JRBB

Reply to this Message

Larry Linson - 15 Oct 2005 22:41 GMT

Interesting -- I have not encountered this, and have compiled some
reasonably large databases, with reasonably large numbers of TableDefs
(sometimes, though, the TableDefs were mostly ODBC connections to server
DB). But if, as they say, it's not predictable, it is entirely possible I
might have that trouble on the very next time I try.

Good luck with your project. I'm sorry I was not able to be of much help.

Larry Linson
Microsoft Access MVP

>> > We have 3 MDB files for this project. I have no
>> > problem to convert the smallest MDB (less than
[quoted text clipped - 58 lines]
>
> JRBB

Reply to this Message

david@epsomdotcomdotau - 16 Oct 2005 10:51 GMT

I too find it hard to imagine a 3.7 MB db with
this problem, but one solution is to break your
code db's into multiple library mde's. A library
mde can be referenced in the same way that
olb/tlb can be referenced.

(david)

> > > We have 3 MDB files for this project. I have no
> > > problem to convert the smallest MDB (less than
[quoted text clipped - 45 lines]
>
> JRBB

Reply to this Message