Linked tables vs sub queries from another database

[HerrSober]

Hi all,

Just wondering, is there a difference performance wise on linking to a table in another database and running a Query for it? (like "SELECT * FROM tblExample in C:\example.accdb;"

Thanks for your help!
Sober

 

[Joe4]

A lot depends on where the other database is located. If it is on the same server, it is not a big deal.

Actually, it is recommended that for a multi-user database, that you actually "split" the data into two databases; a back-end that that contains all the data tables, and a front-end which contains all queries, forms, reports, and VBA. So in that split environment, ALL the table references in the front-end are linked tables.

So using linked tables should not be a problem, especially in they are on the same network.

 

[HerrSober]

A lot depends on where the other database is located. If it is on the same server, it is not a big deal.

Actually, it is recommended that for a multi-user database, that you actually "split" the data into two databases; a back-end that that contains all the data tables, and a front-end which contains all queries, forms, reports, and VBA. So in that split environment, ALL the table references in the front-end are linked tables.

So using linked tables should not be a problem, especially in they are on the same network.

Thanks for your answer Joey. I currently split the databases and refer to the table from the front end.

I see that when being in Design View, when I use linked tables via the graphical interface I can also see the primary key symbol, but when I instead refer to the table with using SQL Query I don't.

Will using the linked tables approach result in better data retrieval since it is possible with direct connection with the primary key for the tables involved?

 

[Joe4]

but when I instead refer to the table with using SQL Query I don't

I am not quite clear what you mean here. Is your data in SQL, or are you just referring to the SQL View in the query.
Regardless, when you link tables, they maintain all the same properties as they do in their native tables (you won't lose primary keys, indexes, or anything like that).

 

[HerrSober]

I am not quite clear what you mean here. Is your data in SQL, or are you just referring to the SQL View in the query.
Regardless, when you link tables, they maintain all the same properties as they do in their native tables (you won't lose primary keys, indexes, or anything like that).

I mean by refering to the table by using such SQL statement: SELECT * from tblExternal in "C:\Externaldatabase.accdb";

Still same properties as if I have used the graphical interface to create a linked table?

 

[Joe4]

It should be. All the graphical interface does is build the SQL behind the scenes. You can actually create queries by using the graphical interface or by writing the SQL code directly. It shouldn't destroy any of the properties inherent to the tables you are referencing in them.

 

[HerrSober]

It should be. All the graphical interface does is build the SQL behind the scenes. You can actually create queries by using the graphical interface or by writing the SQL code directly. It shouldn't destroy any of the properties inherent to the tables you are referencing in them.

Thanks. Excatly how I expected