473,734 Members | 2,724 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Export to Excel, recordset is empty sometimes

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
CopyFromRecords et 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
qryAlleVPmEndwe rte 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.StammlP rop) 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!NumRec ords > 500 Then
'CreateRecordse t = False
'--------------------
If rstC.RecordCoun t > 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
7 8507
je**********@ho tmail.com (Jean) wrote in message news:<7e******* *************** ***@posting.goo gle.com>...
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
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
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
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
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
qryAlleVPmEndwe rte 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.ActiveC onnection = 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
"Jean" <je**********@h otmail.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.ActiveC onnection = 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, _
adOpenForwardOn ly, adReadOnly, adCmdText
Darryl Kerkeslager
Nov 13 '05 #7
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
5032
by: Matt | last post by:
I have an ASP page that calls ASP routines that I created that execute a database query and return the results to a recordset. I then iterate through the recordset and display the data in a table. Before I iterate through the recordset I instruct the browser that the content type is Excel using the following line: (Response.ContentType = "application/vnd.ms-excel") This works fine with Excel 2003 but with older versions (I tested Excel...
6
13141
by: Robin Cushman | last post by:
Hi all, I need some help -- I'm working with an A2K database, using DAO, and am trying to read records into a Crystal Report and then export it to a folder on our network as an Excel spreadsheet. I'm having trouble with my code at the point at which it hits ".ReadRecords" -- the module just runs and runs without generating anything. I've gotten this code to correctly save .rpt files without any data, but not with data, nor have I been...
1
2297
by: Ellen Manning | last post by:
I'm trying to export an Excel2K spreadsheet to A2K. Here is a sample of the Excel spreadsheet: LastName FirstName Hours Location HoursPercent Doe John 9 WMC 2.94 VA Med Ctr 265 VA 86.60 32 VA Res Clinic 10.45 <blank row>
9
3917
by: jillandgordon | last post by:
I am trying to import an excel file into Access 97. It looks perfectly all right but, every time I try to import it, I get to the lst step and am told that it was not imported due to an error. There is no further explanation. What are the kinds of things that make this happen? Thanks from an obvious rookie. Gordon
1
2896
by: Jim | last post by:
This should really be simple, but I can't figure it out. I have some VB that exports a table in an Excel format ("C:\NewReport.xls"). After the export is done, I simply want to have some code that launches NewReport.xls in Excel. How?
14
6439
by: bonehead | last post by:
Greetings, I'm using the DoCmd.TransferText method to export the results of a MS Access query to a csv file. The csv will then be used to load an Oracle table. In other systems such as TOAD for Oracle, it's possible to force an additional comma delimiter after the last column, if the column is empty on a particular row. Oracle requires this additional comma on empty rightmost columns, for importing purposes.
4
8710
by: paul.chae | last post by:
I have a table in Access with about 3000 records. There are ~60 unique values in the ID field for the 3000 records. What I would like to do is automatically generate multiple Excel worksheets within a single workbook with these records. I would end up with around 500 worksheets, 1 for each unique ID value. I was thinking this could be done if I have an exported flag column in the table, and I search for the max (or min) on the ID...
1
8709
by: baling | last post by:
Hi.... Hi everybody, i have a code that i make in VBA and know I want to use this code in to VB6. But i don't know how to use that code in to VB 6.0 Please correct this code so i can use it in VB 6.0. Code: --- use in mainform ------- Option Compare Database Option Explicit
7
28900
Merlin1857
by: Merlin1857 | last post by:
Its great producing data for users to look at in your web pages and generally that is sufficient for their needs but sometimes you may want to supply your user with the data in a form they can actually do something more with. This code shows you how to display data from your database and then how to give that data to the user in the form of a useable Excel spreadsheet which they can then take away and play with themselves. The way I have shown...
0
8776
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9449
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
9236
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8186
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6735
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6031
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4550
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4809
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3261
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.