Query in VB access returns less records than via DAO ?

A

Acie

Hi,

I have a query which is basically a left join of two tables with a few iif
statements, and it returns the entire lefthanded table(14,000 rows) when I
run it in Access directly. However, that same query when I run it with DAO3.6
in MS Access VBA returns only 593 records. First time this has ever happened.
I use the rst.getrows() function to retrieve the data. Sample code is below..

sql code..

sql = "SELECT Equity.SEC_ID, Equity.ID_ISIN, Equity.ID_CUSIP,
Equity.ID_SEDOL1, Equity.ID_VALOREN, "
sql = sql & "IIf(IsNumeric([eqy_sh_out]),[eqy_sh_out]*1000000,[eqy_sh_out])
AS converted_eqy_sh_out, "
sql = sql & "Equity.CNTRY_OF_INCORPORATION, Equity.CPN, Equity.CPN_TYPE,
Equity.CRNCY, Equity.DEFAULTED, "
sql = sql & "Equity.ADR_ADR_PER_SH, Equity.DVD_CRNCY, Equity.MTG_FACTOR,
Equity.MOST_RECENT_REPORTED_FACTOR, "
sql = sql & "Equity.SINKING_FUND_FACTOR, Equity.MTG_FACTOR_DT,
Equity.NXT_FACTOR_DT, Equity.ISSUER_INDUSTRY, "
sql = sql & "Equity.INDUSTRY_GROUP, Equity.ISSUE_DT, Equity.ISSUER,
Equity.MARKET_ISSUE, Equity.MATURITY, "
sql = sql & "IIf(IsNumeric([price]),IIf([crncy]<> " & quote & "USD" & quote
& ",[amt_issued]*[exchange_rate],[amt_issued]), "
sql = sql & "[amt_issued]) AS converted_amt_issued, Equity.MTG_ORIG_AMT,
Equity.MTG_COLLAT_TYPE, "
sql = sql & "Equity.SECURITY_DES, Equity.SECURITY_TYPE,
Equity.SECURITY_TYPE2, Equity.LEAD_MGR, "
sql = sql & "Equity.COUNTRY_GUARANTOR, Equity.ISO_COUNTRY_GUARANTOR,
IIf(IsNumeric([price]), "
sql = sql & "IIf([crncy]<>" & quote & "USD" & quote &
",[price]*[exchange_rate],[price]),[price]) AS converted_price, "
sql = sql & "Equity.EQY_DVD_HIST_ALL, Null AS INT_ACC, Null AS
Amt_outstanding, " & quote & Get_survey_year & quote & " AS survey_date "
sql = sql & "FROM Equity LEFT JOIN CLTBS_CL_CURRENCIES ON Equity.CRNCY =
CLTBS_CL_CURRENCIES.CODE;"


and then the command to retieve the data....
Set rst = dbs.OpenRecordset(sql)

found_data = False
If Not rst.BOF And Not rst.EOF Then
vardata2 = rst.GetRows(200000)
found_data = True
End If
rst.Close

vardata2 (a variant) contains only 593 rows of data, instead of about 14,000
records.

Any ideas?

thanks.
 
B

Bob Barrows [MVP]

Acie said:
Hi,

I have a query which is basically a left join of two tables with a
few iif statements, and it returns the entire lefthanded table(14,000
rows) when I run it in Access directly. However, that same query when
I run it with DAO3.6 in MS Access VBA returns only 593 records. First
time this has ever happened. I use the rst.getrows() function to
retrieve the data. Sample code is below..

sql code..

sql = "SELECT Equity.SEC_ID, Equity.ID_ISIN, Equity.ID_CUSIP,
Equity.ID_SEDOL1, Equity.ID_VALOREN, "
sql = sql &
"IIf(IsNumeric([eqy_sh_out]),[eqy_sh_out]*1000000,[eqy_sh_out]) AS
converted_eqy_sh_out, "
sql = sql & "Equity.CNTRY_OF_INCORPORATION, Equity.CPN,
Equity.CPN_TYPE, Equity.CRNCY, Equity.DEFAULTED, "
sql = sql & "Equity.ADR_ADR_PER_SH, Equity.DVD_CRNCY,
Equity.MTG_FACTOR, Equity.MOST_RECENT_REPORTED_FACTOR, "
sql = sql & "Equity.SINKING_FUND_FACTOR, Equity.MTG_FACTOR_DT,
Equity.NXT_FACTOR_DT, Equity.ISSUER_INDUSTRY, "
sql = sql & "Equity.INDUSTRY_GROUP, Equity.ISSUE_DT, Equity.ISSUER,
Equity.MARKET_ISSUE, Equity.MATURITY, "
sql = sql & "IIf(IsNumeric([price]),IIf([crncy]<> " & quote & "USD" &
quote & ",[amt_issued]*[exchange_rate],[amt_issued]), "
sql = sql & "[amt_issued]) AS converted_amt_issued,
Equity.MTG_ORIG_AMT, Equity.MTG_COLLAT_TYPE, "
sql = sql & "Equity.SECURITY_DES, Equity.SECURITY_TYPE,
Equity.SECURITY_TYPE2, Equity.LEAD_MGR, "
sql = sql & "Equity.COUNTRY_GUARANTOR, Equity.ISO_COUNTRY_GUARANTOR,
IIf(IsNumeric([price]), "
sql = sql & "IIf([crncy]<>" & quote & "USD" & quote &
",[price]*[exchange_rate],[price]),[price]) AS converted_price, "
sql = sql & "Equity.EQY_DVD_HIST_ALL, Null AS INT_ACC, Null AS
Amt_outstanding, " & quote & Get_survey_year & quote & " AS
survey_date "
sql = sql & "FROM Equity LEFT JOIN CLTBS_CL_CURRENCIES ON
Equity.CRNCY = CLTBS_CL_CURRENCIES.CODE;"


and then the command to retieve the data....
Set rst = dbs.OpenRecordset(sql)

found_data = False
If Not rst.BOF And Not rst.EOF Then
vardata2 = rst.GetRows(200000)
found_data = True
End If
rst.Close

vardata2 (a variant) contains only 593 rows of data, instead of about
14,000 records.

Any ideas?
Click to expand...
Can't debug a sql statement without seeing what it is. I've just gone to
the trouble of running this code in a VBA module and writing the content
of the sql variable to the debug window. Compare this result to the sql
of the query that returns 140000 records and let us know the difference,
if any:

SELECT Equity.SEC_ID, Equity.ID_ISIN, Equity.ID_CUSIP,Equity.ID_SEDOL1,
Equity.ID_VALOREN,
IIf(IsNumeric([eqy_sh_out]),[eqy_sh_out]*1000000,[eqy_sh_out]) AS
converted_eqy_sh_out, Equity.CNTRY_OF_INCORPORATION, Equity.CPN,
Equity.CPN_TYPE,Equity.CRNCY, Equity.DEFAULTED, Equity.ADR_ADR_PER_SH,
Equity.DVD_CRNCY, Equity.MTG_FACTOR,Equity.MOST_RECENT_REPORTED_FACTOR,
Equity.SINKING_FUND_FACTOR, Equity.MTG_FACTOR_DT,Equity.NXT_FACTOR_DT,
Equity.ISSUER_INDUSTRY, Equity.INDUSTRY_GROUP, Equity.ISSUE_DT,
Equity.ISSUER,Equity.MARKET_ISSUE, Equity.MATURITY,
IIf(IsNumeric([price]),IIf([crncy]<>
"USD",[amt_issued]*[exchange_rate],[amt_issued]), [amt_issued]) AS
converted_amt_issued, Equity.MTG_ORIG_AMT,Equity.MTG_COLLAT_TYPE,
Equity.SECURITY_DES, Equity.SECURITY_TYPE,Equity.SECURITY_TYPE2,
Equity.LEAD_MGR, Equity.COUNTRY_GUARANTOR,
Equity.ISO_COUNTRY_GUARANTOR,IIf(IsNumeric([price]),
IIf([crncy]<>"USD",[price]*[exchange_rate],[price]),[price]) AS
converted_price, Equity.EQY_DVD_HIST_ALL, Null AS INT_ACC, Null AS
Amt_outst
anding, "" AS survey_date FROM Equity LEFT JOIN CLTBS_CL_CURRENCIES ON
Equity.CRNCY = CLTBS_CL_CURRENCIES.COD

I've got to wonder why you are retrieving 14000 records to a VBA
variable ...
 
B

Bob Barrows [MVP]

Acie said:
Hi,

I have a query which is basically a left join of two tables with a
few iif statements, and it returns the entire lefthanded table(14,000
rows) when I run it in Access directly. However, that same query when
I run it with DAO3.6 in MS Access VBA returns only 593 records. First
time this has ever happened. I use the rst.getrows() function to
retrieve the data. Sample code is below..
Click to expand...

PS. I've also got to wonder why you don't simply save the query that
works and execute it instead of going to all the trouble of assigning it
to a string variable ...

dim qdf as querydef, db as database,rs as dao.recordset
set db=currentdb
set qdf=db.querydefs("nameofquery")
set rs=qdf.createrecordset

Much simpler, isn't it?
 
A

Acie

Bob,

Thanks for looking at my issue.

I ran the sql code in MS Access, and it retrieves the 14,000 records. But I
have to try it in the VBA code.

To answer a couple of your questions:
1. The query has tablenames as parameters, and the output of a function as a
parameter. Plus, I will be creating a textfile from the 14,000 records.
Although I think I could probably do that from Access export function, but I
have not looked at how to invoke it form VBA (yet).


I will try out your query in my vba code and test it and will reply with my
results.

thanks,
Acie


Bob Barrows said:
Acie said:
Hi,

I have a query which is basically a left join of two tables with a
few iif statements, and it returns the entire lefthanded table(14,000
rows) when I run it in Access directly. However, that same query when
I run it with DAO3.6 in MS Access VBA returns only 593 records. First
time this has ever happened. I use the rst.getrows() function to
retrieve the data. Sample code is below..

sql code..

sql = "SELECT Equity.SEC_ID, Equity.ID_ISIN, Equity.ID_CUSIP,
Equity.ID_SEDOL1, Equity.ID_VALOREN, "
sql = sql &
"IIf(IsNumeric([eqy_sh_out]),[eqy_sh_out]*1000000,[eqy_sh_out]) AS
converted_eqy_sh_out, "
sql = sql & "Equity.CNTRY_OF_INCORPORATION, Equity.CPN,
Equity.CPN_TYPE, Equity.CRNCY, Equity.DEFAULTED, "
sql = sql & "Equity.ADR_ADR_PER_SH, Equity.DVD_CRNCY,
Equity.MTG_FACTOR, Equity.MOST_RECENT_REPORTED_FACTOR, "
sql = sql & "Equity.SINKING_FUND_FACTOR, Equity.MTG_FACTOR_DT,
Equity.NXT_FACTOR_DT, Equity.ISSUER_INDUSTRY, "
sql = sql & "Equity.INDUSTRY_GROUP, Equity.ISSUE_DT, Equity.ISSUER,
Equity.MARKET_ISSUE, Equity.MATURITY, "
sql = sql & "IIf(IsNumeric([price]),IIf([crncy]<> " & quote & "USD" &
quote & ",[amt_issued]*[exchange_rate],[amt_issued]), "
sql = sql & "[amt_issued]) AS converted_amt_issued,
Equity.MTG_ORIG_AMT, Equity.MTG_COLLAT_TYPE, "
sql = sql & "Equity.SECURITY_DES, Equity.SECURITY_TYPE,
Equity.SECURITY_TYPE2, Equity.LEAD_MGR, "
sql = sql & "Equity.COUNTRY_GUARANTOR, Equity.ISO_COUNTRY_GUARANTOR,
IIf(IsNumeric([price]), "
sql = sql & "IIf([crncy]<>" & quote & "USD" & quote &
",[price]*[exchange_rate],[price]),[price]) AS converted_price, "
sql = sql & "Equity.EQY_DVD_HIST_ALL, Null AS INT_ACC, Null AS
Amt_outstanding, " & quote & Get_survey_year & quote & " AS
survey_date "
sql = sql & "FROM Equity LEFT JOIN CLTBS_CL_CURRENCIES ON
Equity.CRNCY = CLTBS_CL_CURRENCIES.CODE;"


and then the command to retieve the data....
Set rst = dbs.OpenRecordset(sql)

found_data = False
If Not rst.BOF And Not rst.EOF Then
vardata2 = rst.GetRows(200000)
found_data = True
End If
rst.Close

vardata2 (a variant) contains only 593 rows of data, instead of about
14,000 records.

Any ideas?
Click to expand...
Can't debug a sql statement without seeing what it is. I've just gone to
the trouble of running this code in a VBA module and writing the content
of the sql variable to the debug window. Compare this result to the sql
of the query that returns 140000 records and let us know the difference,
if any:

SELECT Equity.SEC_ID, Equity.ID_ISIN, Equity.ID_CUSIP,Equity.ID_SEDOL1,
Equity.ID_VALOREN,
IIf(IsNumeric([eqy_sh_out]),[eqy_sh_out]*1000000,[eqy_sh_out]) AS
converted_eqy_sh_out, Equity.CNTRY_OF_INCORPORATION, Equity.CPN,
Equity.CPN_TYPE,Equity.CRNCY, Equity.DEFAULTED, Equity.ADR_ADR_PER_SH,
Equity.DVD_CRNCY, Equity.MTG_FACTOR,Equity.MOST_RECENT_REPORTED_FACTOR,
Equity.SINKING_FUND_FACTOR, Equity.MTG_FACTOR_DT,Equity.NXT_FACTOR_DT,
Equity.ISSUER_INDUSTRY, Equity.INDUSTRY_GROUP, Equity.ISSUE_DT,
Equity.ISSUER,Equity.MARKET_ISSUE, Equity.MATURITY,
IIf(IsNumeric([price]),IIf([crncy]<>
"USD",[amt_issued]*[exchange_rate],[amt_issued]), [amt_issued]) AS
converted_amt_issued, Equity.MTG_ORIG_AMT,Equity.MTG_COLLAT_TYPE,
Equity.SECURITY_DES, Equity.SECURITY_TYPE,Equity.SECURITY_TYPE2,
Equity.LEAD_MGR, Equity.COUNTRY_GUARANTOR,
Equity.ISO_COUNTRY_GUARANTOR,IIf(IsNumeric([price]),
IIf([crncy]<>"USD",[price]*[exchange_rate],[price]),[price]) AS
converted_price, Equity.EQY_DVD_HIST_ALL, Null AS INT_ACC, Null AS
Amt_outst
anding, "" AS survey_date FROM Equity LEFT JOIN CLTBS_CL_CURRENCIES ON
Equity.CRNCY = CLTBS_CL_CURRENCIES.COD

I've got to wonder why you are retrieving 14000 records to a VBA
variable ...

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
Click to expand...
 
B

Bob Barrows [MVP]

Acie said:
Bob,

Thanks for looking at my issue.

I ran the sql code in MS Access, and it retrieves the 14,000 records.
But I have to try it in the VBA code.
Click to expand...

You are misunderstanding me. That query i posted was the one generated
by your "sql=" statements.
What I wanted you to do was compare that sql statement to the one that
actually worked to see what the differences were.

Or are you telling me that they are identical? The one you ran using the
Access query builder and returned 14000 records is exactly the same as
the one you assigned to the sql variable?
To answer a couple of your questions:
1. The query has tablenames as parameters, and the output of a
function as a parameter. Plus, I will be creating a textfile from the
14,000 records. Although I think I could probably do that from Access
export function, but I have not looked at how to invoke it form VBA
(yet).
Click to expand...

Instead of opening a recordset, use the DoCmd.TransferText method. See
online help for details about this method. The idea is to create a named
query containing the sql generated by your concatenation:

dim sql as string
sql = "select ... "
dim qdf as querydef,db as database
set db=currentdb
set qdf=db.createquerydef("ForExport",sql)
docmd.TransferText acExportDelim,,"ForExport",filepathandname
db.querydefs.delete "ForExport"
 
A

Acie

Bob,

The same exact query that brings 14,000 records through access query
builder, when embedded in the VBA code provides only 593 records. When I
replaced the static values with variables, I also got the 593 records.
This is the part that confuses me.

I will try your method of creating a temporary query in vba and then
exporting the data via export method.

But I'm hoping the query will retrieve the 14,000 records. I will go ahead
and try it now, and I'll post my results.

thanks,
Acie
 
B

Bob Barrows [MVP]

Acie said:
Bob,

The same exact query that brings 14,000 records through access query
builder, when embedded in the VBA code provides only 593 records.
When I replaced the static values with variables, I also got the 593
records. This is the part that confuses me.
Click to expand...

Well, it confuses me as well. It strikes me as impossible (unless LIKE
and wildcards are involved, which is not the case here). That's why I
was sure there would be a difference between the statement used in the
query builder and the statement generated by the vba statements.
Frankly, I've never run into anything like this.
Of course, I would never have done the variables substitution until I
figured out what was going on with the version with the static values.

Are there 593 records in the CLTBS_CL_CURRENCIES table? That would
indicate to me that the join was being treated as an INNER rather than
OUTER join. Perhaps use the full "LEFT OUTER JOIN" phrase instead of
"LEFT JOIN" .... I'm grasping at straws.
I will try your method of creating a temporary query in vba and then
exporting the data via export method.
Click to expand...

This was not intended to fix anything ... I was simply explaining the
best technique to export your data.
But I'm hoping the query will retrieve the 14,000 records. I will go
ahead and try it now, and I'll post my results.
Click to expand...
I'll be interested
 
A

Acie

Bob Barrows said:
Well, it confuses me as well. It strikes me as impossible (unless LIKE
and wildcards are involved, which is not the case here). That's why I
was sure there would be a difference between the statement used in the
query builder and the statement generated by the vba statements.
Frankly, I've never run into anything like this.
Of course, I would never have done the variables substitution until I
figured out what was going on with the version with the static values.
Click to expand...

I tried it both ways, and still got the 593 records. I use getrows heavily
but its usually for rather small number of rows < 500.

Anywyay, I created the query from the sql, and executed it from the
docmd.transfertext cmd and it retrieved the 14,000 records. Now, I have to
figure out how to use a semicolon as a delimiter, and to remove the double
quotes from the created text file.
Are there 593 records in the CLTBS_CL_CURRENCIES table? That would
indicate to me that the join was being treated as an INNER rather than
OUTER join. Perhaps use the full "LEFT OUTER JOIN" phrase instead of
"LEFT JOIN" .... I'm grasping at straws.
Click to expand...

No, the currencies table only has about 200 records. Funny enough, I have a
similar process for another table, and the getrows gets me the 19,000 records
without a hitch.... with an exact query and left join... strange
enough...yes..

Anyway, thanks for your suggestions and time spent on helping me out. It is
much appreciated. Thanks, Acie
 
Top