Solved

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

Posted on 2003-11-11
13
1,076 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

828 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