docmd.transferspreadsheet freezes Excel

[blammo04]

My problem is that Access freezes up whenever I try to use the docmd.transfers preadsheet method, I can use the docmd.outputto and it works fine but I need to be able to use the transferspreads heet method so I can have multiple queries in one excel file.

If anyone can help as what the problem may be, I would greatly appreciate it.

strReps = "SELECT Software.[Software Name], Software.Version, Software.[Operating System], " & _
            "Software.Status, Software.[Status Date], Software.[Approved Platforms], " & _
            "Software.[Code Type], CUsage.[Cost Center], CUsage.[Entered On] " & _
            "FROM Software INNER JOIN CUsage " & _
            "ON Software.[Software Name] = CUsage.[Software Name] " & _
            "AND Software.Version = CUsage.Version " & _
            "AND Software.[Operating System] = CUsage.[Operating System] " & _
            "WHERE Software.[Software Name] = '" & strSoftwareNameOI & "' " & _
            "AND Software.Version = '" & strVersionOI & "' " & _
            "AND Software.[Operating System] = '" & strOSOI & "' "
            
Set db = CurrentDb()
        Set qry = db.CreateQueryDef("strSoftwareNameOI", strReps)
       
      
        If DCount("*", "strSoftwareNameOI") > 0 Then
        'MsgBox "NOT Empty"
     
       
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "strSoftwareNameOI", "C:\Documents and Settings\jmayhew\Desktop\testing.xls", True
        db.QueryDefs.Delete ("strSoftwareNameOI")
        
       Else
        'MsgBox "Empty"
          db.QueryDefs("strSoftwareNameOI").SQL = "SELECT Software.[Software Name], Software.Version, Software.[Operating System], " & _
            "Software.Status, Software.[Status Date], Software.[Approved Platforms], " & _
            "Software.[Code Type], Space(30) = '0' AS [Cost Center], Space(30) = '0' AS [Entered On] " & _
            "FROM Software " & _
            "WHERE Software.[Software Name] = '" & strSoftwareNameOI & "' " & _
            "AND Software.Version = '" & strVersionOI & "' " & _
           "AND Software.[Operating System] = '" & strOSOI & "' "

 DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "strSoftwareNameOI", "C:\Documents and Settings\jmayhew\Desktop\testing.xls", True
        db.QueryDefs.Delete ("strSoftwareNameOI")

End If

[jimatqsi]

Let's see, I've seen this, a year or more ago, I'm not sure I can tell right off the bat ... either quit Excel before you do the transfer, or make sure Excel is open, I think it's one of those two.

Or, possibly there is a window waiting for a response from you and it's hidden behind another window. Try hitting Alt-Tab to see what other windows are open. You might find a message box waiting for your input.

I'm sorry, it's too late for me to research it now, or I'd give you a more definitive answer.

Jim

[blammo04]

Jim,

Thanks for the response, I have tried both closing excel and having it open and neither works, Access still freezes and I get the program is not responding and Access closes automatically. I'm lost as to what is going on.

If you have any other ideas as to what the problem may be or any examples or anything, please let me know.

Thank you

[MMcCarthy]

Try adding in a DoEvents command between transferring the spreadsheet and deleting the query.

        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "strSoftwareNameOI", "C:\Documents and Settings\jmayhew\Desktop\testing.xls", True
        DoEvents
        db.QueryDefs.Delete ("strSoftwareNameOI")

I suspect VBA tries to delete the query before it finishes transferring the spreadsheet. The DoEvents command should take care of that.

Mary

[blammo04]

Thanks for the responses, I tried both suggestions and neither worked.

I did figure out what is causing the problem though but I am unsure of how to fix it.

The problem is coming from the ADOBD.Recordset s, and this is causing it to crash.

I ran a test sample using DAO.Recordset and it worked but for this program it has to be ADOBD.Recordset s because they reference other things in the program.

Anybody have any suggestions as to why the ADOBD.Recordset s is causing this?

Thanks.