Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Query or code problem ? (exporting recordset to excel)

Posted on 2003-12-08
15
Medium Priority
?
879 Views
Last Modified: 2013-11-26
Hi !

I have this code that exports a recordset to an excel workbook in an Access Database (Access 2002).
My procedure is exporting the whole thing and is dynamic (depending on fields selected and number of records).

The problem is that i use an inner join query and it only returns
me the first result, even though the same query in Access returns two results !!

I have the source here (it's a little easy code) :
http://pages.infinit.net/rouxjean/bd1.zip

Open the export form Export and click on the Excel button.
Compare the result of the export with the Query Requête 1.
I use the same SQL query for both...

Please help this is important !!!
0
Comment
Question by:rouxjean
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 6
15 Comments
 
LVL 8

Expert Comment

by:ampapa
ID: 9900876
You count was off on your for next routine:

' Exporter les données
rstSource.MoveFirst
For i = 0 To rstSource.RecordCount
    For j = 0 To rstSource.Fields.Count
        ExcelWSheet.Cells(i + 3, j + 1) = rstSource.Fields(j).Value
    Next j
    rstSource.MoveNext
Next i
rstSource.MoveFirst
0
 
LVL 39

Expert Comment

by:appari
ID: 9901118
change

' Exporter les données
rstSource.MoveFirst
For i = 0 To rstSource.RecordCount
    For j = 0 To rstSource.Fields.Count
        ExcelWSheet.Cells(i + 3, j + 1) = rstSource.Fields(j).Value
    Next j
    rstSource.MoveNext
Next i

to

' Exporter les données
rstSource.MoveFirst
while not     rstSource.EOF
    For j = 0 To rstSource.Fields.Count
        ExcelWSheet.Cells(i + 3, j + 1) = rstSource.Fields(j).Value
    Next j
    rstSource.MoveNext
wend

0
 

Author Comment

by:rouxjean
ID: 9901216
Thx for your replies.
Unfortunately, the solution you provide is not dynamic so i changed the 3 for rstSource.RecordCount.
When i used my sql string, it didn't work either, i had the same result as before.
Any idea ??
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 8

Expert Comment

by:ampapa
ID: 9901310
You count is dictated by the "i" variable. Replacing the "3" with rstsource.recordcount wil surely error.

What kind of error are you recieving?
0
 
LVL 8

Expert Comment

by:ampapa
ID: 9901337
The output that you recieve when running the for next routine is exactly tied to the number of records in your table.

This is what I get when I run the export routine:
Nom      Prenom      Solde
Roux      Jean-Sébastien      $500.00
Milot      Valérie      $1,000.00
0
 
LVL 8

Expert Comment

by:ampapa
ID: 9901356
Her is the full source:

Option Compare Database
Option Explicit

Private Sub Commande0_Click()
Dim rstSource As DAO.Recordset
Dim ExcelApp As Excel.Application
Dim ExcelWBook As Excel.Workbook
Dim ExcelWSheet As Excel.Worksheet
Dim i, j As Integer
Dim strSQL As String

strSQL = "SELECT clients.Nom, clients.Prenom, soldes.Solde FROM clients INNER JOIN soldes ON clients.ID = soldes.ID WHERE (([soldes]![IDClient]=[clients]![ID]));"

Set rstSource = CurrentDb.OpenRecordset(strSQL)

'Avoir ou créer l'objet Excel
On Error Resume Next
Set ExcelApp = GetObject(, "Excel.Application")

If Err.Number <> 0 Then
    Set ExcelApp = New Excel.Application
        Err.Clear
End If

' Créer le classeur Excel
Set ExcelWBook = ExcelApp.Workbooks.Add
Set ExcelWSheet = ExcelWBook.ActiveSheet

' Exporter le nom des champs
For j = 0 To rstSource.Fields.Count
    ExcelWSheet.Cells(2, j + 1) = rstSource.Fields(j).Name
Next j

' Exporter les données
rstSource.MoveFirst
For i = 0 To rstSource.RecordCount
    For j = 0 To rstSource.Fields.Count
        ExcelWSheet.Cells(i + 3, j + 1) = rstSource.Fields(j).Value           <=================
    Next j
    rstSource.MoveNext
Next i
rstSource.MoveFirst

' Ajuster la largeur des colonnes automatiquement
For i = 1 To rstSource.Fields.Count
    ExcelWSheet.Columns(i).AutoFit
Next i

' Sélection de la première cellule pour déselectionner
ExcelWSheet.Cells(i + 1, j - 1).Select

'ActiveCell.FormulaR1C1 = "=SUM(B1:B2)"

' Afficher Excel pour modifications ultérieures
ExcelApp.Visible = True

'On flushe l'objet
Set ExcelApp = Nothing
Set ExcelWBook = Nothing
Set ExcelWSheet = Nothing
End Sub
0
 

Author Comment

by:rouxjean
ID: 9903256
What i'm trying to say is what if there is a third record, the code won't work anymore !!! It will show only the first two !!!
0
 

Author Comment

by:rouxjean
ID: 9903294
What is strange though is if my recordset is only the content of a table, it'll work...
0
 
LVL 8

Accepted Solution

by:
ampapa earned 2000 total points
ID: 9903498
O.K here is the scoop. In the help file it states "The RecordCount property doesn't indicate how many records are contained in a dynaset-, snapshot-, or forward-only–type Recordset object until all records have been accessed. Once the last record has been accessed, the RecordCount property indicates the total number of undeleted records in the Recordset or TableDef object. To force the last record to be accessed, use the MoveLast method on the Recordset object."

So because you were starting at the first record "rstSource.MoveFirst" your count is off. I just added a variable to get the count. Hope this helps.

ampapa,



Option Compare Database
Option Explicit

Private Sub Commande0_Click()
Dim rstSource As DAO.Recordset
Dim ExcelApp As Excel.Application
Dim ExcelWBook As Excel.Workbook
Dim ExcelWSheet As Excel.Worksheet
Dim i, j, rstcount As Integer                     <=====================
Dim strSQL As String

'strSQL = "SELECT clients.Nom, clients.Prenom, soldes.Solde FROM clients INNER JOIN soldes ON clients.ID = soldes.ID WHERE (([soldes]![IDClient]=[clients]![ID]));"
strSQL = "SELECT clients.Nom, clients.Prenom, soldes.Solde FROM clients INNER JOIN soldes ON clients.ID = soldes.ID WHERE ((([clients]![ID])=[soldes]![IDClient]));"
Set rstSource = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
rstSource.MoveLast                                <=====================
rstcount = rstSource.RecordCount           <=====================

'Avoir ou créer l'objet Excel
On Error Resume Next
Set ExcelApp = GetObject(, "Excel.Application")

If Err.Number <> 0 Then
    Set ExcelApp = New Excel.Application
        Err.Clear
End If

' Créer le classeur Excel
Set ExcelWBook = ExcelApp.Workbooks.Add
Set ExcelWSheet = ExcelWBook.ActiveSheet

' Exporter le nom des champs
For j = 0 To rstSource.Fields.count
    ExcelWSheet.Cells(2, j + 1) = rstSource.Fields(j).Name
Next j

' Exporter les données
rstSource.MoveFirst
For i = 1 To rstcount 'rstSource.RecordCount               <=====================
    For j = 0 To rstSource.Fields.count
        ExcelWSheet.Cells(i + 3, j + 1) = rstSource.Fields(j).Value
    Next j
    rstSource.MoveNext
Next i
rstSource.MoveFirst

' Ajuster la largeur des colonnes automatiquement
For i = 1 To rstSource.Fields.count
    ExcelWSheet.Columns(i).AutoFit
Next i

' Sélection de la première cellule pour déselectionner
ExcelWSheet.Cells(i + 1, j - 1).Select

'ActiveCell.FormulaR1C1 = "=SUM(B1:B2)"

' Afficher Excel pour modifications ultérieures
ExcelApp.Visible = True

'On flushe l'objet
Set ExcelApp = Nothing
Set ExcelWBook = Nothing
Set ExcelWSheet = Nothing
End Sub
0
 

Author Comment

by:rouxjean
ID: 9908182
Thanks :-)
I have this so far :
======================================================================
Option Compare Database
Option Explicit

