Re: Are split databases a chimera of utility?

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: crash on virtual dimensions
    ... SQL authentication doesn't work for SQL Server to anywhere. ... It is basically a way for access developers to start writing all of their ...
    (microsoft.public.sqlserver.olap)
  • Re: Opinions on 3rd party code generators?
    ... The high level documentation appears to suggest they're ... How had is it to write Select InvoiceID, ... They claim you don't need to know SQL to use their tools. ... developers don't understand SQL. ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Profiler Security
    ... > development point of view alot of companies need to give ... > want to assign the developers the sys admin sql role. ... Why can't Profiler run without logging in as Admin? ...
    (microsoft.public.sqlserver.security)
  • Re: Visual Query Builder (Was: Its Official: CodeGear EnjoyingDouble-Digit Profit Rate)
    ... developers is a 'pretty' selling feature. ... inferior solution, I invite you to think of ways it could give you a ... code completion for SQL in there for good measure. ... CodeGear Developer Network: http://dn.codegear.com ...
    (borland.public.delphi.non-technical)
  • Re: Security - give developers read access to prod DBs
    ... > We have a test SQL 2000 server where most developers have their own SQL ... > databases on the prod SQL instance where all development databases are ...
    (microsoft.public.sqlserver.server)