VBA & Pass through Queries

[nector]

I have created an action query which is working ok in MS Access, now I want to convert it to a Passthrough query, though at first was looking like easy but the trouble came in because of the date and form input values which are mandatory. Below is a query in Ms Access and finally the modified into a passthrough query which is not working:

Ms Access working query

INSERT INTO tblGrnDetails ( Warehouse, ProductID, Qty, Cost, GRNID, GrnStockAccName, GrnStockAcc, SuspenseName, SuspenseAcc, BSIDSTOCK, BSIDSuspense, GrnDate, Discounts, Vat, PurchasesDate, PurchaseOrderID )
SELECT tblPurchasesDetailslines.Warehouse, tblProducts.ProductID, tblPurchasesDetailslines.Quantity, tblPurchasesDetailslines.CostValue, [Forms]![frmGrn]![GRNID] AS DocID, "Stock" AS GrnstockName, "114-10-1000" AS GrnStockAccount, "GRN Suspense" AS GrnSuspenseName, "121-12-1000" AS GRNSuspenseAccount, "2" AS BSIDStock, "3" AS BSIDSuspense, Date() AS Mth, tblPurchasesDetailslines.Discounts, tblPurchasesDetailslines.Vat, tblPurchasesHeader.PurchasesDate, tblPurchasesHeader.PurchaseID
FROM tblPurchasesHeader INNER JOIN (tblWarehouse INNER JOIN (tblProducts INNER JOIN tblPurchasesDetailslines ON tblProducts.ProductID = tblPurchasesDetailslines.ProductID) ON tblWarehouse.WHID = tblProducts.WHID) ON tblPurchasesHeader.PurchaseID = tblPurchasesDetailslines.PurchaseID
WHERE (((tblPurchasesDetailslines.PurchaseID)=[Forms]![frmGrn]![CboOrder]));

Pass Through query Modified

INSERT INTO tblGrnDetails ( Warehouse, ProductID, Qty, Cost, GRNID, GrnStockAccName, GrnStockAcc, SuspenseName, SuspenseAcc, BSIDSTOCK, BSIDSuspense, GrnDate, Discounts, Vat, PurchasesDate, PurchaseOrderID )
SELECT tblPurchasesDetailslines.Warehouse, tblProducts.ProductID, tblPurchasesDetailslines.Quantity, tblPurchasesDetailslines.CostValue, [Forms]![frmGrn]![GRNID] AS DocID, 'Stock' AS GrnstockName, '114-10-1000' AS GrnStockAccount, 'GRN Suspense' AS GrnSuspenseName, '121-12-1000' AS GRNSuspenseAccount, '2' AS BSIDStock, '3' AS BSIDSuspense, Date() AS Mth, tblPurchasesDetailslines.Discounts, tblPurchasesDetailslines.Vat, tblPurchasesHeader.PurchasesDate, tblPurchasesHeader.PurchaseID
FROM tblPurchasesHeader INNER JOIN (tblWarehouse INNER JOIN (tblProducts INNER JOIN tblPurchasesDetailslines ON tblProducts.ProductID = tblPurchasesDetailslines.ProductID) ON tblWarehouse.WHID = tblProducts.WHID) ON tblPurchasesHeader.PurchaseID = tblPurchasesDetailslines.PurchaseID

The problem here is how to handle the Date() As mth and [Forms]![frmGrn]![GRNID] AS DocID , I'm complete stuck here

Required ideal move or final pass through query

On Error GoTo Err_Handler
Dim db As DAO.Database
Dim strSql As String
Set db = CurrentDb
strSql = "SELECT * FROM [PassThrough Qry] WHERE [tblPurchasesDetailslines.PurchaseID] = " & [Forms]![frmGrn]![CboOrder]
DoCmd.SetWarnings False
DoCmd.OpenQuery "PassThrough Qry"

 

[Josef P.]

A pass-through query cannot access Access elements. Their content is executed in the server.
=> assemble the SQL statement via VBA and then pass it to the PT query.

The SQL statement of the PT query must be written in the SQL dialect of the DBMS (for SQL Server: T-SQL).
Date() is not known by the SQL server. But you could create a SQL function with the name.
With GETDATE() the SQL server returns the current date incl. time.

Tip: write the SQL statement for the PT query best with the SQL Server Management Studio. Then you have IntelliSense as write support.

 

[nector]

I think my query has an error again I do not know where I'm going wrong.

