CODEWITHSUNDEEP
ms-accessvbams-access-2010
Hulapig
Hulapig

Reputation: 13

MS Access currentdb.excute has different result than Docmd.RunSQL

I use VBA to run an INSERT INTO ... SELECT... SQL query with Currentdb.Execute command. However, I have trouble with the result. There are some rows missing the data.

The result is correct when using Docmd.RunSQL with the same SQL, but I don't want the warning message for inserting data into the table.

I also tried using only the SELECT... with Currentdb.Execute and print the result on the debug window. The result is correct, no missing data.

Here's my code:

strsql = strsql & "INSERT INTO tempLineItems (OrderNo, PositionNo, PartNo, [Description], PlannedDeliveryDate, Qty, Unit, Price, Curr, txta, LineItemText, Discount, Tax) "
strsql = strsql & "SELECT dbo_ttdsls041600.t_orno, dbo_ttdsls041600.t_pono, dbo_ttdsls041600.t_item, IIF(dbo_ttipcs021600.t_dsca is null,dbo_ttiitm001600.t_dsca,dbo_ttipcs021600.t_dsca) AS t_dsca, dbo_ttdsls041600.t_ddta, dbo_ttdsls041600.t_oqua, dbo_ttdsls041600.t_cups, dbo_ttdsls041600.t_pric, dbo_ttdsls040600.t_ccur, dbo_ttdsls041600.t_txta, Null AS LineItemText, dbo_ttdsls041600.t_disc_1, dbo_ttdsls041600.t_cvat "
strsql = strsql & "FROM dbo_ttdsls040600 INNER JOIN ((dbo_ttipcs021600 RIGHT JOIN dbo_ttdsls041600 ON (dbo_ttipcs021600.t_item = dbo_ttdsls041600.t_item) AND (dbo_ttipcs021600.t_cprj = dbo_ttdsls041600.t_cprj)) LEFT JOIN dbo_ttiitm001600 ON dbo_ttdsls041600.t_item = dbo_ttiitm001600.t_item) ON dbo_ttdsls040600.t_orno = dbo_ttdsls041600.t_orno "
strsql = strsql & "WHERE (((dbo_ttdsls041600.t_orno)=" & Me.txtSalesOrderNo.Value & ") AND ((dbo_ttdsls041600.t_pono)>0)) "
strsql = strsql & "ORDER BY dbo_ttdsls041600.t_pono;"

The problem is on the 

IIF(dbo_ttipcs021600.t_dsca is null,dbo_ttiitm001600.t_dsca,dbo_ttipcs021600.t_dsca) AS t_dsca

I used ISNULL() function, and it has the same result as using Is Null

3/28/2017 Update: Those tables are linked tables from the SQL Server. I created local tables based on a subset data of those linked ones, and there's no missing data at all.

Upvotes: 1

Views: 259

Answers (2)

Hulapig
Hulapig

Reputation: 13

I'm just curious about this problem, so I did a different approach by using multiple layer of queries.

strsql = strsql & "INSERT INTO tempLineItems (OrderNo, PositionNo, PartNo, Description, PlannedDeliveryDate, Qty, Unit, Price, Curr, txta, LineItemText, Discount, Tax) "
strsql = strsql & "SELECT SO1.t_orno, SO1.t_pono, SO1.t_item, nz(SO1.t_dsca, dbo_ttiitm001600.t_dsca) AS t_dsca, SO1.t_ddta, SO1.t_oqua, SO1.t_cups, SO1.t_pric, SO1.t_ccur, SO1.t_txta, Null AS LineItemText, SO1.t_disc_1, SO1.t_cvat "
strsql = strsql & "FROM (SELECT SO.t_orno, SO.t_ccur, SO.t_pono, SO.t_item, SO.t_oqua, SO.t_pric, SO.t_cups, SO.t_ddta, SO.t_txta, SO.t_disc_1, SO.t_cvat, dbo_ttipcs021600.t_dsca "
strsql = strsql & "FROM (SELECT dbo_ttdsls040600.t_orno, dbo_ttdsls040600.t_ccur, dbo_ttdsls041600.t_pono, dbo_ttdsls041600.t_item, dbo_ttdsls041600.t_pric, dbo_ttdsls041600.t_cups, dbo_ttdsls041600.t_ddta, dbo_ttdsls041600.t_cprj, dbo_ttdsls041600.t_txta, dbo_ttdsls041600.t_disc_1, dbo_ttdsls041600.t_cvat, dbo_ttdsls041600.t_oqua "
strsql = strsql & "FROM dbo_ttdsls041600 INNER JOIN dbo_ttdsls040600 ON dbo_ttdsls041600.t_orno = dbo_ttdsls040600.t_orno "
strsql = strsql & "WHERE (((dbo_ttdsls040600.t_orno)=" & Me.txtSalesOrderNo & "))) SO "
strsql = strsql & "LEFT JOIN dbo_ttipcs021600 ON dbo_ttipcs021600.t_cprj = SO.t_cprj AND dbo_ttipcs021600.t_item = SO.t_item)  AS SO1 LEFT JOIN dbo_ttiitm001600 ON dbo_ttiitm001600.t_item = SO1.t_item "
strsql = strsql & "ORDER BY SO1.t_pono;"

And this works!! I guess there's some issue in Access internally.

Upvotes: 0

June7
June7

Reputation: 21389

Very odd. But I've never had to work much with SQLServer tables. To use RunSQL and not get warnings, turn them off and on.

DoCmd.SetWarnings False
DoCmd.RunSQL ...
DoCmd.SetWarnings True

Upvotes: 0

Related Questions