?
Solved

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

Posted on 2003-11-11
13
Medium Priority
?
1,084 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
[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
  • 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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
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
 
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 160 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

764 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