On Error GoTo Err_Handler
Dim db As DAO.Database
Dim strSql As String
Set db = CurrentDb
strSql = "INSERT INTO tblGrnDetails ( Warehouse, ProductID, Qty, Cost, GRNID, GrnStockAccName, GrnStockAcc, SuspenseName, SuspenseAcc, BSIDSTOCK, BSIDSuspense, GrnDate, Discounts, Vat, PurchasesDate, PurchaseOrderID )"&_
"SELECT tblPurchasesDetailslines.Warehouse, tblProducts.ProductID, tblPurchasesDetailslines.Quantity, tblPurchasesDetailslines.CostValue"&_
, "[Forms]![frmGrn]![GRNID] AS DocID, "Stock" AS GrnstockName, "114-10-1000" AS GrnStockAccount, "GRN Suspense" AS GrnSuspenseName"&_
, ""121-12-1000" AS GRNSuspenseAccount, "2" AS BSIDStock, "3" AS BSIDSuspense, Date() AS Mth, tblPurchasesDetailslines.Discounts "&_
, "tblPurchasesDetailslines.Vat, tblPurchasesHeader.PurchasesDate, tblPurchasesHeader.PurchaseID"&_
"FROM tblPurchasesHeader INNER JOIN (tblWarehouse INNER JOIN (tblProducts INNER JOIN tblPurchasesDetailslines ON tblProducts.ProductID = tblPurchasesDetailslines.ProductID) ON tblWarehouse.WHID = tblProducts.WHID)"&_
"ON tblPurchasesHeader.PurchaseID = tblPurchasesDetailslines.PurchaseID"&_
WHERE tblPurchasesDetailslines.PurchaseID)=[Forms]![frmGrn]![CboOrder]"

 

[Minty]

You aren't concatenating things correctly (at all tbh)

Format it in such a way it's easier to read

strSQL = "INSERT INTO tblGrnDetails ( Warehouse, ProductID, Qty, Cost, GRNID, GrnStockAccName, GrnStockAcc, SuspenseName, SuspenseAcc, BSIDSTOCK, BSIDSuspense, GrnDate, Discounts, Vat, PurchasesDate, PurchaseOrderID ) "
strSQL = strSQL & " SELECT tblPurchasesDetailslines.Warehouse, tblProducts.ProductID, tblPurchasesDetailslines.Quantity, " 
strSQL = strSQL & " tblPurchasesDetailslines.CostValue,  " &  [Forms]![frmGrn]![GRNID]  & " AS DocID, 'Stock' AS GrnstockName, '114-10-1000' AS GrnStockAccount, 'GRN Suspense' AS GrnSuspenseName, "
strSQL = strSQL & " '121-12-1000' AS GRNSuspenseAccount, '2' AS BSIDStock, '3' AS BSIDSuspense, Getdate() AS Mth, tblPurchasesDetailslines.Discounts, tblPurchasesDetailslines.Vat, "
strSQL = strSQL & " tblPurchasesHeader.PurchasesDate, tblPurchasesHeader.PurchaseID "
strSQL = strSQL & " FROM tblPurchasesHeader  "
strSQL = strSQL & " INNER JOIN tblPurchasesDetailslines  ON tblPurchasesHeader.PurchaseID = tblPurchasesDetailslines.PurchaseID "
strSQL = strSQL & " INNER JOIN tblProducts ON tblProducts.ProductID = tblPurchasesDetailslines.ProductID "
strSQL = strSQL & " INNER JOIN tblWarehouse ON tblWarehouse.WHID = tblProducts.WHID "
strSQL = strSQL & " WHERE tblPurchasesDetailslines.PurchaseID)= " & [Forms]![frmGrn]![CboOrder]

Debug.Print strSQL

If the order and DocID are numbers the above should work, if not you'll need to escape them as text with single quotes.

 

[Josef P.]

I think my query has an error again I do not know where I'm going wrong.

You're not familiar with SQL Server yet, are you?

Suggestion: implement your plan in steps.
First create join statement as a view in SQL Server.
Inserting into the linked table and filtering the linked view will not be the extreme bottleneck.

1.) new view in SQL-Server (without dynamic filter - only required data)

SELECT
   PH.PurchaseID, PH.PurchasesDate
 , PL.Warehouse, PL.Quantity, PL.CostValue, PL.Discounts, PL.Vat
 , P.ProductID
FROM
    dbo.tblPurchasesHeader PH --- dbo is the right schema?
    INNER JOIN
    dbo.tblPurchasesDetailslines PL ON PL.PurchaseID = PH.PurchaseID
    INNER JOIN
    dbo.tblProducts P ON P.ProductID = PL.ProductID
    INNER JOIN
    dbo.tblWarehouse WH ON WH.WHID = P.WHID

2.) link this view as in your access client (linked 'table')

3.) Use a normal Access append query and test if that is already sufficient.

INSERT INTO tblGrnDetails ( Warehouse, ProductID, Qty, Cost, GRNID, GrnStockAccName, GrnStockAcc, SuspenseName, SuspenseAcc, BSIDSTOCK, BSIDSuspense, GrnDate, Discounts, Vat, PurchasesDate, PurchaseOrderID )
SELECT
  V.Warehouse, V.ProductID, V.Quantity, V.CostValue
