Need help pleaseeee...updating tables

[erick-flores]

Hello all

I have been trying to look for the answer to my question doing some
search in the groups but no luck. I have linked a table using ODBC.
Then I made a copy of the link table to my local computer (so my
queries can run faster, it was paintful slow with the link table).
Everything works fine but the link table changes everytime (it is an
Invoice table). What I want is a way to automatically update my local
table with the link table everytime I open the database. What is the
best solution for this?

Thanks in advance

[erick-flores]

I tried the Update Query, it works...BUT its veryyyyyy slow. There has
to be another way around.

[Gord]

On Jan 24, 12:41 pm, "erick-flores" <[e-mail]:

I tried the Update Query, it works...BUT its veryyyyyy slow. There has
to be another way around.

If you want to re-create your local table as an exact duplicate of the
external table then you don't need to do an Update query. You might try
to...
- delete the contents of your local table and then do an Append query
from the linked table, as in

DELETE local_table.* FROM local_table;

INSERT INTO local_table
SELECT linked_table.*
FROM linked_table;
....or...
- use VBA code to drop the local table and then re-import the external
table using DoCmd.TransferDatabase

[Larry Linson]

"erick-flores" <er**********@gmail.comwrote

I have been trying to look for the answer to my question doing some
search in the groups but no luck. I have linked a table using ODBC.
Then I made a copy of the link table to my local computer (so my
queries can run faster, it was paintful slow with the link table).
Everything works fine but the link table changes everytime (it is an
Invoice table). What I want is a way to automatically update my local
table with the link table everytime I open the database. What is the
best solution for this?

Well-thought out designs and user interfaces often work just fine with
linked ODBC tables. Copying entire tables, or multiple tables, is rarely
the best solution for "painfully slow"... because you are often retrieving
vastly more records than you will access during the session.

Thus, I hesitate to offer suggestions for good/better ways to do something
that might well not be the way the problem should be addressed, but for what
it's worth: You'd put the code in an event of the Form you specify in the
Startup Parameters... on the menu Tools | Startup. But, I caution, you'd be
better to describe your situation in some detail because someone may have a
much better suggestion.

My experience with ODBC links to server DB tables has been that they were
surprisingly efficient, if you didn't "go out of your way" to force them to
be INefficient.

Larry Linson
Microsoft Access MVP

['69 Camaro]

Hi, Erick

I tried the Update Query, it works...BUT its veryyyyyy slow. There has
to be another way around.

Perhaps my comparison of the car that's missing an engine to a table missing
a primary key is sounding a little more apropos, especially since you're
asking how to make your car to go faster -- despite there being no engine.

http://groups.google.com/group/comp....0815c8b2c7639a

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blog: http://DataDevilDog.BlogSpot.com
http://www.Access.QBuilt.com/html/ex...ributors2.html for contact
info.
"erick-flores" <er**********@gmail.comwrote in message
news:11**********************@l53g2000cwa.googlegr oups.com...

>

I tried the Update Query, it works...BUT its veryyyyyy slow. There has
to be another way around.

[Gord]

On Jan 24, 6:09 pm, "Larry Linson" <[e-mail]:

My experience with ODBC links to server DB tables has been that they were
surprisingly efficient, if you didn't "go out of your way" to force them to
be INefficient.

That may be true for mainstream ODBC drivers, but sometimes an ODBC
driver just sucks. In my particular case it was an ODBC driver that a
vendor supplied for their Btrieve-based application. The driver was
such a dog that we had to import the entire database into Access so we
could create custom reports. If we tried to use linked tables the
reports would literally take *days* to run.

[Tim Marshall]

Gord wrote:

could create custom reports. If we tried to use linked tables the
reports would literally take *days* to run.

Did you try pass through queries?

I find for basic queries in Oracle, linked tables work just as quickly
as do pass through queries and even faster in some instances(!).
However, I prefer Pass through queries as for complex queries with many
tables and most especially, anything with subqueries tends to lock up.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Be Careful, Big Bird!" - Ditto "TIM-MAY!!" - Me

[erick-flores]

Thanks for all ur replies.

I created a Make-Table Query based on the link table. So everytime I
open the application this query will run and update my table. The link
table is now local, and my queries and reports run perfect.

I am not sure if this will give me future problem...but at least its
working for the moment, and working fine.

[Lyle Fairfield]

"erick-flores" <er**********@gmail.comwrote in
news:11**********************@v33g2000cwv.googlegr oups.com:

>
Thanks for all ur replies.

I created a Make-Table Query based on the link table. So everytime I
open the application this query will run and update my table. The link
table is now local, and my queries and reports run perfect.

I am not sure if this will give me future problem...but at least its
working for the moment, and working fine.

Why not print out (hardcopy) all the data at its source and send it to the
local client via UPS every day?

[Tim Marshall]

erick-flores wrote:

Thanks for all ur replies.

What? The ancient lost city of Ur replied?
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Be Careful, Big Bird!" - Ditto "TIM-MAY!!" - Me

[Gord]

On Jan 25, 9:59 am, Tim Marshall
<[e-mail]:

>Did you try pass through queries?

In a manner of speaking, yes. When we got beyond the most trivial of
reports and started to see "lockups" I ran tests by passing SQL queries
directly to the ODBC driver via ADO. The results indicated that as the
number of rows in the main table increased by a factor of 10, the
execution time of the query increased by a factor of 100. For one
particular test query

60 rows took 2 seconds
600 rows took 214 seconds (3.6 minutes)
6,000 rows took 20,535 seconds (5.7 hours)
so 60,000 rows would take ~2,000,000 seconds (about 23 days)

Ouch.