Slow A97 module running in A2002

[Adam]

Hi All,

I have the below module, which converts a database field from an
integer into a date format (UK Date .i.e dd/mm/yyyy).

Please see below:

Function ConvertDate(InputValue As Variant) As Variant
Dim lngDay As Long
Dim lngMonth As Long
Dim lngYear As Long

If IsNull(InputValue) = False Then
lngYear = InputValue \ 65536
lngMonth = (InputValue Mod 65536) \ 256
lngDay = InputValue - (lngYear * 65536) - (lngMonth * 256)
ConvertDate = DateSerial(lngYear, lngMonth, lngDay)
Else
ConvertDate = Null
End If

End Function


This ran quite quickly in Access 97 however now that I've exported it
into my Access XP database it is running a lot slower!!!

Can anyone see why it would be so slow now? Is there any improvements
that could be made to the module code??

Many Thanks for your help


Adam

 

[Douglas J Steele]

Realistically, you don't need such a function. Dates are stored as numbers:
there's no need to do any conversion to use the number as a date.

Look at the following code I just ran in the Debug window (Ctrl-G):

?Format(Date(), "#")
38670
?Format(38670, "dd/mm/yyyy")
14/11/2005

 

[Adam]

Will this work?? Seems so simple?

You see all of the calculations that my module is running?

 

[Douglas J Steele]

Sorry, you're right. Your encoding scheme is different than what Access uses
to store dates.

Can you change so that you do use the same scheme? (In Access, dates are
stored as the number of days relative to 30 Dec, 1899, and times are stored
as a fraction of a day)

I'm not sure there's any way to speed up your existing code.

 

[Adam]

I'm using Access to pull data off of the SQL server. There is a call
logging program called RoyalBlue Touchpaper which records dates in this
format.

 

[aaron.kempf]

well you could keep your DATA in a DATABASE instead of in a bunch of
MDB files.

try using Access Data Projects; they rock!!

 

[Douglas J Steele]

Ouch. Not sure there's anything that can be done, then: nothing about your
code looks like it should be causing delays.

 

[Adam]

I've just been reading about these.

Are they good? How do you use them?

Wouldn't I just have the same problem? I still need to convert the
Touchpaper date into something recognisable..

I found this on the web, it explains that the date returned from
Touchpaper is a HEX format? Apparently it means something to
developers:
http://www.vbcity.com/forums/faq.asp?fid=44&cat=Date/Time&

Is this something I could do with Access Projects?

 

[aaron.kempf]

ADP rock

use them with MSDE and it wont cost you anymore than MDB-- but it runs
a lot better and the query experience is a LOT better.

if a date is in hex format-- yeah you should give us some samples..

things like this are easy to write as SQL udfs-- user defined
functions; and then once you use them once; they can be re-used
anywhere

-Aaron

 

[Adam]

Ok, there's an example on this website:
http://www.vbcity.com/forums/faq.asp?fid=44&cat=Date/Time&

The hex sample: 131336971

This represents the date 11/11/2004 (dd/mm/yyyy)


Where are the queries and tables stored in an ADP ? I want to create
forms which show the queries etc. Are these all stored in the ADP
database? Is this still possible?

 

[aaron.kempf]

yes; ADP stores all the tables and queries in a db server

it makes things a lot more manageable.. i love that platform

 

[Adam]

So it stores them locally, in the ADP? Or on the SQL server?

Also if I only have read-only access, does this effect anything?

 

[aaron.kempf]

read only access to the SQL Server?

yeah, of course it'll effect you; you won't be able to write any
queries (views and sprocs)

I woudl reccomend asking them for a new database, just for you to use;

say the db you want to report on is called Amazon

get a new db named adam on the same db server; and then if you ever
want to refer to stuff on the db named amazon; you just say things like
this

Select FieldName from Amazon.dbo.TableName

that way you dont have to setup links between databases-- it's just a
lot easier to deal with and it'll still let you get to stuff in other
databases (without re-importing everythign into your database)

it's just a lot more powerful than mdb; it might be a pain-- but it
works; and it's a LOT better queries than mdb queries.. i mean.. sprocs
and views beat queries any day