By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,234 Members | 1,844 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,234 IT Pros & Developers. It's quick & easy.

dao reference confusion: access 97 to 2003

P: n/a
object invalid or no longer set - confusion of the recordset in access
2003.

i am currently converting from access 97 to access 2003. majority of
the codes converted over perfectly fine, though my recordset did not.
i read from pasts posts that some references will not convert. so i
tried to change the dao 3.51 reference to dao 3.6 in access 97. then i
imported all of the objects into an access 2003. it did not work.

so my second option was to unclick the microsoft activex data ojbects
2.1 library in access 2003 and left dao 3.6 object library as my
reference. it did not work either.

so am i left to just recreating the recordset in access 2003?

i'm a little confused to why some of my recordsets executed without
any runtime/compile errors.

any suggestions or links that could direct me out of the dao reference
confusion would be
greatly appreciated :)
thanks in advance - jung
------------------------
here is the "inherited" code if anyone is curious:

Public Function GetBusDays(NearDate As Date, fardate As Date) As Long

Dim ExactDaysDiff As Single
Dim TrueDaysDiff As Long
Dim NonBusDays As Integer
Dim LoopCount As Long
Dim Dbe As Database
Dim RstDays As Recordset 'rstDays - object invalid or no longer set in
DAO 3.6

'If The Login Date is equal to Now (unlikely) or less than Now (almost
impossible)
If NearDate <= fardate Then
'then business days = 1
GetBusDays = 1
Exit Function
'If difference is less than one day...
ElseIf Format(NearDate - fardate, "#.#######") < 1 And
Format(NearDate, "dd") = Format(fardate, "dd") Then
DoCmd.Beep
'then business days "rounds up" to 1.
GetBusDays = 1
Exit Function
Else
'Difference must therefore be more than 1 day; calculate as
follows...
TrueDaysDiff = Format(NearDate - fardate, "#")
'Start the counter for non-business days...
NonBusDays = 0
Set Dbe = DBEngine(0)(0)
'gather the list of Bank Holidays once...
Set RstDays = Dbe.OpenRecordset("SELECT [BH Date] From [Bank
Holidays]")

'Loop for the gross days difference
For LoopCount = 1 To TrueDaysDiff
If Left(Format((fardate - 1 + LoopCount), "dddd"), 3) = "Sun"
Or Left(Format((fardate - 1 + LoopCount), "dddd"), 3) = "Sat" Then
'If the above statement is true, the day is a weekend
date: increment non-business days by one.
NonBusDays = NonBusDays + 1
'The day is Mon - Fri, but it could be a holiday; check
holiday list...
Else
'always start at beginning of list for this comparison...
RstDays.MoveFirst
'compare each item in the holidays list...
Do Until RstDays.EOF
If (fardate - 1 + LoopCount) >= RstDays![BH Date] And
(fardate - 1 + LoopCount) < RstDays![BH Date] + 1 Then
'The above line takes special consideration for Login
Dates as they have time-designations as well:
'The holiday date is actually the date at exactly
12:00 a.m. This calculation compares the looped date
'to the RANGE of the holiday date (from holiday date
12:00 a.m. to holiday date 11:59:59pm).
'increment non-business days by one.
NonBusDays = NonBusDays + 1
'holiday date has been identified; signal end of
loop.
RstDays.MoveLast
End If
'holiday date did not match, continue the loop...
RstDays.MoveNext
'End of holiday list (SEE SPECIAL NOTE BELOW)
Loop
'End determination of weekend, holiday, or neither.
End If
'Move to next date within range for determination of weekend,
holiday, or neither.
Next
'RstDays.Close
Dbe.Close
'Business days = gross days between now and Login Date less
weekend dates and holidays.
GetBusDays = TrueDaysDiff - NonBusDays + 1
End If
Nov 13 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
What happens if you use explicit references, e.g. Dim RstDays As
DAO.Recordset?

On 12 Aug 2004 09:18:05 -0700, pi******@yahoo.fr (JMCN) wrote:
object invalid or no longer set - confusion of the recordset in access
2003.

