MDB with only linked tables growing tremendously

[simmy]

I am working on a project that has 80 users working with an mdb file that
only has linked tables in it. Via our gui the users query, insert, delete and
update rows in the linked sql server tables. For some reason the mdb file has
been growing to a very large size which has caused errors. We are accessing
the mdb via DAO

Any ideas how we can prevent this problem?

Thanks!
Simmy

 

[gllincoln]

The cause of the growth can be fairly difficult to isolate/eliminate but a
work-around that I've used with reasonable success is to run a daily
compact/repair event in the off hours, using the Windows event scheduler to
trigger the process at 2 AM (or whatever works for the situation).

Hope this helps,
Gordon

 

[a a r o n . k e m p f]

move to SQL Server, you shouldn't have to put up with this.

Jet is too bloated for real world usage; and it has been for a decade.

-Aaron

 

[rquintal]

move to SQL Server, you shouldn't have to put up with this.

Jet is too bloated for real world usage; and it has been for a decade.

-Aaron

You should learn to read, Aaron, the O.P. said that the back end is
SQL Server.
SQL server does not have GUI tools.

Bob Q

- Show quoted text -

thisFile = replace(thisFile, "Aa","Mo")

 

[Sylvain Lafontaine]

Even when linked to a SQL-Server database, any MDB file will grow over time
and must be compacted on a regular basis.

However, some behavior make it worse. For example, if you are using
passthrough queries, you should use unamed querydefs instead of named
querydefs to perform your queries. Another possibility is that Access is
not running your queries directly on SQL-Server because they are to complex
to be translated from JET to T-SQL. This is an important problem because
when it happends, not only your MDB file grow faster but the performance
will drop like a rock. You should verify with the SQL-Server Profiler to
see that's not the problem.

 

[½£¿Í]

wewrewewerwerw

 

[½£¿Í]

wewrewewerwerw

 

[a a r o n . k e m p f]

I'm sorry.. but you said that SQL server does not have GUI tools?

Where do you come up with this crap, dude?

MSDE didn't come with an interface (EXCEPT FOR 'ACCESS DATA PROJECTS')
but SQL Server has always had a GUI.
and it's a much much much better GUI than anything available in Jet.

-Aaron

 

[a a r o n . k e m p f]

or, you could just move to 'Access Data Projects'- where you don't
have to deal with a bloated engine.

SQL Server is easier for development and faster for production.
It's a really easy equation, IMHO.

-Aaron

 

[teddysnips]

I'm sorry.. but you said that SQL server does not have GUI tools?

Where do you come up with this crap, dude?

MSDE didn't come with an interface (EXCEPT FOR 'ACCESS DATA PROJECTS')
but SQL Server has always had a GUI.
and it's a much much much better GUI than anything available in Jet.

Crap. EM is a crock. SQL Server is a terrific database, and one that
I work with all the time. But EM sucks.

 

[a a r o n . k e m p f]

EM?

Who's reccomending EM for anything?

EM does a lot for you-- but I generally use either MS Access, Query
Analyzer, or SQL Server Management Studio (I especially like the free
flavors)

 

[a a r o n . k e m p f]

ADP does forms and reports. Better than anything else, except maybe
'dreamweaver' and classic ASP.

-Aaron

"a a r o n . k e m p f @ g m a i l . c o m" <[email protected]>
wrote in
(e-mail address removed)
m:

I'm sorry.. but you said that SQL server does not have GUI tools?

Yes, and I stand by my claims.

Where do you come up with this crap, dude?

It's only crap by some m o r o n 's definition, not in reality.  

MSDE didn't come with an interface (EXCEPT FOR 'ACCESS DATA
PROJECTS') but SQL Server has always had a GUI.
and it's a much much much better GUI than anything available in
Jet.

Except that it doesn't do Forms or Reports. It does create tables
and relations, and 'better' is a subjective word. Some m o r o n s
may think it better, but many do not.

Q

 

[a a r o n _ k e m p f]

what the **** are you talking about?

"a a r o n . k e m p f @ g m a i l . c o m" <[email protected]>
wrote in
@u18g2000pro.googlegroups.co
m:



ADPis not bundled with SQL Server. It does come with some versions
of Access.

Q








strThisMsg = replace(strThisMsg,"aa","mo")
--
Bob Quintal

PA is y I've altered my email address.
- Hide quoted text -

- Show quoted text -

 

[a a r o n _ k e m p f]

SQL Server does forms and reports. Directly. It's called 'Access Data
Projects' and it is a much more efficient platform than any flavor of
Jet.
SQL Server does forms and reports. Directly. It's called 'Access Data
Projects' and it is a much more efficient platform than any flavor of
Jet.
SQL Server does forms and reports. Directly. It's called 'Access Data
Projects' and it is a much more efficient platform than any flavor of
Jet.
SQL Server does forms and reports. Directly. It's called 'Access Data
Projects' and it is a much more efficient platform than any flavor of
Jet.

"a a r o n . k e m p f @ g m a i l . c o m" <[email protected]>
wrote in
(e-mail address removed)
m:

I'm sorry.. but you said that SQL server does not have GUI tools?

Yes, and I stand by my claims.

Where do you come up with this crap, dude?

It's only crap by some m o r o n 's definition, not in reality.  

MSDE didn't come with an interface (EXCEPT FOR 'ACCESS DATA
PROJECTS') but SQL Server has always had a GUI.
and it's a much much much better GUI than anything available in
Jet.

Except that it doesn't do Forms or Reports. It does create tables
and relations, and 'better' is a subjective word. Some m o r o n s
may think it better, but many do not.

Q

 

[Carlos ansogain]

wewrewewerwerw

 

[Paul Shapiro]

If you're using Access 2007, there was a hotfix announcement that might
apply:

Issues fixed include:
.. You experience slower performance than you did in previous versions when
you share a database file over a network.
.. You may notice your database file increase in increments of 4 KB without
any user interaction when the database is open.
.. You may see a sub-form become blank after you set the main form's
recordset. You may also receive the following error: "Run-time error 2467 -
The expression you entered does not exist".
These fixes are available at http://support.microsoft.com/kb/960307.

If the mdb that's growing is the front-end, does each user execute a local
copy rather than all users running the same shared copy? I setup a shortcut
that copies the front-end db from the server to the local workstation and
then runs that shortcut. So even if the front-end grows, the user starts
with a clean copy each time they run the application.

If it's the backend db with the data that's growing, it needs to be
compacted periodically. How often depends on how much updating your users
do, but once a week might be a good start.