Pass-Through Query - Connecting to another Access Backend

[monheimx9]

Well, looks like I'm stuck

I have a Procedure that use a Pass-Through Query to connect to an SQL Server Database, this part works wonderfuly without any issues

But for some reasons, It seems that I simply can't do the same with an Access Backend instead

ODBC Connect Str:
ODBC;Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=R:\Business\Gestion\IMEx-DTP_SA_Dorsal.accdb;

When I try to run that query I get this error:
ODBC--connection to '{Microsoft Access Driver (*.mdb, *.accdb)}' failed

Context:
This query is part of a process that automatically recreate local tables when I'm updating Views or Stored Procedure on the SQL Backend
In order to succeed I need to use DAO instead of ADO to work with TableDef properly
Since there are tables and queries that I can't move yet that are still on the Access Backend I need to compromise a bit and work with said backend
This backend is accessible on our local network

My question is as follow:
Has anybody been able to run Pass-Through Queries to another Access Database ?
If that's the case, were should I look to try debugging this ?

If that's not the case, I know a workarround, but I want to be sure before I start to refactor some of my code

 

[sonic8]

Has anybody been able to run Pass-Through Queries to another Access Database ?

I don't think this is possible.

 

[GPGeorge]

There are ways to run queries in an external accdb, although I'm not aware of anyone doing so in a pass-through query. Pass-through queries are intended to allow you to send T-SQL directly to the server database, bypassing the Access query engine. I'm not sure there would be any benefit, therefore, to doing so against an accdb anyway; it's the accdb query engine in both cases.

In your case, it might look like this, using the 'IN" operator to specify the remote accdb:

SELECT FieldOne, FieldTwo
FROM YourTableNameGoesHere IN 'R:\Business\Gestion\IMEx-DTP_SA_Dorsal.accdb';

 

[DickyP]

The whole purpose of pass through queries is irrelevant against a separate Access data source, and would achieve nothing that a linked data source is designed to do.

As GPGeorge says they are designed to enable Server side processing by tSQL, MySQL, Oracle etc, and to aĺlow sending ƁE specific SQL to that particular server.

Given that Access doesn't do server side processing and you only use Access specific SQL the whole idea is a nonsense.

 

[spaLOGICng]

Echoing @GPGeorge

 

[CJ_London]

You can also use a database object set to the back end

On my phone so may not quite have the correct syntax

Dim BE as dao.database
Dim sqlstr as string
Set BE=application.opendatabase(“bepath”)
Sqlstr=“create table…….”
BE.execute sqlstr

 

[monheimx9]

Thanks for all your replies, now I know the direction I should take

There are ways to run queries in an external accdb, although I'm not aware of anyone doing so in a pass-through query. Pass-through queries are intended to allow you to send T-SQL directly to the server database, bypassing the Access query engine. I'm not sure there would be any benefit, therefore, to doing so against an accdb anyway; it's the accdb query engine in both cases.

In your case, it might look like this, using the 'IN" operator to specify the remote accdb:

SELECT FieldOne, FieldTwo
FROM YourTableNameGoesHere IN 'R:\Business\Gestion\IMEx-DTP_SA_Dorsal.accdb';

I did not know about the IN operator to query from external sources, that's interesting even though it doesn't fit my use case

The whole purpose of pass through queries is irrelevant against a separate Access data source, and would achieve nothing that a linked data source is designed to do.

As GPGeorge says they are designed to enable Server side processing by tSQL, MySQL, Oracle etc, and to aĺlow sending ƁE specific SQL to that particular server.

Given that Access doesn't do server side processing and you only use Access specific SQL the whole idea is a nonsense.

Yes that's my actual use case, I have one Pass-Through query that in update on the fly with VBA
I did use this method because it was the easiest way to dynamically get a DAO object (since my queries return rows) from an SQL backend
The reason is that DAO makes it easier to deal with field types in order to create a TableDef from a recordset
The idea was to use the same QueryDef object for both the SQL Backend and the Access Backend

But as you stated, it goes agains the database engine of MS Access, that's why I''m going to go the other way arround and get the recordset via DAO.Database object and open the remote database (as CJ_London said above)

Now it's time to refactor


Thread solved

 

[monheimx9]

PS:

Here's the example of how it's used:

Private Sub recreateTable(ByVal idTable As Long)
    Dim rsDropTables As ADODB.Recordset
    Set rsDropTables = getRecordset("SELECT * FROM DROP_TABLES WHERE ID = " & idTable, adoDbLocal)
    Dim localTableName As String
    Dim remoteStatement As String
    localTableName = rsDropTables.Fields.Item("TABLE").Value
    remoteStatement = rsDropTables.Fields.Item("SP").Value

    On Error Resume Next
    DoCmd.DeleteObject acTable, localTableName
    On Error GoTo 0

        'GET FIELDS NAME FROM STORED PROCEDURE
    Dim fld As DAO.Field
    Dim fieldname As String
    Dim fieldType As String
    Dim i As Long
    Dim a As Long
    Dim rsSQL As DAO.Recordset
    Dim qry As DAO.QueryDef
    Dim strsql As String
    Dim db As DAO.Database
    Set db = CurrentDb
    Set qry = db.QueryDefs("spDROP")
    qry.Connect = stringDAOSQLServer

    'TABLE CREATE START

    qry.SQL = remoteStatement
    Set rsSQL = db.OpenRecordset("spDROP")
    a = rsSQL.Fields.Count
    i = 0
    Dim tdf As DAO.TableDef
    Set tdf = db.CreateTableDef(localTableName)
    Do Until i = a
        fieldname = rsSQL.Fields(i).Name
        fieldType = rsSQL.Fields(i).Type
        Set fld = tdf.CreateField(fieldname, fieldType)
        tdf.Fields.Append fld
        i = i + 1
    Loop

    db.TableDefs.Append tdf
    db.TableDefs.Refresh
    Application.RefreshDatabaseWindow


    'TABLE CREATE END
End Sub

 

[Pat Hartman]

But for some reasons, It seems that I simply can't do the same with an Access Backend instead

Correct. Pass thru queries are only used with RDBMS'. Access is intimately connected with Jet/ACE and so ODBC is not used.

 

[The_Doc_Man]

From a mechanistic viewpoint, for a native Access back end file, the RDBMS program/engine that you are using is wherever you installed Access. Probably 99+% of the time it is on your local machine.

The running DB engine is neither in the front end file nor the back end file. Instead it is in either the MSACCESS.EXE file or one of its associated libraries (probably ACECORE.DLL since Access 2007). In practical terms, that means the DB is running on the machine that has an "file-type association" with the file types .MDB or .ACCDB or any of the related variants thereof.

Connecting to a back-end file on another machine only opens a data channel, not a program channel. Native Access back ends are connected not by ODBC protocol, but by SMB (server message block) protocol. SMB is a file-system protocol, not a database protocol, and thus would not understand that pass-through query. To use a native Access back end, you must go through the process of mapping the table to the file that contains it (because that is the way a file-based protocol works.