i am currently converting from access 97 to access 2003. majority of
the codes converted over perfectly fine, though my recordset did not.
i read from pasts posts that some references will not convert. so i
tried to change the dao 3.51 reference to dao 3.6 in access 97. then i
imported all of the objects into an access 2003. it did not work.

so my second option was to unclick the microsoft activex data ojbects
2.1 library in access 2003 and left dao 3.6 object library as my
reference. it did not work either.

so am i left to just recreating the recordset in access 2003?

i'm a little confused to why some of my recordsets executed without
any runtime/compile errors.

any suggestions or links that could direct me out of the dao reference
confusion would be
greatly appreciated :)
thanks in advance - jung
------------------------
here is the "inherited" code if anyone is curious:

Public Function GetBusDays(NearDate As Date, fardate As Date) As Long

Dim ExactDaysDiff As Single
Dim TrueDaysDiff As Long
Dim NonBusDays As Integer
Dim LoopCount As Long
Dim Dbe As Database
Dim RstDays As Recordset 'rstDays - object invalid or no longer set in
DAO 3.6

....
Nov 13 '05 #2

P: n/a
You must use DAO 3.51 in A97.
Change the reference back.
Recompile.
Then try the conversion again.

After conversion, the new database will reference DAO 3.6, which is correct.
You can deselect the ADO 2.x library if you do not need it. The problem is
that the ADO library also has a Recordset object. To be doubly sure that
things are right, do a search'n'replace (Edit menu in code window) in the
new database. Replace
As Recordset
with:
As DAO.Recordset
Then compile the application to ensure everything is undersood.

For more information on references, see:
http://members.iinet.net.au/~allenbrowne/ser-38.html

For more information on converting from A97, see:
http://members.iinet.net.au/~allenbrowne/ser-48.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"JMCN" <pi******@yahoo.fr> wrote in message
news:27**************************@posting.google.c om...
object invalid or no longer set - confusion of the recordset in access
2003.

i am currently converting from access 97 to access 2003. majority of
the codes converted over perfectly fine, though my recordset did not.
i read from pasts posts that some references will not convert. so i
tried to change the dao 3.51 reference to dao 3.6 in access 97. then i
imported all of the objects into an access 2003. it did not work.

so my second option was to unclick the microsoft activex data ojbects
2.1 library in access 2003 and left dao 3.6 object library as my
reference. it did not work either.

so am i left to just recreating the recordset in access 2003?

i'm a little confused to why some of my recordsets executed without
any runtime/compile errors.

any suggestions or links that could direct me out of the dao reference
confusion would be
greatly appreciated :)
thanks in advance - jung
------------------------
here is the "inherited" code if anyone is curious:

Public Function GetBusDays(NearDate As Date, fardate As Date) As Long

Dim ExactDaysDiff As Single
Dim TrueDaysDiff As Long
Dim NonBusDays As Integer
Dim LoopCount As Long
Dim Dbe As Database
Dim RstDays As Recordset 'rstDays - object invalid or no longer set in
DAO 3.6

'If The Login Date is equal to Now (unlikely) or less than Now (almost
impossible)
If NearDate <= fardate Then
'then business days = 1
GetBusDays = 1
Exit Function
'If difference is less than one day...
ElseIf Format(NearDate - fardate, "#.#######") < 1 And
Format(NearDate, "dd") = Format(fardate, "dd") Then
DoCmd.Beep
'then business days "rounds up" to 1.
GetBusDays = 1
Exit Function
Else
'Difference must therefore be more than 1 day; calculate as
follows...
TrueDaysDiff = Format(NearDate - fardate, "#")
'Start the counter for non-business days...
NonBusDays = 0
Set Dbe = DBEngine(0)(0)
'gather the list of Bank Holidays once...
Set RstDays = Dbe.OpenRecordset("SELECT [BH Date] From [Bank
Holidays]")

