How to use MS Access as Middleware with QODBC

Problem Description:

Any errors related to the driver and custom software or packaged software that does not work properly with QODBC.Example, Prepared Statements in JDBCODBCDriver.

Sample
 Connection qbConn = null;
 PreparedStatement psStmt = null;
 Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
 con = DriverManager.getConnection("jdbc:odbc:QRemote")
 cusPstmt = qbConn.prepareStatement("insert into ItemSubtotal (Name,ItemDesc,IsActive) values (?,?,?)");

when I execute the above code at line 5 it throws an error as below.

java.sql.SQLException: [Microsoft][ODBC Driver Manager] Driver does not support this function

Few of the features of ODBC are not support on QODBC, what is the workaround?

Solutions:

You are using a function/feature which is not supported by QODBC driver or maybe it's not a standard ODBC call. To overcome the issue, you can use MS-Access as middleware, i.e. Create linked tables in MS-Access, create a DSN of your ms access database, connect your application to MS-Access DSN.

Your application will connect to MS-Access, MS-Access has linked table connecting to QRemote or QODBC.

First of all, you need to create the linked table in MS Access. Please refer below mentioned article for creating the linked table in MS Access.

For 32-bit MS Access: How to Configure QODBC to Work With 32-bit MS Access
For 64-bit MS Access: How to Configure QODBC to Work With 64-bit MS Access
 

Assuming you have created an MS Access file and have created linked tables.

Let us create an MS Access DSN that will point to Access database file which we had created in the above steps. Please follow below steps for creating MS Access DSN.

1) Please create new MS Access DSN from:
Start>>All Programs>> QODBC Driver for use with QuickBooks>> Configure QODBC Data Source>>Go To "System DSN" Tab>>Click on "Add"

2) Choose "Microsoft Access Driver (*.mdb, *.accdb)" & click on "Finish" button.

3) Enter DSN name & description & click on select.

4) Provide MS Access database file location which we created earlier & click on "OK".

5) Click on "OK" to save DSN.

6) Newly created DSN is available in DSN list.

7) Now we will use this DSN as a Middleware. We will test this DSN with our VB Demo application. Please open VB Demo from:
Start>>All Programs>> QODBC Driver for use with QuickBooks>>VB Demo
Please click on Connection>>Add New Connection:

8) Please select newly created DSN from Machine Data Source & click on "OK".

9) You can see we are able to connect to MS Access DSN from VB Demo application. Please write your SQL statement & click on "Query".

You can see we are able to fetch data with MS Access DSN from VB Demo application. 

NOTE: In this example, we are using VB Demo for testing purpose, Once you are able to connect and query using VB Demo, you should be able to use the MS Access DSN ("Access Middleware") on your application.

For 64-bit MS Access: How to Configure QODBC to Work With 64-bit MS Access

Please Note 64-bit MS Access article contains information about Import table, Please choose "Link to the data source by creating a linked table" option for creating the linked table.

For 64-bit : Start>>All Programs>> QODBC Driver for use with QuickBooks>> Configure QODBC Data Source 64-bit>>Go To "System DSN" Tab>>Click on "Add"