Splitting Database Didn't Fix It

On Wed, 13 Aug 2008 06:45:01 -0700, Author wrote:

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.

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...

"Klatuu" wrote:

If you go to your immediate window in the VB editor and type in:
?Currentdb.TableDefs("NameOfTheTableGoesHere").Con nect
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.
--
Dave Hargis, Microsoft Access MVP


"Author" wrote:

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" wrote:

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.

--
Dave Hargis, Microsoft Access MVP


"Author" wrote:

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!

"Larry Linson" wrote:

"Author" wrote

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

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




On Aug 13, 7:12*am, Michael Gramelspacher wrote:
On Wed, 13 Aug 2008 06:45:01 -0700, Author wrote:
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.

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.
--
Dave Hargis, Microsoft Access MVP


"Author" wrote:

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...

"Klatuu" wrote:

If you go to your immediate window in the VB editor and type in:
?Currentdb.TableDefs("NameOfTheTableGoesHere").Con nect
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.
--
Dave Hargis, Microsoft Access MVP


"Author" wrote:

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" wrote:

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.

--
Dave Hargis, Microsoft Access MVP


"Author" wrote:

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!

"Larry Linson" wrote:

"Author" wrote

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

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

"Author" wrote in message
...
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" wrote:

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.

--
Dave Hargis, Microsoft Access MVP


"Author" wrote:

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!

"Larry Linson" wrote:

"Author" wrote

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

"a a r o n . k e m p f @ g m a i l . c o m"
wrote in

m:

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

You are so full of horse****.

In any event, you don't do Jet development, so how the hell would
you have any idea what are best practices for Access/Jet
applications?

You can't even get it right when posting recommendations about
Access used with SQL Server, your claimed area of expertise!

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

=?Utf-8?B?QXV0aG9y?= wrote in
:

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.

No, that doesn't necessarily indicate anything. It may be that you
are running as a user that doesn't have permission to create
tables/views.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

=?Utf-8?B?QXV0aG9y?= wrote in
:

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...

Are you using ODBC for the connection? Do you have linked tables?
Are you running the command in the front end MDB?

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

"a a r o n _ k e m p f" wrote:

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

Certainly, you are trying to obscure the fact that you know less than
nothing about what you are being paid to do, so you use a naming convention
that is meaningless, content-free. Aren't you one who was bragging about how
clear your naming convention is? What a POC you are.

It is a sin and a shame that you are denser than spent uranium and just
can't realize it. Poor rattlebrain, numbskull aaron.

So Sorry Than Anyone Has to be So Stupid

I feel out of my league when you ask such questions. In the most logical
terms I can manage, I'll briefly explain how it is set up (mind you, I didn't
create it. I'm just a guy trying to make life a little easier). There are
three "linked tables" that "talk to" a computer program (ManMan, in fact) and
retrieve copious amounts of data. Users run a report (with a query as its
source data) about 50 times a day, and each time they run the report it
filters through all three of these "linked tables" (which are actually the
result of Access talking to ManMan). Now, these "linked tables" were
referred to as ODBC by my boss- whether that's right or not, I can't say.
The only table that I attempted to link between the FE and BE of my split
database was a local version of the data they were viewing (I kept the query
exactly as it was, only I typed INTO tStaticDataForReportViewing so that it
would put the data into a smaller table, resulting in instant opening of the
report rather than waiting three minutes for the query to run. Brilliant,
right? grimace) As it turns out, that plan won't work.
Anyhow, you guys are pretty sharp with your database knowledge. I just hope
the next time I have a question I'll 1.) Know what I'm talking about and 2.)
Have a problem with a solution. It turns out I was originally running the
command in the non-split version of the database, a fact that made me feel
silly and small. Thank you all for your help.
Cheers,
Me

David W. Fenton" wrote:

Are you using ODBC for the connection? Do you have linked tables?
Are you running the command in the front end MDB?

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/