Possible for T-SQL pass-through query in Access to include a local Access table?

Question

I am working on an Access DB, which have ODBC linked SQL Server table and local table, and I have following tsql script (as VB script) in Access DB. I am wondering if the tsql query can be made up of one linked table and one local Access table, instead of all are from linked tables. I know SQL Server does not know anything about my local data, but I just wonder if this coding can work or not. If yes, how should the following codes be modified? If no, that means I need to move the local table to SQL Server first? Appreciate if someone can advise. Thanks a lot.

The following section tries to update linked table from local table.

qryd.SQL = "
UPDATE dbo.linked_table 
SET dbo.linked_table.field = '" & [local_access_table]![access_field] & "' 
FROM dbo.linked_table 
INNER JOIN " & [local_access_table] & " 
ON ( '" & [local_access_table]![access_field] & "' = dbo.linked_table.field ) 
WHERE dbo.linked_table.field = 'value'
"

OR vice versa, this section below tries to update local table from linked table.

qryd.SQL = "
UPDATE " & [local_access_table] & "
SET '" & [local_access_table]![access_field] & "' = dbo.linked_table.field
FROM " & [local_access_table] & "
INNER JOIN dbo.linked_table
ON ( dbo.linked_table.field = '" & [local_access_table]![access_field] & "' ) 
WHERE '" & [local_access_table]![access_field] & "' = 'value'
"

Maybe my mindset is too much affected by Access, so I have other similar question. In Access, a select query(created in local Access) can be easily included in other Access query, I am wondering if this concept can be applied to tsql query? Take this as an example.

qryd.SQL = "
UPDATE dbo.linked_table 
SET dbo.linked_table.field = '" & [local_access_query]![query_field] & "' 
FROM dbo.linked_table 
INNER JOIN " & [local_access_query] & " 
ON ( '" & [local_access_query]![query_field] & "' = dbo.linked_table.field ) 
WHERE dbo.linked_table.field = 'value'
"

Thanks a lot!

First off, are you aware that "T-SQL" ("Transact-SQL") is the dialect of SQL that runs on Microsoft SQL Server, "Access SQL" is the dialect that the Access Database Engine uses, and that they are *not* the same? — Gord Thompson, Sep 26 '15 at 18:50
Hi @GordThompson, thanks for comment and help. Yes, I know T-SQL is to be run on SQL Server, and different from Access SQL. Though I have some basic experience in Access for few years, but I am totally new to T-SQL, SQL Server and also VB, so I don't know how much they can work in Access when T-SQL is coded with VB. Thanks. — Will, Sep 26 '15 at 18:59
What do you mean by: "I have following tsql script (as VB script) in Access DB"? Though related programming base, Access VBA is handled differently than VB or VB script. For instance, code is usually triggered through Access objects (forms, queries, reports) and not directly run through a console. Is the TSQL externally located and called from Access? Or is this code in an Access VBA module? Or behind a form or report? — Parfait, Sep 26 '15 at 20:15

score 3 · Accepted Answer · edited May 23 '17 at 11:44

Yes, it is technically possible to make Access tables and views available to T-SQL code running on a SQL Server instance. That can be done by tweaking the SQL Server configuration and then

adding the Access database as a "Linked Server" in SQL Server, or
using OPENROWSET() in the T-SQL code runnning on the SQL Server.

However, those approaches are rarely used in production systems because the SQL Server tweaks required (details here) can potentially degrade the performance and stability of the SQL Server instance itself, and because the resulting T-SQL code can face performance "challenges" similar to those faced by Access SQL queries that use ODBC linked tables.

In my opinion if there is a real and ongoing need to use a particular table in processes that run on the SQL Server then the table should reside on the SQL Server. Any Access processes that need to work with that table can still do so by using an ODBC linked table.

Possible for T-SQL pass-through query in Access to include a local Access table?

1 Answers1