strange things with select queries

[Aivars]

Dear group,

I have two queries, which if they are run separately from Access work
as expected. When I run them via the code, each one after another,
unexplainable four records are added to final table.
Query1:
PARAMETERS [DATUMSLIDZ] DateTime;
SELECT
"doc_parc" AS DOC,
[DATUMSLIDZ] AS DATUMS,
"mana_parc" AS [TEXT],
IIF(
Q2.EXCHDIF>=0,
"82500",
Q2.KONTS
) AS DEB,
IIF(
DEB=Q2.KONTS,
"81500",
Q2.KONTS
) AS KRED,
0 AS VSUM,
Q2.CURR1 AS [VAL],
abs(
Q2.EXCHDIF
) AS SUMMA,
Q2.RATE AS KURS,
iif(
DEB=Q2.KONTS,
Q2.PKONTS,
""
) AS PACCD,
IIF(
KRED=Q2.KONTS,
Q2.PKONTS,
""
) AS PACCC
FROM
qryKS2 AS Q2

After that I run the very simple second qyery to make table KS3:
Query2:
SELECT *
INTO KS3 from
qryKS3

If I run them separately they work as expected. If via code then four
additional rows are added to the table. My code:

Sub Tester(dDate As Date)
'run third query qryKS3
Dim cat As ADOX.Catalog
Dim rst As ADODB.Recordset
Set cat = New ADOX.Catalog
Set cat.ActiveConnection = CurrentProject.Connection

Dim prc3 As ADOX.Procedure
Dim cmd As ADODB.Command
Dim prm As ADODB.Parameter
Dim dParamValue As String


dParamValue = Format$(dDate, JetDateFmt)

Set prc3 = cat.Procedures("qryKS3")
Set cmd = prc3.Command
Set prm = cmd.Parameters("[DATUMSLIDZ]")
prm.Value = Eval(dParamValue)
Set rst = cmd.Execute

Set rst = Nothing
Set cmd = Nothing
Set prm = Nothing

' 'run fourth query
Dim prc4 As ADOX.Procedure
Set prc4 = cat.Procedures("MaketblKS3")
Set cmd = prc4.Command
Set prm = cmd.Parameters("[DATUMSLIDZ]")
prm.Value = Eval(dParamValue)
'
'delete existing KS3 table
On Error Resume Next
cat.Tables.Delete "KS3"
On Error GoTo 0

cat.Tables.Refresh
'execute query
Set rst = cmd.Execute
Set rst = Nothing
Set cmd = Nothing
Set prm = Nothing
End Sub

Any ideas how to identify what causes the problem? It is very strange
to me (maybe the reserved Access words are to blame?)

Thanks
Aivars

 

[Aivars]

Actually I found out that it is the second Make Table procedure when
run from code adds four strange rows in the KS3 table.

Aivars

 

[Aivars]

Hello, All,
I solved the problem. The matter was that in a very first query I was
using a wildcard expression:
deb not like '296*". It works when run from Access interface but
strangely enough it does not work when run from code using ADO/ADOX. I
had to explicitely specify which account number not to take into
account: deb <>'29600'. Then running the code these "mysterious" four
rows are not selected into the final table.
It is strange (at least to me) but I am not experienced Access user so
if anybody can tell me why it was not possible to use wildcard as above
in the code I would be very thankful.

Regards
Aivars

 

[Jamie Collins]

I solved the problem. The matter was that in a very first query I was
using a wildcard expression:
deb not like '296*". It works when run from Access interface but
strangely enough it does not work when run from code using ADO/ADOX.

The ANSI-92 query mode equivalent wildcard character is '%'. See:

http://office.microsoft.com/en-gb/access/HP030704831033.aspx

Jamie.

--