Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
copyfromrecordset fails after upgrade to XP and Excel 2003
Howdy! I'm wondering if anyone else has been experiencing the same sort
of wackiness I have been seeing with my macros after an upgrade to Excel 2003. They were developed under Windows 2000, Excel 2000. They are all using ADO 2.5 to read from an Access 2000 database, and they all used to work like a charm. Now some of my users are getting OS/application upgrades, and I'm finding that when they do, the CopyFromRecordset method raises an error, and if they hit "Debug" and F5 or F8 to continue, it magically works without error. So, to sum up, it always fails the first time, but if they debug and continue, it works. My workaround so far is just a roll-your-own, iterate-through-the-recordset subroutine, which is noticeably slower. Here's the original code: ----begin k0de snippet---- pconCN.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & PATH_TO_DATABASE & ";User ID=admin;Password=;" Set prsTemp.ActiveConnection = pconCN prsTemp.CursorType = adOpenStatic prsTemp.CursorLocation = adUseClient psSQL = "SELECT " Do While Len(psFieldName) 0 psSQL = psSQL & "[" & psFieldName & "], " plColCounter = plColCounter + 1 psFieldName = Cells(9, plColCounter) Loop psSQL = Left$(psSQL, Len(psSQL) - 2) psSQL = psSQL & " FROM PLdata " psSQL = psSQL & "WHERE [PL Date] = #" & Sheet2.ComboBox1.Value & "# " psSQL = psSQL & "ORDER BY [PL Date] DESC, ID ASC" prsTemp.Open psSQL Application.Calculation = xlCalculationManual Sheets("Priority List").Activate Range("A10").CopyFromRecordset prsTemp ' <--- here be dragyns!! ----end k0de snippet---- The recordset itself contains no weird data types or anything, and the number of rows returned is only a few hundred. I have already been searching the Knowledge Base, the web, and the newsgroups, and I haven't found any other references to this method failing *once* and then continuing. I *have* already tried referencing a newer version of ADO instead of 2.5 (2.7 was the highest already on the user's machine, and our desktops are pretty tightly "managed" in-house here, so I haven't downloaded 2.8 or whatever the latest one is). Any ideas would be GREATLY appreciated. Thanks! Jim Porter |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Upgrade to Excel 2003 -Will not print! | New Users to Excel | |||
Upgrade from Excel 97 to 2003 | Excel Discussion (Misc queries) | |||
Excel 2003 - 'CopyFromRecordset' Error | Excel Programming | |||
Excel fails me - upgrade to database? | Excel Programming | |||
Workbooks.Open fails after upgrade to XL2000 from XL97 | Excel Programming |