Solved

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

Posted on 2003-11-11
13
1,072 Views
Last Modified: 2009-04-19
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
Comment
Question by:SpinManK
  • 6
  • 5
  • 2
13 Comments
 
LVL 32

Expert Comment

by:jadedata
ID: 9727284
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
 
LVL 4

Expert Comment

by:inox
ID: 9730757

why do you want to use DAO if it works with ADO?
0
 

Author Comment

by:SpinManK
ID: 9731830
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
 
LVL 4

Expert Comment

by:inox
ID: 9732476

in that case, follow the suggestion of jadedata
you seem to have an old DAO library (<3.5)
0
 
LVL 32

Expert Comment

by:jadedata
ID: 9732802
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
 

Author Comment

by:SpinManK
ID: 9764261
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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 32

Expert Comment

by:jadedata
ID: 9764424
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
 

Author Comment

by:SpinManK
ID: 9765772
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
 
LVL 32

Expert Comment

by:jadedata
ID: 9765847
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
 

Author Comment

by:SpinManK
ID: 9766021
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
 
LVL 32

Accepted Solution

by:
jadedata earned 80 total points
ID: 9766257
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
 

Author Comment

by:SpinManK
ID: 9766411
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
 
LVL 32

Expert Comment

by:jadedata
ID: 9766905
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

937 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

Need Help in Real-Time?

Connect with top rated Experts

4 Experts available now in Live!

Get 1:1 Help Now