'Loop for the gross days difference
For LoopCount = 1 To TrueDaysDiff
If Left(Format((fardate - 1 + LoopCount), "dddd"), 3) = "Sun"
Or Left(Format((fardate - 1 + LoopCount), "dddd"), 3) = "Sat" Then
'If the above statement is true, the day is a weekend
date: increment non-business days by one.
NonBusDays = NonBusDays + 1
'The day is Mon - Fri, but it could be a holiday; check
holiday list...
Else
'always start at beginning of list for this comparison...
RstDays.MoveFirst
'compare each item in the holidays list...
Do Until RstDays.EOF
If (fardate - 1 + LoopCount) >= RstDays![BH Date] And
(fardate - 1 + LoopCount) < RstDays![BH Date] + 1 Then
'The above line takes special consideration for Login
Dates as they have time-designations as well:
'The holiday date is actually the date at exactly
12:00 a.m. This calculation compares the looped date
'to the RANGE of the holiday date (from holiday date
12:00 a.m. to holiday date 11:59:59pm).
'increment non-business days by one.
NonBusDays = NonBusDays + 1
'holiday date has been identified; signal end of
loop.
RstDays.MoveLast
End If
'holiday date did not match, continue the loop...
RstDays.MoveNext
'End of holiday list (SEE SPECIAL NOTE BELOW)
Loop
'End determination of weekend, holiday, or neither.
End If
'Move to next date within range for determination of weekend,
holiday, or neither.
Next
'RstDays.Close
Dbe.Close
'Business days = gross days between now and Login Date less
weekend dates and holidays.
GetBusDays = TrueDaysDiff - NonBusDays + 1
End If

Nov 13 '05 #3

P: n/a
hi allen -
thank you for your great advise and the links. i did exactly what you
suggested and it worked well. thank you again and it's very much
appreciated as i plug away at converting these access 97 databases to
access 2003!!!
cheers - jung
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message news:<41***********************@per-qv1-newsreader-01.iinet.net.au>...
You must use DAO 3.51 in A97.
Change the reference back.
Recompile.
Then try the conversion again.

After conversion, the new database will reference DAO 3.6, which is correct.
You can deselect the ADO 2.x library if you do not need it. The problem is
that the ADO library also has a Recordset object. To be doubly sure that
things are right, do a search'n'replace (Edit menu in code window) in the
new database. Replace
As Recordset
with:
As DAO.Recordset
Then compile the application to ensure everything is undersood.

For more information on references, see:
http://members.iinet.net.au/~allenbrowne/ser-38.html

For more information on converting from A97, see:
http://members.iinet.net.au/~allenbrowne/ser-48.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

Nov 13 '05 #4

P: n/a
hi steve -
your and allen's advise worked great! thanks!!!
cheers - jung

Steve Jorgensen <no****@nospam.nospam> wrote in message news:<re********************************@4ax.com>. ..
What happens if you use explicit references, e.g. Dim RstDays As
DAO.Recordset?

On 12 Aug 2004 09:18:05 -0700, pi******@yahoo.fr (JMCN) wrote:
object invalid or no longer set - confusion of the recordset in access
2003.

i am currently converting from access 97 to access 2003. majority of
the codes converted over perfectly fine, though my recordset did not.
i read from pasts posts that some references will not convert. so i
tried to change the dao 3.51 reference to dao 3.6 in access 97. then i
imported all of the objects into an access 2003. it did not work.

so my second option was to unclick the microsoft activex data ojbects
2.1 library in access 2003 and left dao 3.6 object library as my
reference. it did not work either.

so am i left to just recreating the recordset in access 2003?

i'm a little confused to why some of my recordsets executed without
any runtime/compile errors.

any suggestions or links that could direct me out of the dao reference
confusion would be
greatly appreciated :)
thanks in advance - jung
------------------------
here is the "inherited" code if anyone is curious:

Public Function GetBusDays(NearDate As Date, fardate As Date) As Long

Dim ExactDaysDiff As Single
Dim TrueDaysDiff As Long
Dim NonBusDays As Integer
Dim LoopCount As Long
Dim Dbe As Database
Dim RstDays As Recordset 'rstDays - object invalid or no longer set in
DAO 3.6

...

Nov 13 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.