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