Solved

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

Posted on 2003-11-11
13
1,071 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
Comment Utility
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
Comment Utility

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

Author Comment

by:SpinManK
Comment Utility
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
Comment Utility

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
Comment Utility
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
Comment Utility
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
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 32

Expert Comment

by:jadedata
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
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…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

772 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

10 Experts available now in Live!

Get 1:1 Help Now