Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1099
  • Last Modified:

Using CopyFromRecordset to Fill a Spreadsheet with data from a DAO Recordset

I´m trying to fill a spreadsheet (the activex control, I'm using Microsoft Office Spreadsheet 10.0) with data I'm retrieving from a query.
I want to use the CopyFromRecordset method. The documentation says de it can be used with ADO and DAO recordsets. It works fine with ADO recordsets, but I get an error message when i try to use with DAO recordset. The message is something like
"Error '430' in execution time: This class does not admit Automatization or does not admit the expected interface".

The code I'm using is:

Dim res As DAO.Recordset
Set res = CurrentDb.OpenRecordset(sql)
disponibilidad = res.GetRows(res.RecordCount)
nPersonas = UBound(disponibilidad, 2) + 1

With Me.datosTurnos
    .ScreenUpdating = False
    .ActiveSheet.Range("A1").EntireRow.Hidden = False
    .ActiveSheet.Range(.ActiveWindow.ViewableRange).Clear
    .TitleBar.interior.Color = "Grey"
    With .ActiveSheet
       .Cells.Font.Name = "Tahoma"
       .Cells.CopyFromRecordset res2

That's it... What can I do?
0
SpinManK
Asked:
SpinManK
  • 6
  • 5
  • 2
1 Solution
 
jadedataMS Access Systems CreatorCommented:
Hey SpinManK!

  Make sure the MS DAO 3.6 library is referenced and is not listed as missing if it is referenced.
 
  this line:
  .Cells.CopyFromRecordset res2
  I believe needs to be specific to the upper left cell in the range to be filled.
  .Range("A2").copyfromrecordset res2

  I don't see a declaration for res2 or the instantiation of the Excel object.  I presume that they are "out-of-range" of the code snippit.

regards
Jack
0
 
inoxCommented:

why do you want to use DAO if it works with ADO?
0
 
SpinManKAuthor Commented:
I want to use DAO because when I use the CurrentDB.OpenRecordset("...") method I receive a DAO recordset. I know there are ways to get the answer of a query in an ADO recordset (such as using the open method of ADO.Recordset), but in that case I have problems connecting to the database (when multiple users connect at the same time...)

Somehing else, in the code I put in the question, res and res are the same, it was an error transcribing the code, but it´s ok in my code.
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.

 
inoxCommented:

in that case, follow the suggestion of jadedata
you seem to have an old DAO library (<3.5)
0
 
jadedataMS Access Systems CreatorCommented:
DAO 3.6 if you are in Access2K/XP

And while a crowd of my associates will disagree with me,... the DAO object model still exposes ALL the properties and methods of the DAO object, unlike the ADO.  I will use it until it's dead and buried, or until ADO exposes the same stuff.

a note... the lines
  disponibilidad = res.GetRows(res.RecordCount)

might be changed to:
  res.movelast
  disponibilidad = res.recordcount
  res.movefirst

Getrows is an ADO method.
0
 
SpinManKAuthor Commented:
I have DAO 3.6 referenced. I really don´t understan what's happening. Everywhere and everyone says that CopyFromRecordset works fine with DAO, but it doesn't. In fact, I tried to use it in a new and empty form, and still doesn't work. I also tried to convert my DAO recordset to ADO, but I couldn't. So I supose I'll have to get back to ADO...
If anyone has any idea, thanks...
0
 
jadedataMS Access Systems CreatorCommented:
It does work with DAO.  I should know, all I currently use is DAO.  Something else is afoot here...
Did you read my last post??
0
 
SpinManKAuthor Commented:
Hi jadedata!

Yes, I red your post, maybe I didn't understand it completely.
I have DAO 3.6 referenced, so that shouldn´t be a problem.

About the lines you suggested me to change, I did it, but with no difference.
I tested a more simple example and I get the same error message. I have a Spreadsheet named 'horasTurnos' in my form. I use the following code:

-------------------------------------------
Private Sub horasTurnos_Initialize()
    Call llenarHoras
End Sub
-------------------------------------------

-------------------------------------------
Public Function llenarHoras()
    Dim res As DAO.Recordset
    Dim sql as String
    sql = "Select idTurno,fecha,horaInicio,horaTermino FromTurnos " _
         & "Where vigencia " _
         & "Order By fecha,horaInicio,horaTermino"
    Set res = CurrentDb.OpenRecordset(sql)
    Me.horasTurnos.Cells.CopyFromRecordset res
End Function
-------------------------------------------

I receive the following error message:
"Error '430' in execution time: This class does not admit Automatization or does not admit the expected interface".

I also tried using a Spreadsheet object declared within the code (not the one appearing in mi form). I got the same error.

So that's why I don't know what to do...

Thank you anyway
0
 
jadedataMS Access Systems CreatorCommented:
Your code makes no reference to an Excel Object.

CopyFromRecordset is an Excel method and only works against an Excel object

What happened to the code that opened the Excel object??

 
0
 
SpinManKAuthor Commented:
I'm not sure what do you mean with an excel object. What I have is a Spreadsheet object, which I put in my form. That's why I execute Me.horasTurnos.Cells.CopyFromRecordset res, Me.horasTurnos makes reference to the Spreadsheet 'horasTurnos' I have in my form. So I think this should work. In fact, the same code works for me with ADO recordsets, but with DAO still doesn´t work...
0
 
jadedataMS Access Systems CreatorCommented:
that IS an Excel object.  That spreadsheet object needs to be instantiated in VBA as the child of an Excel.app object via a workbook objects in order to do this.  You will not be able to push that data into the object type you are currently using, using DAO

Perhaps you should revert the code back to ADO is that is what was working for you.
0
 
SpinManKAuthor Commented:
So I can´t do it... That's why it doesn't work.
I will revert it to ADO. The only other option is converting a DAO recordset to an ADO one, but I'm not sure how to do it.

Thanks anyway...
0
 
jadedataMS Access Systems CreatorCommented:
It can be done, but you can't do it the way you are attempting, via an embedded object on a form.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 6
  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now