CopyfromRecordset DAO issues

[Len Dalberti]

I am getting a "Run-Time 430 error, Class does not support Automation or does not support expected interface" with the following code. It appears to be related to the use of DAO instead of ADODB. This code is the combination of two codes, one with DAO and the other with ADODB. So I'm trying to marry the two, but I'm having problems. I don't have any preference to stay with DAO or ADODB (which it seems is better for Excel exporting). But I need help with the code. Anyways, here is the code (up until the error) and the last line posted is what is being flagged. Thanks for your help.

And to clarify my references, these are active on my system:

Visual Basic for Applications
Microsoft Access 12.0 Object Library
OLE Automation
Microsoft Office 12.0 Access database engine Object library
Microsoft Excel 12.0 Object Library
Microsoft ActiveX Data Objects 2.1 Library

Public Sub ExportBIGrids_test()
 
    Dim rs      As DAO.Recordset
    Dim rs1     As DAO.Recordset
    Dim rs2     As DAO.Recordset
    Dim rs3     As DAO.Recordset
    Dim rstMgr  As DAO.Recordset
    Dim dbs     As DAO.Database
 
    Dim strSQL  As String
    Dim strMgr  As String
    Dim x       As New Excel.Application
    Dim w       As Excel.Workbook
    Dim s       As Excel.Worksheet
    Dim r       As Range
    Dim r1      As Range
    Dim r2      As Range
    Dim r3      As Range
    Dim d       As String
 
strSQL = "SELECT DISTINCT BlowInName FROM BlowIn_tblName"
 
Set dbs = CurrentDb()
 
Set rstMgr = dbs.OpenRecordset(strSQL)
 
