Splitting Database Didn't Fix It

[Author]

Previously, a report was run by 15 users each of which ran it several times a
day. The report used a query as its record source, and the query pulled from
three ODBC tables with 100,000+ records in each table. To make it faster, I
designed a Make-Table Query to write the data, made it so the data would be
refreshed frequently, and redesigned the report to display the table data.
My next big problem arose when I had the report open, and got an error
message when the query attempted to overwrite the table with refreshed data.
"Split the database!" came the cries from the learned. And split it I did.
But I get the same message:
"Run-Time Error '3009'. You tried to lock table while opening it, but the
table cannot be locked because it is currently in use. Wait a moment and
then try the operation again."
I should be able to modify the BE data to my heart's content; that's why I
split the database in the first place. I still get Run-Time Error 3009 when
I have the FE report open and I overwrite the BE table. There's something
simple I'm probably missing. Help?

 

[Klatuu]

If you are using an ODCB database, it would be much faster if you use either
a Pass Through query or a View in you external database engine rather than
what you are doing.

 

[Albert D. Kallal]

To make it faster, I
designed a Make-Table Query to write the data, made it so the data would
be
refreshed frequently

Who runs that that data process? Everyone, or just one user?

I should be able to modify the BE data to my heart's content; that's why I
split the database in the first place.

No, if a table is open, it make snot a hill of beans if you split. on the
other hand, if you pull the data into a table in the front end...there your
problem is elmoanted here.

it is just a qeuston of who runs that "data transfer" process. As
mentioend, you likey get the best perfoamcne by running a pass-through query
espeically if your odbc data source is some type of sql server...you speed
should be good, and you not have to mess with all that data transfter
danceing that you doing now....


I still get Run-Time Error 3009 when

I have the FE report open and I overwrite the BE table. There's something
simple I'm probably missing. Help?

I never seen any post anywhere that said you can modify the structure of a
table while people are using it. I don't know where you got the strange idea
that splitting would allow you to modify a table that is in-use. Splitting
changes nothing in this regards.

if each user runs the data transfer process, then you could in theory pull
the data into the front end. However, due to bloat and huge file growth when
you do this, I would recommend pulling the data into a temp mdb that
resides on EACH pc, but then again, the pass-query idea might be a solution
for you here....

 

[a a r o n . k e m p f]

yah no shit.. splitting doesn't fix anything

it just makes it more complex.. and more difficult to do anything.

Honestly-- Splitting is not the reccomended move-- and it hasnt' been
for a decade.

Just upsize to SQL Server, SQL 2005 or 2008 Express works _GREAT_ with
15 users
It would proabbly work best if you used Access 2007 (runtime) though

If you want it done quickly; i'd find some kid off of CraigsList to do
some of the heavy lifting for you.

-Aaron

 

[a a r o n . k e m p f]

yah no shit.. splitting doesn't fix anything

it just makes it more complex.. and more difficult to do anything.

Honestly-- Splitting is not the reccomended move-- and it hasnt' been
for a decade.

Just upsize to SQL Server, SQL 2005 or 2008 Express works _GREAT_ with
15 users
It would proabbly work best if you used Access 2007 (runtime) though

If you want it done quickly; i'd find some kid off of CraigsList to do
some of the heavy lifting for you.

-Aaron

 

[Larry Linson]

Previously, a report was run by 15 users each of which
ran it several times a day. The report used a query as its
record source, and the query pulled from three ODBC
tables with 100,000+ records in each table.

Your tables (unless you are using ODBC on a "desktop database") are in a
server database, on a server in your LAN. To improve performance, you need
to assure that processing and manipulation are done, as much as possible, in
the server database on the server. You can create a view, to force SELECTION
and MANIPULATION to be done in the server database on the server, so you
will minimize the amount of data being transmitted on the LAN's bandwidth
(and, unsurprisingly, almost certainly improving performance). An
alternative, but unlikely to be either as easy as a View, or any better
performer, would be to use a pass-through Query.

To make it faster, I designed a Make-Table Query to write
the data, made it so the data would be refreshed frequently,
and redesigned the report to display the table data.

Retrieving all the data from a server is likely to severely impact bandwidth
on your LAN. Making a local table out of a mass of server data is never (at
least, almost never) the prescription for improving performance. In fact,
guaranteeing yourself that massive amounts of data are retrieved over the
LAN before selecting only the Records needed is like finding a doctor to
write you a prescription for performance degradation.

My next big problem arose when I had the report open, and got an error
message when the query attempted to overwrite the table with refreshed data.
"Split the database!" came the cries from the learned. And split it I

did.

You've complicated the situation with the local table. Splitting is a good
idea for multiuser Access databases, in general, but in your case, you need
to follow the advice and create a View to force processing on the server.

But I get the same message:
"Run-Time Error '3009'. You tried to lock table while opening it, but the
table cannot be locked because it is currently in use. Wait a moment and
then try the operation again."

I should be able to modify the BE data to my heart's
content; that's why I split the database in the first place.

Now, what makes you so strong in this belief? Being able to modify the data
depends on quite a number of factors, and splitting only helps when doing
team development. A much more likely scenario, in your situation, is that
one of your forms or the report has the data locked.

Larry Linson
Microsoft Office Access MVP

 

[Larry Linson]

yah no shit.. splitting doesn't fix anything

No, you are correct that it does not _if the tables in question are already
in a server database_ -- as they seem to be by the description as ODBC
tables.

it just makes it more complex.. and more
difficult to do anything.

It has no such effect -- never did, and doesn't now.

Honestly-- Splitting is not the reccomended
move-- and it hasnt' been for a decade.

