Are split databases a chimera of utility?

[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"

 

[Jeff Boyce]

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

 

[Chris O'C via AccessMonster.com]

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]

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?!

 

[Author]

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

 

[Dale Fye]

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.

 

[Chris O'C via AccessMonster.com]

You update data in a table with an update query. This will cause row level
locking (or page level locking if your db is in that mode) for each row that
you're updating while updating it, but it won't require a table lock.

dim db as database
set db = currentdb
db.execute "update ReportData ...", dbfailonerror

If all the data needs to be replaced, delete the rows in the table first and
append the rows located in the table you wanted to overwrite the report's
table with.

dim db as database
set db = currentdb
db.execute "delete * from ReportData", dbfailonerror
db.execute "update ReportData ...", dbfailonerror

If you overwrite a table, you need a table lock to keep others out while your
operation completes. It doesn't really matter if you overwrite the table
with the same structure or not. It's the table lock itself that's important
when dealing with contention issues. You can't get your needed table lock if
someone else already has a row level lock because a row is being updated.

Chris
Microsoft MVP

 

[jacksonmacd]

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.