Is is possible to use an SQL statement to reference tables in databases residing on other computers?

I am hoping to avoid linking tables - but it may not be possible.

TIA,

t

You can play with the "IN" clause, though linked tables would be more efficient. More info in Help, but the basics:

FROM tableexpression IN
{path | ["path" "type"] | ["" [type; DATABASE = path]]}

Thanks for the reply and for the lead - I will try it out.

I was afraid there might be a performance hit - I wonder how big. My experience with linked tables in access 2000 was that they didn't tolerate network hiccups well. That was when I switched all my transactions from linked tables initially. But now I find myself needing to do a WHERE NOT EXISTS from a local table to a remote table.

I guess if you assign the the links in VBA and then break them when finished it might be ok. Is that what most people do to make the links a bit more dynamic and network fault tolerant?

I haven't had any problems with network hiccups over a wired network. I have heard that Access does not work well over a wireless network. Is yours wireless?

... not wireless, but the client computers are on carts and frequently disconnected.

t

Terry,

I'd opt for using Linked tables. You can refresh the links dynamically when the user's
hook their machines up to the network. There are plenty of samples here; look for "TableDef" and
"RefreshLink".

I think that if you use the syntax:

Select * From SomeTable In 'C:\SomeDir\SomeDB.MDB'

there can be only ONE "In" clause in a statement.

Wayne

I've tried to time both methods just to see how big of a performance hit there is using the 'IN' method vs linked tables. But, they both complete in under a second and I can't figure out a way to time to tenths or hundreds of a sec.

But thank you both for your suggestions.

t

Terry,

It's just a computer. Put your test query inside a loop and run it a
thousand times or so. That should give you an idea which method
is faster.

Wayne

1000 loops - Good Call!

Setup:
-Source table is in an access database on a computer connected via 100/baseT LAN, is a flat table (not a query), has 7895 records in 10 fields.
-SQL query where clause matches 51 records from source table and is in general form below:
INSERT INTO ... (all 10 fields)
SELECT all 10 fields
FROM ... [IN ...]
WHERE 3 "=" conditions AND
NOT EXISTS (SELECT)
-For each connection method (IN clause vs. linked table), two loops of 1000 DoCmd.RunSQL statements are run. In the first 1000, the destination table is cleared each time so that all 51 records are recreated. In the second 1000, the destination table is left untouched so that no records are recreated, but more records must be evaluated by the NOT EXISTS clause (which is the more common scenario in this application).

Here are the results:
IN clause - empty local table - 1000 loops - 64 seconds
IN clause - full local table - 1000 loops - 67 seconds
linked table - empty local table - 1000 loops - 59 seconds
linked table - full local table - 1000 loops - 62 seconds

----

So, it seems clear that the performance hit is minimal. As expected, in both scenarios, it takes longer to compare a full table than to populate an empty one. Unless anyone can point out something that has skewed the results; then given the simplicity of coding afforded by the IN clause vs. repointing linked tables, I am inclined to stick with the IN method.

I really appreciate the various suggestions.

t