Splitting for multi-user Jet/ACE backends was the recommended "move" from
the beginning, has never ceased to be the recommended "move", and is still
the recommended "move". It simply does not apply to client applications to
server databases, as this seems to be from the poster's description.

Just upsize to SQL Server, SQL 2005 or 2008
Express works _GREAT_ with 15 users. It would
proabbly work best if you used Access 2007
(runtime) though

The poster appears to have already done so, though perhaps to some other
server database. It would be interesting to see your reasoning on the latter
part of your statement about using the Access 2007 runtime, though. There's
really nothing about using Access 2007 that should provide a performance
boost in the situation as described.

If you want it done quickly; i'd find some kid
off of CraigsList to do some of the heavy
lifting for you.

The poster seems to have already been quite capable of creating a client
application and only asked for advice on avoiding locking. Unfortunately,
someone seems to have given him extremely poor advice on how to improve it
(download to a local table) and split, instead of forcing the work to be
done at the server to limit the selected records transmitted across the LAN.

Larry Linson
Microsoft Office Access MVP

 

[a a r o n _ k e m p f]

jet isn't reccomended, never has been.. never will be

_ESPECIALLY_ for SQL Server data

ADP is a much more direct interface.. it doesn't take 10 lines of code
to bind a form to a sproc.
and if you're not using sprocs you need to go elsewhere

SQL 2005 would give the performance boost.. you see.. when you're in
ADP land-- you can benefit from performance improvements in 2
different tiers-- so not only is it inherently faster-- because you're
using ALL of the resources of TWO machines..

it's more scalable because you can swap out EITHER Machine to make it
faster.

Of course, it all depends on what you're trying to do

 

[a a r o n _ k e m p f]

avoiding locking problems = using Access Data Projects

it's not extra work to filter on the server side.. it is merely
another location to add a filter-- it is quite convienent to do it in
two places-- depending on what you're trying to do.

I've usually filtered using the .ServerFilter property to limit the
records so that Susie can only see her records.
and then Susie can still filter and sort- on the clientside-

try doing _THAT_ in Jet, period!

-Aaron

 

[Author]

This is why I didn't make any changes to the "real" database. When I
previously posted my issue about modifying a table while it is in use, I was
led to believe that splitting the database would solve the problem. After
much meditation, I see why this "new way" of doing things won't work. You
can't rewrite the table consistently, because people will be viewing it in a
report. Also, you waste bandwidth retrieving data from the server (and the
query was set to repeat every 3 minutes). And you depend on the PC
responsible for constantly refreshing the table data to be bug-free 24/7/365.
I will now commence my search on what a 'View' is. Before I dive into my
library of tutorials and helpful websites- Any preliminary suggestions on
what to avoid/remember?
You have my thanks, as does everyone who contributed in this thread (and
others). As a self-taught user with 3 weeks of experience who owes
everything he knows about SQL, VBA, and databases to online tutorials all I
can say is...
Thanks!

 

[a a r o n . k e m p f]

create view vwEmployees
as
Select (select FirstName + ' ' + LastName from employees subq where
subq.empid = e.managerid) as mgrName, *
from employees e

 

[Author]

Thank you, Aaron. However, I get the error "Syntax error in CREATE TABLE
statement" -but I used CREATE VIEW syntax. Ideas?

 

[Klatuu]

In the simplest terms, a View in SQL Server is analogous to an Access Stored
Query.
Once you have created your view in your SQL Server database, you connect to
it through an ODBC connection just as you would a table. Access, in fact,
sees it as a table.

 

[Author]

I think I know my problem. I'm fairly certian I'm not running on SQL Server.
I don't know what exactly I'm running on, but I'm pretty sure it's not SQL
Server. That might account for the CREATE TABLE error I get when I use
CREATE VIEW syntax.
The problem, she remains. <slap forehead>

 

[Klatuu]

If you go to your immediate window in the VB editor and type in:
?Currentdb.TableDefs("NameOfTheTableGoesHere").Connect
The results will tell you what kind of back end you have. If you look for
something that says DRIVER=
What follows will tell you what kind of database it is.

 

[Michael Gramelspacher]

Thank you, Aaron. However, I get the error "Syntax error in CREATE TABLE
statement" -but I used CREATE VIEW syntax. Ideas?

Try putting a comma between the two table names in the subquery.

 

[Author]

I copied and pasted that in the immediate window (After replacing
"Name...Here" with a table inside the database) and pressed Enter, and
nothing happened. I also tried putting it inside a MsgBox within the Sub and
I got a blank message box.
Curiouser and curiouser...

 

[a a r o n _ k e m p f]

SubQ is actually an ALIAS for the employees table
I name tables in all my subqueries SubQ, SubQ2, SubQ3, etc

 

[Klatuu]

Sounds like maybe it is not a linked table at all, but a local table. You
can verify with this:
?Len(Currentdb.Tabledefs("NameOfTable").Connect)

If it returns 0, then it is a local table. That means you did not split it
after all.

 

[Larry Linson]

My assumption was based on your description of the large tables as "ODBC
tables" -- you can't access Jet/ACE tables via ODBC from Access, no matter
where they are located. I have not had occasion to create a VIEW in SQL
Server, but have dug out the documentation on a number of other server
databases and created them. They are, as has already been stated, a SELECT
statement that is roughly equivalent to an Access/Jet/ACE saved Query, but
which via ODBC, Access sees as though it were a Table.

But, an ODBC table would, indeed, have a Connect property, which, from your
description, may not be the case. How did you decide that these were "ODBC
tables"?

Larry Linson
Microsoft Office Access MVP