Private Sub Commande0_Click()
Dim rstSource As DAO.Recordset
Dim ExcelApp As Excel.Application
Dim ExcelWBook As Excel.Workbook
Dim ExcelWSheet As Excel.Worksheet
Dim i, j, rstcount As Integer
Dim strSQL As String

strSQL = "SELECT clients.Nom, clients.Prenom, soldes.Solde FROM clients INNER JOIN soldes ON clients.ID = soldes.IDClient;"
Set rstSource = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
rstSource.MoveLast
'Compter le nombre d'enregistrements dans le recordset
rstcount = rstSource.RecordCount

'Avoir ou créer l'objet Excel
On Error Resume Next
Set ExcelApp = GetObject(, "Excel.Application")

If Err.Number <> 0 Then
    Set ExcelApp = New Excel.Application
        Err.Clear
End If

' Créer le classeur Excel
Set ExcelWBook = ExcelApp.Workbooks.Add
Set ExcelWSheet = ExcelWBook.ActiveSheet

' Exporter le nom des champs
For j = 0 To rstSource.Fields.Count
    ExcelWSheet.Cells(2, j + 1) = rstSource.Fields(j).Name
Next j

' Exporter les données
rstSource.MoveFirst
For i = 1 To rstcount
    For j = 0 To rstSource.Fields.Count
        ExcelWSheet.Cells(i + 3, j + 1) = rstSource.Fields(j).Value
    Next j
    rstSource.MoveNext