strMgr = DLookup("BlowInName", "BlowIn_tblName", "BlowInName = """ & rstMgr!BlowInName & """")
 
d = "K:\OB MS Admin\Titles\Blow Ins\"
 
Set w = x.Workbooks.Open(d & "Blow In Profiles Template.xlsx")
 
 
    If rstMgr.EOF = False And rstMgr.BOF = False Then
        rstMgr.MoveFirst
        Do While rstMgr.EOF = False
 
 
            Set rs = dbs.OpenRecordset("SELECT BI_Name, BI_Title , BI_CPM FROM BlowIn_tblCPM WHERE BI_Name = """ & rstMgr!BlowInName & """")
            Set rs1 = dbs.OpenRecordset("select ProfName, ProfCreative , ProfSize, ProfWeight, ProfStatus from BlowIn_tblProfile Where ProfName = """ & rstMgr!BlowInName & """")
            Set rs2 = dbs.OpenRecordset("select BI_Name, BI_Event , BI_Creative, BI_Keycode, BI_MHMNo, BI_Qty, BI_Notes from BlowIn_tblKeys Where BI_Name = """ & rstMgr!BlowInName & """")
            Set rs3 = dbs.OpenRecordset("select BlowInName from BlowIn_tblName Where BlowInName = """ & rstMgr!BlowInName & """")
 
            Set s = w.Sheets("Template")
            Set r = s.Range("B4")
            Set r1 = s.Range("E4")
            Set r2 = s.Range("E11")
            Set r3 = s.Range("A1")
 
            r.CopyFromRecordset rs <----This is the line that is flagged
            r1.CopyFromRecordset rs1
            r2.CopyFromRecordset rs2
            r3.CopyFromRecordset rs3
 
            s.Columns("B:J").EntireColumn.AutoFit
            s.Columns("B:J").Font.Size = 10
'DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, strMgr, "K:\OB MS Admin\Titles\Blow Ins\" & "Mailer Breakdowns.xls"
 
rstMgr.MoveNext
 
        Loop
    End If
 
 
    rstMgr.Close
    rs.Close
    rs1.Close
    rs2.Close
    rs3.Close
    dbs.Close
 
    Set rs = Nothing
    Set rs1 = Nothing
    Set rs2 = Nothing
    Set rs3 = Nothing
 
 
 
    w.SaveAs d & "-" & PrevMonth(Date), , , , False
    w.Close
    x.Quit
 
    Set r = Nothing
    Set r1 = Nothing
    Set r2 = Nothing
    Set r3 = Nothing
    Set s = Nothing
    Set w = Nothing
    Set x = Nothing
    Set dbs = Nothing
    Set rstMgr = Nothing
End Sub
 
 
 
Public Function PrevMonth(d)
'Requires the D in brackets, as used in the formulas below
 
Dim M
M = Month(d)
Select Case M
    Case 2 To 12
            PrevMonth = Format(DateSerial(Year(d), M, 1), "mmm") & "_" & Year(d)
        ' Date Serial, Year, Month (M), Day of Month (1) - Needs name of function
    Case 1
            PrevMonth = "Dec" & "_" & Year(d) - 1
        ' Needs name of Function
End Select
End Function
 

[pod]

I do not see the Microsoft DAO Object Library reference in your listing, that could very well be the cause of your error message

[Len Dalberti]

I've tried to activate the Microsoft DAO 3.6 Library but it tells me that the "name conflicts with existing module, project or object library."

[NeoPa]

Try losing "Microsoft ActiveX Data Objects 2.1 Library" before adding the DAO reference.

Also, ensure all your references are explicit. Line #6 specifically is not.

Last point, try to get into the habit of putting all your Dim lines at the top of the code within whose scope it is effective. IE. Dims for a procedure should be at the start of the procedure and Dims for a module should be at the start of the module. It makes life easier both for yourself and anyone else who ever has to review your code.

[Len Dalberti]

>>>Try losing "Microsoft ActiveX Data Objects 2.1 Library" before adding the DAO reference.

Still getting the same "Name conflict"

>>>Also, ensure all your references are explicit. Line #6 specifically is not.

Done - changed to DAO.Database

>>>...putting all your Dim lines at the top of the code within whose scope it is effective...

My eyes are getting crossed with this code, so I will try to clean it up. Thanks for the tips.

[NeoPa]

The only other one that sounds as if it may clash is "Microsoft Office 12.0 Access database engine Object library". try removing that before adding in the DAO reference.

[Stewart Ross]

You have not said where this error is occurring. Could you be a bit more specific and systematic about error tracing? You are assuming it is a DAO from ADODB error; I doubt that, and I think such assumptions can be very misleading unless you have stepped through your code and found that it errors on one of the DAO recordset lines.

I note a problem at line 40 where you have an implicit reference to your Excel workbook - this will not function correctly when using Excel as an automation server unless you fully qualify the Excel object concerned:

Set w = Workbooks.Open(d & "Blow In Profiles Template.xlsx") 

should be qualified with the Excel object variable you have instantiated, unhelpfully named x:

Set w = x.Workbooks.Open(d & "Blow In Profiles Template.xlsx")

Anyhow, taking a more systematic approach to stepping through and debugging your code may well pay dividends here.

I would echo what NeoPa said about not having DIM statements away from the top of your code. There is a potential problem at line 29 within your While loop:

Dim x As New Excel.Application

This I think will create a new instance of Excel each time it is run, orphaning any current instances of Excel assigned to object variable x (unless there is clean-up code which you have not shown later in the loop that quits the Excel instance cleanly).

You just need one instance of the Excel application. Within the loop you should open and close each workbook in turn - there is no need to declare a new Excel application each time. You will need to quit Excel properly at the end of your routine (which is easy to do - it is just x.quit).

-Stewart

[NeoPa]

Nice catches Stewart. All good points :-)

[Len Dalberti]

Thank you so much Stewart and Neo!! Excellent points. As you can see, I'm very much a rookie at this type of thing. I will continue to work on it.

I did note in my first post that the last line (line 48) is where it's getting stuck at. And I do have an x.quit at the end. And yes, it compiled fine.

[Len Dalberti]

I have revised to my latest code and I have included the entire code. I tried to move the opening of the excel file and worksheet to outside of the Loop. What I think I'm missing as well is that it doesn't appear that I'm telling the code to copy the "Template" worksheet nor telling it what to name the copied worksheet.

But in any case, Line 50 is now the line is flagged (the same as before) and I'm still getting the same error message. On Line 57, I have this extraneous line of code that I'm not sure should be in there in case you're wondering.

[Len Dalberti]

So I found the problem. I needed to run this:

Regsvr32.exe "C:\Program Files\Common Files\Microsoft Shared\DAO\DAO360.dll"

I couldn't do it previously because I didn't have admin rights. It solved the problem and thank you for taking the time to look at this for me.

[NeoPa]

Len Dalberti:
I did note in my first post that the last line (line 48) is where it's getting stuck at.

Len,

While I appreciate that there is a comment in the code to that effect, that is not generally where people are going to want to look for information you need to tell us (People don't generally scan all your many lines of code to see what should be included as part of the explanation). Don't get me wrong, I see that you've done a pretty good job of telling us everything you could, but next time you should do it as part of the explanation rather than embedded in the code. Essentially, don't consider any code as part of the question itself, but as supporting information.

Anyway, well done for discovering the solution.