Sad Der
accessd666 at yahoo.com
Thu Jan 22 00:25:38 CST 2004
Here is (part of) the code:
PS: I found the GetMaxDate() function, I think on
wolfwares.com!
'=========================================================================================
' Function Name : FillStamMeetProces
' Parameters : No parameters
' Return value : (String) <MEANING>
' Purpose : <PURPOSE>
' Assumptions : ---
' Uses : ---
' Created : 2003-10-24 10:12, SaDe
' Modifications :
'=========================================================================================
Function FillStamMeetProces(strProces As String) As
String
'150 StamMeetData
'210 StamMeetData
Dim strMeD As String 'Message_date
Dim strSQL As String
Dim strRecordcount As String
On Error GoTo FillStamMeetProces_Error
Select Case strProces
Case "SM"
'Cleanup table
DeleteRecords strProces
'----------------------------------------
' 150 Te verwerken ontvangen stamgegevens
'----------------------------------------
'---------------------------------------------------------
'MaxDate()
'---------------------------------------------------------
strSQL = vbNullString
'SQL statement to fetch most recent date
strSQL = "SELECT Max([Bericht 150 (Update Master
Data)].Message_Date) AS MaxOfMessage_Date " & _
"FROM [Bericht 150 (Update Master
Data)] " & _
"WHERE Connect_EAN =" & "'" &
gstrSelectedEan & "' "
strMeD = GetMaxDate(strSQL)
If Len(strMeD) = 0 Then
strMeD = "N/A"
End If
'---------------------------------------------------------
'Aantal Records
'---------------------------------------------------------
strSQL = vbNullString
'SQL statement to fetch most recent date
strSQL = "SELECT COUNT(*) " & _
"FROM [Bericht 150 (Update Master
Data)] " & _
"WHERE Connect_EAN =" & "'" &
gstrSelectedEan & "' "
strRecordcount = GetRecordCount(strSQL)
'Check if a date was returned
If strRecordcount = vbNullString Then
strRecordcount = "0"
End If
'Create new record
AddRecords strProces, "150", strMeD,
strRecordcount
'----------------------------------------
' 210 Te verwerken ontvangen meetdata
'----------------------------------------
'---------------------------------------------------------
'MaxDate()
'---------------------------------------------------------
strMeD = vbNullString
strSQL = vbNullString
'SQL statement to fetch most recent date
strSQL = "SELECT Max(V2_210_MEDTD1.message_date)
AS MaxOfmessage_date " & _
"FROM V2_210_MEDTD1 " & _
"WHERE Connect_EAN =" & "'" &
gstrSelectedEan & "' "
strMeD = GetMaxDate(strSQL)
If Len(strMeD) = 0 Then
strMeD = "N/A"
End If
'---------------------------------------------------------
'Aantal Records
'---------------------------------------------------------
strSQL = vbNullString
'SQL statement to fetch most recent date
strSQL = "SELECT COUNT(*) " & _
"FROM V2_210_MEDTD1 " & _
"WHERE Connect_EAN =" & "'" &
gstrSelectedEan & "' "
strRecordcount = GetRecordCount(strSQL)
'Check if a date was returned
If strRecordcount = vbNullString Then
strRecordcount = "0"
End If
'Create new record
AddRecords strProces, "210", strMeD,
strRecordcount
Case Else
MsgBox "UNKNOW PROCES!"
End Select
FillStamMeetProces_Exit:
' Collect your garbage here
Exit Function
FillStamMeetProces_Error:
' Collect your garbage here
Call
g_oGenErr.Throw("Switchdatabase.mMultiInterface",
"FillStamMeetProces")
End Function
Function GetMaxDate(strSQL As String) As String
'Dim strSQL As String
Dim conn As ADODB.Connection
Dim rstBericht As ADODB.Recordset
Dim strMaxDate As String
Set conn = CurrentProject.Connection
Set rstBericht = New ADODB.Recordset
With rstBericht
.ActiveConnection = conn
.CursorType = adOpenKeyset
.LockType = adLockReadOnly
.Source = strSQL
.Open
If .RecordCount > 0 Then
If IsNull(.Fields(0)) Then
strMaxDate = vbNullString
Else
strMaxDate = .Fields(0)
End If
Else
strMaxDate = vbNullString
End If
End With
'Return value
GetMaxDate = strMaxDate
Set rstBericht = Nothing
End Function
'=========================================================================================
' Function Name : AddRecords
' Parameters : strProces As String
' strBericht As String
' strDatum As String
' strAantal As String
' Return value : (Boolean) <MEANING>
' Purpose : Inserts records into
tblMultiInterface. This table is used to display
detail
' information regarding the
switch, verhuis, stam & meetdata and contract
' processes
' Assumptions : ---
' Uses : ---
' Created : 2003-10-21 09:00, SaDe
' Modifications :
'=========================================================================================
Function AddRecords(strProces As String, strBericht As
String, strDatum As String, strAantal As String) As
Boolean
Dim strSQL As String
On Error GoTo AddRecords_Error
strSQL = "INSERT INTO tblMultiInterface VALUES ("""
& strProces & """,""" & strBericht & """,""" &
strDatum & """,""" & strAantal & """ )"
DoCmd.RunSQL strSQL
AddRecords_Exit:
' Collect your garbage here
Exit Function
AddRecords_Error:
' Collect your garbage here
Call
g_oGenErr.Throw("Switchdatabase.Form_fMultiInterface",
"AddRecords")
End Function
--- Andy Lacey <andy at minstersystems.co.uk> wrote:
> Sander
> Rather than attaching the code try pasting it into
> the email. Shouldn't be a
> size issue then, unless, of course, there's miles of
> it! I don't know about
> anyone else but I certainly would get a clearer
> picture of what you're doing
> by seeing the code. Can't really get a handle on it
> yet.
>
> Andy Lacey
> http://www.minstersystems.co.uk
>
> > -----Original Message-----
> > From: accessd-bounces at databaseadvisors.com
> > [mailto:accessd-bounces at databaseadvisors.com] On
> Behalf Of Sad Der
> > Sent: 21 January 2004 08:40
> > To: Acces User Group
> > Subject: [AccessD] Major Performance Issue!
> >
> >
> > Hi group,
> >
> > I've got a MAJOR performance issue. I need to
> display
> > a lot of data. A performance test showed that it
> takes
> > between 120 - 160 seconds (2 minutes - 2.40
> minutes)
> > to display the data! I have no idea how to speed
> > things up dramaticly. I really could use some
> tips.
> >
> > Here's a description of the current environment:
> > Access 2000 Client (size: 20 Mb)
> > Access 2000 Back-end (size: 100 Mb)
> > approx 4 users
> > Novel network
> >
> > Functionality:
> > I need to display 4 listbox' with data.
> >
> > Every listbox has the same table as source.
> > Every listbox has a query as RowSource
> > Every listbox has this layout:
> >
> > Proces Type | Date | # Records
> >
> > Listbox 1 displays 4 records
> > Listbox 2 displays 2 records
> > Listbox 3 displays 6 records
> > Listbox 4 displays 3 records
> >
> > So the table (tblMultiInterface) has 15 records in
> > total.
> >
> > Table tblMultiInterface
> > Fields:
> > Proces Type Text
> > Date Text (!)
> > Records Text (!) (zero records need to
> display
> > N/A)
> >
> > I added the code as an attachment but unfortunatly
> > this mail was blocked! If anybody would like to
> take a
> > look at the code I can send it to you directly.
> >
> > Comments/tips/etc would be GREATLY appriciated!
> >
> > TIA
> > Sad
> >
> >
> > __________________________________
> > Do you Yahoo!?
> > Yahoo! Hotjobs: Enter the "Signing Bonus"
> Sweepstakes
> > http://hotjobs.sweepstakes.yahoo.com/signingbo>
> nus
> >
> > _______________________________________________
> > AccessD
> > mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/a>
> ccessd
> > Website:
> > http://www.databaseadvisors.com
> >
> >
>
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
__________________________________
Do you Yahoo!?
Yahoo! SiteBuilder - Free web site building tool. Try it!
http://webhosting.yahoo.com/ps/sb/