Next i
rstSource.MoveFirst

' Ajuster la largeur des colonnes automatiquement
For i = 1 To rstSource.Fields.Count
    ExcelWSheet.Columns(i).AutoFit
Next i

' Sélection de la dernière cellule pour calculer
ExcelWSheet.Cells(rstcount + 4, rstSource.Fields.Count).Select

'Calculer le total
ActiveCell.FormulaR1C1 = "=SUM(R[-" & rstcount & "]C:R[-1]C)"

' Afficher Excel pour modifications ultérieures
ExcelApp.Visible = True

'On flushe l'objet
Set ExcelApp = Nothing
Set ExcelWBook = Nothing
Set ExcelWSheet = Nothing
End Sub
======================================================================
The problem is when i click on the button to export once, close Excel without saving and click on the button again, Excel seems to freeze.

Any idea ?
0
 
LVL 8

Expert Comment

by:ampapa
ID: 9908459
You should "quit" Excel before setting the object to nothing that may help.

ExcelApp.quit

You might try using  -  "CreateObject("Excel.Application")" instead of "GetObject(, "Excel.Application")"
0
 

Author Comment

by:rouxjean
ID: 9908538
Using create object fixed the problem :-D
However, this line :
===========================================
ActiveCell.FormulaR1C1 = "=SUM(R[-" & rstcount & "]C:R[-1]C)"
===========================================
executes only once even when i click on the button to export twice !!!
So i guess there is still problems with the object closing.
I have to close and re-load the database in order for it to work again.
I have no error at all, the focus is on the good cell but the sum is just ignored.

Any idea ?
0
 
LVL 8

Expert Comment

by:ampapa
ID: 9908866
Replace:
'Calculer le total
ActiveCell.FormulaR1C1 = "=SUM(R[-" & rstcount & "]C:R[-1]C)"

with:
ExcelWSheet.Cells(rstcount + 4, rstSource.Fields.Count).FormulaR1C1 = "=SUM(R[-" & rstcount & "]C:R[-1]C)"
0
 

Author Comment

by:rouxjean
ID: 9911072
Once again, that worked great :-)
Many thanks for your help !!!
0
 
LVL 8

Expert Comment

by:ampapa
ID: 9911533
Your very welcome....
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses

609 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question