, [Forms]![frmGrn]![GRNID] AS DocID, 'Stock' AS GrnstockName, '114-10-1000' AS GrnStockAccount, 'GRN Suspense' AS GrnSuspenseName
, '121-12-1000' AS GRNSuspenseAccount, '2' AS BSIDStock, '3' AS BSIDSuspense, Date() AS Mth, V.Discounts
, V.Vat, V.PurchasesDate, V.PurchaseID
FROM
   YourNewLinkedView as V
WHERE
   PurchaseID=[Forms]![frmGrn]![CboOrder]

This approach does not yet bring the best possible performance, but perhaps it will help you to get to know SQL Server better.

 

[cheekybuddha]

Why are you storing all this data again (redundantly) in tblGrnDetails when you can get with your SELECT query ?

 

[Pat Hartman]

Another question - what is the point of using a pass through query if the Access query doesn't have a problem? Access attempts to "pass through" all queries. You can prevent this by not understanding the process and by using functions that do not have a direct correlation to a T-SQL function. Date() is fine but user defined functions are never fine, nor are VBA functions that do not have a correlation to a T-SQL function.

Granted, there is some overhead with using an Access query since Access has to be involved and translate the query to t-SQL before sending it but in most cases, the hit is trivial.

 

[nector]

Sorry people I have taken too much time to reply because I was going through your details carefully and below is my final View joined with two fields

INSERT INTO tblGrnDetails ( Warehouse, ProductID, Qty, Cost, GRNID, GrnStockAccName, GrnStockAcc, SuspenseName, SuspenseAcc, BSIDSTOCK, BSIDSuspense, GrnDate, Discounts, Vat, PurchasesDate, PurchaseOrderID )
SELECT ViewGrnUpdates.Warehouse, ViewGrnUpdates.ProductID, ViewGrnUpdates.Quantity, ViewGrnUpdates.CostValue, [Forms]![frmGrn]![GRNID] AS DocID, ViewGrnUpdates.GrnstockName, ViewGrnUpdates.GrnStockAccount, ViewGrnUpdates.GrnSuspenseName, ViewGrnUpdates.GRNSuspenseAccount, ViewGrnUpdates.BSIDStock, ViewGrnUpdates.BSIDSuspense, Date() AS Mth, ViewGrnUpdates.Discounts, ViewGrnUpdates.Vat, ViewGrnUpdates.PurchasesDate, ViewGrnUpdates.PurchaseID
FROM ViewGrnUpdates
WHERE (((ViewGrnUpdates.PurchaseID)=[Forms]![frmGrn]![CboOrder]));

But there is some performance improvement , better than before

Coming to those where were asking about the GRN

Why are you storing all this data again (redundantly) in tblGrnDetails when you can get with your SELECT query ?

In accounting we copy the PO details and convert it to GRN when we receive the materials , if some materials are not received then we edit the GRN the order system we will keep tracking unreceived goods.

 

[Josef P.]

[view]
But there is some performance improvement , better than before

Then on to the next acceleration.

Try to run this statement in a pass-through-query (untested code):

dim InsertSql as String
InsertSql = "INSERT INTO dbo.tblGrnDetails (Warehouse, ProductID, Qty, Cost," & _
    "     GRNID, GrnStockAccName, GrnStockAcc, SuspenseName, SuspenseAcc, BSIDSTOCK, BSIDSuspense, " & _
    "     GrnDate, Discounts, Vat, PurchasesDate, PurchaseOrderID )" & _
    " SELECT V.Warehouse, V.ProductID, V.Quantity, V.CostValue, " & _
          [Forms]![frmGrn]![GRNID] & " AS DocID, V.GrnstockName, V.GrnStockAccount, V.GrnSuspenseName, V.GRNSuspenseAccount, V.BSIDStock, V.BSIDSuspense, " & _
    "     convert(date, getdate()) AS Mth, V.Discounts, V.Vat, V.PurchasesDate, V.PurchaseID" & _
    " FROM dbo.ViewGrnUpdates V" & _
    " WHERE V.PurchaseID=" & [Forms]![frmGrn]![CboOrder]

' GRNID and PurchaseID are int (VBA long)?

' existing QueryDef (odbc connection saved)
with CurrentDb.QueryDef("QdfName")
     .SQL = InsertSql
     .Execute dbFailOnError
end with

' or:

'temp QueryDef
With CurrentDb.CreateQueryDef("")
    .Connect = YourOdbcConnectionString
    .SQL = InsertSql
    .Execute dbFailOnError
End With

My estimation: somewhat, but not much faster.

But it can be done even faster