Re: Are split databases a chimera of utility?

Pete,

Not sure this is exactly what you are looking for, but I'll give it a shot.

If you have a report that is based on a query that takes an inordinate
amount of time to run, and are not concerned with how current the data in the
report is (once a day refresh is fine), then what I have done in the past is
create a new table which has the structure needed for your report.

Then, I add a field (rpt_xxx_Updated_at, DateTime) to my db_Parameters table
(have one of these for almost every database). Then, when someone wants to
run this report, I check to see whether the value in this field is equal to
todays date. If it is not, then I run a couple of lines of code that delete
the old data and insert the new data into this table and update the field
with todays date. It might look something like:

Private Sub cmd_Report_Click

Dim dtLastUpdated as date

dtLastUpdated = NZ(DLOOKUP("rpt_xxx_updated_at", "db_Parameters"), 0)
if dtLastUpdate <> Date() Then
currentdb.execute "DELETE * FROM tbl_rpt_xxx"
currentdb.execute "qry_rpt_xxx_Insert"
currentdb.execute "Update db_Parameters " _
& "SET rpt_xxx_updated_at = #" & date() & "#"
endif
docmd.openreport "rpt_xxx"

End Sub

This way, only the first person who runs the report actually generates the
data for the report. If you need the report to be more specific, then use
the WHERE clause of the OpenReport method to restrict the reports result set.


--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



"Author" wrote:

I'm a hybrid of a purchasing agent and half-baked DB guy. I spent a week
reading tutorials about access and SQL and learned more than my boss, who
heretofore knew more than anyone else did about how our database runs.

Here's the skinny, Chris. I had the idea to change a report's structure
from *running a lengthy query each time it is opened by one of the users* to
*reading data from a table that gets refreshed by an automated query*. Now
my question to you (i.e. the forum) is twofold:
1. How can I update data in a table without incurring Run-Time Error 3009
"Lock table while in use"?
2. If I overwrite a table with a table that has an identical structure (only
different data), is it really modifying the structure?
I hate to argue semantics, but this situation has me peeved. Not your
fault, I know. Chris, thank you for your response. I wish you and all who
read this a fantastic weekend.
-Pete

"Chris O'C via AccessMonster.com" wrote:

No one can modify a database table's structure while it's in use. Not even
you. Not in SQL Server, not in mysql, not in Oracle, not even in Access.

By splitting your db, you gain the advantages of speed for users accessing
the db, speed for developers in development and maintenance, exclusive access
to the front end db when necessary, ease of data backups and restores,
separation of processes for individual users, and you avoid many chances for
corruption. Furthermore you get a chance to see how good a db developer you
are. If performance improves after a db split in a multiuser db, you're good,
if performance degrades after a db split, well...

Chris
Microsoft MVP


Author wrote:
Are they just the tool of a false god?

If I can't modify a table's structure while it is in use,
then what's the point?

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-tablesdbdesign/200808/1


.

Relevant Pages

  • Re: SQL for Counting 1 total of 4 fields - normalization issue onl
    ... peoplein each group from the union query, ... The first report grouped by. ... Chris F ... "Duane Hookom" wrote: ...
    (microsoft.public.access.queries)
  • Re: SQL for Counting 1 total of 4 fields - normalization issue onl
    ... query output fields. ... Here are the fields I wanted for the report: ... Chris F ... "Duane Hookom" wrote: ...
    (microsoft.public.access.queries)
  • Re: Populating a list -- table structure?
    ... I had made a report already and figured out about adding the ... your responce below, but thanks to your help with SQL, I was able to get the ... It takes a summary from a select query and gives the ... KitID, long integer ...
    (microsoft.public.access.forms)
  • Re: Fields in Label dont give correct information
    ... You need to create a query based on tblRegEvent and tblRegPayment. ... -If your DCountis to count values in your report, ... half the time with very few errors (other than that label problem I had.) ... For example in the Room assignment on the report an idividual will be ...
    (microsoft.public.access.reports)
  • Re: Tips on domain aggregate replacements
    ... This already is a split db, but the users are 400 miles away from the server hosting the data:) They are all running the same copy of the FE locally on a single terminal server via TS/RDP sessions. ... It doesn't seem to have much impact on performance; the report takes just about as long to run if they are all logged in as it does when I am logged on testing it at night. ... I would do DSums from the controls on the report on the data returned by the query. ... The biggest offender is a certain report that needs to Sum a particular complex total for each of the next twelve months (the DSum in VBA was a sideline to this issue) and present these as items on each line of output. ...
    (microsoft.public.access.modulesdaovba)
Loading