By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,504 Members | 1,194 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,504 IT Pros & Developers. It's quick & easy.

Export to Excel, recordset is empty sometimes

P: n/a
Hello,

I have a form that performs a search, according to criteria that a
user enters into the text boxes.

When the user clicks on „Search", a SQL string (say strSQL) is built
up with the criteria. Then a list box RowSource property is set to
this strSQL, to display the results of the search.
StrSQL is a global variable within this Form's code module, i.e. in
the class module.
This all works fine so far.

I then have a button „Export…", that exports the results displayed in
the listbox to an Excel spreadsheet using automation. It also uses
strSQL to build a recordset, and then uses this recordset to use the
CopyFromRecordset method of the Excel object.

This all works fine, until I have the user filling out a specific
criteria. The problem SQL is as follows:

SELECT A.VPNummer, A.Nachname, A.Vorname, A.Abteilung, A.Telefon,
A.Sex AS Geschlecht, A.VFG, A.Körperhöhe, A.StammlProp FROM
qryAlleVPmEndwerte A
WHERE (A.Sex) = 'm' AND (A.Körperhöhe) >= 1750 AND (A.Körperhöhe) <=
1850 AND (A.StammlProp) Like '*MM*' AND (A.VFG) = True
ORDER BY A.VPNummer;

Only when I have "(A.StammlProp) Like '*MM*' " as part of the SQL
syntax, the following problem occurs.

This displays correctly in the listbox, but when exporting to Excel,
there is a blank page, only the headings appear. I use the following
code to create a recordset for Excel, and this only seems to create an
empty recordset:

Function CreateRecordset(rstD As ADODB.Recordset, _
rstC As ADODB.Recordset, _
strSQL As String, _
strListName As String)
On Error GoTo CreateRecordset_Err
'Create recordset that contains count of records in query
rstC.Open strSQL
'test
Debug.Print strSQL
'If more than 500 records in query result, return false
'Otherwise, create recordset from query
'CAUSE RUN_TIME ERROR
'--------------------
'If rstCount!NumRecords > 500 Then
'CreateRecordset = False
'--------------------
If rstC.RecordCount > 500 Then
CreateRecordset = False

Else
rstD.Open strSQL
CreateRecordset = True
End If

CreateRecordset_Exit:
Set rstC = Nothing
Exit Function

CreateRecordset_Err:
MsgBox "Fehler # " & Err.Number & ": " & Err.Description
Resume CreateRecordset_Exit
End Function

Note: rstD and rstC are empty recordsets when passed to this function
When I execute line 9 of the above, I get runtime error 3021 „No
current record blablabla".
What this tells me is that this is an empty recordset, but I don't
understand why? When copying this strSQL code into a query, it works
fine, and also the search is fine, but it doesn't create a recordset
in the above function.

Any ideas are very welcome. Thanks in advance.

Regards,

Jean
Nov 13 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/a
je**********@hotmail.com (Jean) wrote in message news:<7e*************************@posting.google.c om>...
Any ideas are very welcome. Thanks in advance.

Regards,

Jean


Hmm. First see if extra parentheses around

(A.StammlProp) Like '*MM*'

work. Like has a lower operator precedence than AND and could result
in certain SQL executions trying to perform the AND first.

James A. Fortune
Nov 13 '05 #2

P: n/a
Thanks, I tried your suggestion but it makes no difference.

Hope there is still some help out there...

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 13 '05 #3

P: n/a
Hi again,

Coming back to this problem, i realised that this happens whenever I
have a string data type in my SQL query, e.g.

WHERE ........... AND ((A.StammlProp) Like '*MM*') AND ...........

another example,

WHERE ............ AND (A.Händig) Like '*r*' AND

Both still don't export the recordset to Excel, as you can see I tried
James's suggestion in the first example.

I will keep on working on this one, but it would be great if someone
knew actually what is causing this problem.

Thanks!

Jean

Nov 13 '05 #4

P: n/a
Jean wrote:
Hi again,

Coming back to this problem, i realised that this happens whenever I
have a string data type in my SQL query, e.g.

WHERE ........... AND ((A.StammlProp) Like '*MM*') AND ...........

another example,

WHERE ............ AND (A.Händig) Like '*r*' AND

Both still don't export the recordset to Excel, as you can see I tried James's suggestion in the first example.

I will keep on working on this one, but it would be great if someone
knew actually what is causing this problem.

Thanks!

Jean


Jean,

The next thing I'd try is to run the query without the ((A.StammlProp)
Like '*MM*') to see if any of the results have MM in the Stammlprop
field. I would build the SQL string in pieces so that you can add or
remove criteria easily. Write the SQL string to a file then cut and
paste the SQL string into the SQL area of query design view.

Dim FN As Integer
FN = FreeFile
Open "C:\SQL.TXT" For Output As #FN
Print #FN, strSQL
Close #FN
James A. Fortune

Nov 13 '05 #5

P: n/a
Hi James,

I tried that, what you said with copying the SQL string into a query
design window.

When I run the suspicious SQL string in the SQL view of the query
design: e.g.

SELECT A.VPNummer, A.Nachname, A.Vorname, A.Abteilung, A.Telefon, A.Sex
AS Geschlecht, A.VFG, A.Körperhöhe, A.StammlProp FROM
qryAlleVPmEndwerte A WHERE (A.Sex) = 'm' AND (A.Körperhöhe) >= 1750
AND (A.Körperhöhe) <= 1850 AND ((A.StammlProp) Like '*MM*') AND
(A.VFG) = True ORDER BY A.VPNummer;

it actually returns the right records - so there is in fact no problem
there.

The problem is when I use this SQL to open a recordset in my VB code.
Using the same abovementioned SQL, I do the following:

++++++++++++++++++++++++++
dim rstData as ADODB.Recordset
rstData.ActiveConnection = CurrentProject.Connection

rstData.Open strSQL
++++++++++++++++++++++++++

When I inspect the Status property for rstData in the locals property
of the VB editor, it says "<Either BOF or EOF is true...>"
This is in fact an empty recordset, but why??? There seems to be a
problem with the .Open method of the Recordset object.

Please help!

Nov 13 '05 #6

P: n/a
"Jean" <je**********@hotmail.com> wrote
[snip]
The problem is when I use this SQL to open a recordset in my VB code.
Using the same abovementioned SQL, I do the following:
++++++++++++++++++++++++++
dim rstData as ADODB.Recordset
rstData.ActiveConnection = CurrentProject.Connection

rstData.Open strSQL
++++++++++++++++++++++++++
When I inspect the Status property for rstData in the locals property
of the VB editor, it says "<Either BOF or EOF is true...>"
This is in fact an empty recordset, but why??? There seems to be a
problem with the .Open method of the Recordset object.
-----------------------------------------------------

You have not actually created a new object yet:

Dim rstData As ADODB.Recordset
Set rstData = New ADODB.Recordset ' You need this line

rstData.Open strSQL, CurrentProject.Connection, _
adOpenForwardOnly, adReadOnly, adCmdText
Darryl Kerkeslager
Nov 13 '05 #7

P: n/a
I think I found the problem.

When using ADODB, the wildcard character is "%" not "*"
Thanks for the other tips though

Nov 13 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.