Compact and Repair Alternative

[AccessARS]

I have an Access 2007 Database file which is used for dynamic reporting with
a SQL Server 2005 Database back-end. The Access file resides on the users
desktop from where they run reports, in return the local query creates a
temporary table (source table) which is used to view different viewes of the
data within. The initial make table query is processing approximately 2000
records from SQL into the temp table on each run. In this type of scenario I
usually have the Access file set to Compact & Repair on close but my issue is
that upon initially openning and running the report takes a manageable 10-20
second but after the second or third run (deleting and recreating the temp
table) it takes 10-15 minutes to run the same report. When I compact and
repair the file it speeds up again but it's evident that I would need to
manage this issue while the user has the file open...considering that they
may possibly run the report 2 or 3 during each session.

Any input straight forward or unconventional would be greatly appreciated.

Thank you in advance for your time.

 

[dch3]

Unless there's a MVP lurking around with a some sage input...

I don't know that you can avoid the issue without constantly closing,
compacting and reopening. If you're repeatedly importing data from the SQL
Server database, you can't avoid the DB bloat.

If there's someway to shift the temp tables to SQLServer, I'd go there or
look at an alternative such as Crystal Reports. You may want to google
SQLServer Reports to see if any off the self products come up that may work.

The statement "which is used to view different viewes of the data within."
does catch my attention. What 'different views' are the users working with?
If you're just importing the data to change the fields selected, the sort
order, etc, I would *highly* recommend creating specific views on the server
side, as I seem to recall even though its a 'View' you can still link to it.
Not only will that solve your immediate problem, but it should improve the
query performance since its running server side as opposed to JET reaching
out to SQLServer.

 

[John W. Vinson]

I have an Access 2007 Database file which is used for dynamic reporting with
a SQL Server 2005 Database back-end. The Access file resides on the users
desktop from where they run reports, in return the local query creates a
temporary table (source table) which is used to view different viewes of the
data within. The initial make table query is processing approximately 2000
records from SQL into the temp table on each run. In this type of scenario I
usually have the Access file set to Compact & Repair on close but my issue is
that upon initially openning and running the report takes a manageable 10-20
second but after the second or third run (deleting and recreating the temp
table) it takes 10-15 minutes to run the same report. When I compact and
repair the file it speeds up again but it's evident that I would need to
manage this issue while the user has the file open...considering that they
may possibly run the report 2 or 3 during each session.

Any input straight forward or unconventional would be greatly appreciated.

Thank you in advance for your time.

I'd concur with dch3's suggestion of using server Views (perhaps in
conjunction with Access Queries).

If local temp tables are unavoidable, I'd suggest using the CreateDatabase()
method to create a new .mdb backend file, create the temp tables in it (linked
to your frontend database using TransferDatabase), and Kill'ing the
"scratchpad" backend .mdb file when you're done with it.

 

[Rick A.B.]

On Tue, 12 Aug 2008 11:04:04 -0700, AccessARS
I'd concur with dch3's suggestion of using server Views (perhaps in
conjunction with Access Queries).

If local temp tables are unavoidable, I'd suggest using the CreateDatabase()
method to create a new .mdb backend file, create the temp tables in it (linked
to your frontend database using TransferDatabase), and Kill'ing the
"scratchpad" backend .mdb file when you're done with it.
--

             John W. Vinson [MVP]- Hide quoted text -

- Show quoted text -

See Tony's website for example of creating a temp database.

http://www.granite.ab.ca/access/temptables.htm

Rick

 

[AccessARS]

This is great information that I can leverage off of but it seems like I need
to make a decision as to which approach to take...SQL or temp_Database:

-------------------------------------------------------------------------------------------

Unfortunately Crystal Reports is not in the picture at this point but
definitely a viable approach if it was available.

Regarding the “different viewsâ€â€¦ they are summarized statistical data
displayed in charts and printable reports derived from the main culprit
temp_table which is processed by passing several user defined parameters
populated in an Access GUI as input into a complex multi-table logic driven
make_table query outputting approximately 30 fields by 2000 records on each
run.

The reason for the temp table approach was due to a compiled file running
off of each users machine. There will be 20 up to 100 users with the
possibility of them running the same report simultaneously. The temp table
isolated on their machine gives me the flexibility to run the multiple
summarized breakouts from one local source without crossing users. Basically
the bulk of the functionality and time spent by the user on the front-end
lies post running the temp_table query.

In SQL…
Since each user is running their own report, wouldn’t I have to store that
file on their machine once it’s processed to avoid managing multiple
temp_tables in SQL?

can I pass through parameters to the views?

would a stored procedure used as a pass-through query work better?

 

[dch3]

You're in the fringe of my experience with SQLServer, but I do believe that
its entire possible to do the processing on the SQLServer side and then send
the results back to the local front end for further analysis. I'd look for a
SQLServer newsgroup for more specifics. Keep in mind that if you shift the
processing to the back end that it might impact all of the users depending on
the complexity, however it shouldn't be any worst than what you're doing now.

I'd find a good SQLServer DB and start chatting.

 

[AccessARS]

Thank you all for your valuable input. The temp_Database approach seems very
useful but it in my case I would have to tackle a new frontier by moving my
logic/queries to SQL to stop further complications on the Access user
interface = many late nights in my immediate future.

On that note, If any of you happen to have good links/forums/resources on
SQL Server 2007 Development it would be GREATLY appreciated...


Best regards.