0

I am currently trying to write a pass through query using VBA that connects to an oracle database. Using the answer provided from SQL Server Passthrough query as basis for a DAO recordset in Access as a starting poing, I have the following VBA code.

Option Compare Database

Sub Test_PassThroughQuery()

Dim qdf As DAO.QueryDef, rst As DAO.Recordset
Set qdf = CurrentDb.CreateQueryDef("")
qdf.Connect = "ODBC;DSN=database_name;UID=username;PWD=password;DBQ=ADPR;DBA=W;APA=T;EXC=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;BTD=F;BNF=F;BAM=IfAllSuccessful;NUM=NLS;DPM=F;MTS=T;MDI=F;CSR=F;FWC=F;FBS=64000;TLO=O;MLD=0;ODA=F;"
qdf.SQL = "SELECT * FROM DATE_TABLE"
qdf.ReturnsRecords = True
Set rst = qdf.OpenRecordset
Debug.Print rst
rst.Close
Set rst = Nothing
Set qdf = Nothing

End Sub

However, this prompts an error Type mismatch on the Debug.Print rst.

For the connection string I am using the ODBC connection string from the Property tab.

EDIT Am I calling the Debug.print rst line incorrectly?

Raptor776
  • 190
  • 1
  • 5
  • 15
  • 1
    Eh... Do you have a field named `qryTest` in that table? You need to enter a field name there. – Erik A Mar 15 '18 at 15:39
  • Whoops sorry i just realized that I removed that in my copy paste. Let me put the adjusted code with the error that I am receiving. – Raptor776 Mar 15 '18 at 15:40
  • What do you want to do with the passthrough query? I have the feeling you don't actually want to open a recordset at all – Erik A Mar 15 '18 at 15:46
  • You're correct. Currently I just want to display the same data as I would if I ran the query. Moving forward I want to save the data into a local table. The problem is I was hoping to first get a working pass through query with VBA and work off of that. – Raptor776 Mar 15 '18 at 16:02
  • `Debug.Print rst(0)`will work (and print the first field of first row) if records are fetched, `rst`can't be printed as it is not a string (what causes the error `Type mismatch`), it's a recordset, similar to an array. – ComputerVersteher Mar 15 '18 at 19:05

1 Answers1

2

There are many ways to create pass-through queries. If you want to save a pass-through query in Access, you can set the first parameter of CreateQueryDef:

Sub Test_PassThroughQuery()
    Dim qdf As DAO.QueryDef, rst As DAO.Recordset
    Set qdf = CurrentDb.CreateQueryDef("MyPassthroughQuery")
    qdf.Connect = "ODBC;DSN=database_name;UID=username;PWD=password;DBQ=ADPR;DBA=W;APA=T;EXC=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;BTD=F;BNF=F;BAM=IfAllSuccessful;NUM=NLS;DPM=F;MTS=T;MDI=F;CSR=F;FWC=F;FBS=64000;TLO=O;MLD=0;ODA=F;"
    qdf.SQL = "SELECT * FROM DATE_TABLE"
    qdf.ReturnsRecords = True
    DoCmd.OpenQuery "MyPassthroughQuery"
End Sub

This creates a saved query, and opens it.

You could also query an external data source in Access, which allows you to use the query designer, and use local tables and external data in a single query:

SELECT *
FROM [ODBC;DSN=database_name;UID=username;PWD=password;DBQ=ADPR;DBA=W;APA=T;EXC=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;BTD=F;BNF=F;BAM=IfAllSuccessful;NUM=NLS;DPM=F;MTS=T;MDI=F;CSR=F;FWC=F;FBS=64000;TLO=O;MLD=0;ODA=F;].DATE_TABLE
Erik A
  • 31,639
  • 12
  • 42
  • 67
  • i also did before DoCmd.OpenQuery id did: DoCmd.SetWarnings True, and after query DoCmd.SetWarnings False. also i did DoCmd.DeleteObject acQuery,"MyPassthroughQuery". maybe it is possible to take the connection from an existing linked table: set db = CurrentDb db.TableDefs("TABLE NAME").Connect it is usually equals to "ODBC;DSN=database_name"; – Shimon Doodkin Nov 01 '18 at 13:59