Are split databases a chimera of utility?

  • Thread starter Thread starter Author
  • Start date Start date
A

Author

Are they just the tool of a false god? I split my database, and all I got
was this lousy backend. Front End on each user's C:\ Drive. Back End on the
shared network. My Front End runs a query that replaces a table's contents
with updated data. If I can't modify a table's structure while it is in use,
then what's the point? Excuse me while I tinker with this thing, and pardon
me if I seem jaded.
inb4 SQL Server...
Poster: "My wife left me"
Aaron: "Switch to SQL Server"
 
It would be an unlikely application that required modifying table structure
"on the fly", so I'll assume you are referring to modifying table structure
during development.

One approach I've used, if the ultimate table structure is still "in flux",
is to work with a single file, saving the "split" for after the design is
settled. The split can then be to an Access/Jet Back-end, or a SQL-Server
Back-end, or a ...., whatever is appropriate for the system/user
requirements.

If I subsequently need to alter the back-end structure, I do so in the
back-end, where ever that is, then re-link any altered tables.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Jeff,
The structure of the report is not going to change. "Run-Time Error 3009:
You tried to lock table 'tblWhySplitDatbasesAnnoyMe' while opening it, but it
was in use by another user. Go online to rant for a while, and then try the
operation again."
All I'm trying to do is this:
Sub SendRefreshedDataToBackEnd()
DoCmd.CopyObject "db1_be", "ReportData", acTable, "TempReportData"
End Sub

If I could just put the TempReportData into the Backend ReportData table
that everyone runs their reports on, I'd be golden. The responses I've
gotten when posting this question have been "Well of course, you can't modify
a table's structure while it's in use!" Well, if I split a database to make
this possible and you say it's not possible, then why did I have the bowl,
Jeff? Why did I have the bowl?!
 
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
 
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.
 
Perhaps I misunderstand your intention, but it sounds like you are
attemping to create a temporary table to be used as the basis of a
report. If that's the case, and if the temporary table is intended
*only* for that single user, then it is quite acceptable to store the
table in the local (FE) database. No need to store the temp table in
the shared BE.

Even better, create a "temp" MDB file on the local machine, and store
the table in *that* MDB file to avoid "churn" in the FE. A single FE
can use tables from multiple BE's.
 
Back
Top