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

1
votes

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.

2
ms-accessvbams-access-2010
or just use NZ(dbo_ttipcs021600.t_dsca,dbo_ttiitm001600.t_dsca) and save a few keystrokes. – geeFlo
I tried NZ() function, but the result came out the same with missing data. – Hulapig
Why do you bother to alias t_item, especially with its own name? Record is inserted but you are missing data from the Description field? Any others? Want to provide sample data for testing? I recommend Box.com fileshare site. – June7
I must do something with the t_item before, and didn't remove it when I change it back. Those tables are linked tables from SQL server, and I will try to create a local table and see if the problem still exists. – Hulapig
I created local tables based on a subset data of those linked ones, and there's no missing data at all. – Hulapig

2 Answers

0
votes

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
0
votes

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.