Simple Date SQL Errors on One Machine but not all the rest 
Author Message
 Simple Date SQL Errors on One Machine but not all the rest

Hi  Can anybody help ?
I have  a Small network of 12 machines running on windows 2000 server
All the machines are Dell's running  Windows2000 Pro, Windows98 and 2 with
Windows M.E. except One. This Machine Is an e-Machine with a celerion chip
which I have Re-Installed  with Windows2000 Pro for security purposes.There
are two Problems with this Installation
 The code supplied below written in VB6 SP5 deletes everything older than
100 days and works fine on all the Dell's but RUN TIME ERRORS on the
e-Machine.
Has anyboby had a similar problem and how did they get over it

Regards John Roy

'------------------------------------------------------------
Private Sub Form_Load()
Dim SQLstr As String
Dim DateFROM As Date
Dim DateFROM2 As String

    Me.Left = (Screen.Width - Me.Width) / 2
    Me.Top = (Screen.Height - Me.Height) / 2
    Me.Caption = Me.Caption & " for  " & companyName
    Me.MousePointer = vbArrow

DateFROM = Format(Date, "dd/mm/yy")
DateFROM = DateFROM - PurchaseHistoryDeleteDays ' =100 days
DateFROM2 = Mid(DateFROM, 4, 2) & "/" & Mid(DateFROM, 1, 2) & "/" &
Mid(DateFROM, 7, 2)

AdoPurchaseHistory.ConnectionString = conStrMain
SQLstr = "SELECT * FROM PurchaseHistoryTable WHERE ArchivedDate < #" &
DateFROM2 & "#"
AdoPurchaseHistory.RecordSource = SQLstr
AdoPurchaseHistory.Refresh
'-------------Delete everything over 6 months old------------------------
With AdoPurchaseHistory.Recordset
If .RecordCount > 0 Then .MoveFirst
Do Until .EOF
.Delete 1
.MoveNext
Loop
End With
'-------------Refresh
AdoPurchaseHistory.ConnectionString = conStrMain
AdoPurchaseHistory.RecordSource = "SELECT * FROM PurchaseHistoryTable ORDER
BY PurchaseNumber,ItemToPurchase"
AdoPurchaseHistory.Refresh

End Sub
'-------------------------------------------------------------------
re: Simple Date SQL Errors on One Machine but not all the rest



Thu, 02 Oct 2003 12:01:08 GMT  
 Simple Date SQL Errors on One Machine but not all the rest
I strongly suspect that your eMachine has a different regional date setting
to the others. Check the regional date setting in the control panel|regional
settings

Your code to get DateFrom and DateFrom2 is highly suspect also:
DateFROM = Format(Date, "dd/mm/yy")
DateFROM = DateFROM - PurchaseHistoryDeleteDays ' =100 days
DateFROM2 = Mid(DateFROM, 4, 2) & "/" & Mid(DateFROM, 1, 2) & "/" &
Mid(DateFROM, 7, 2)
I ran this code on 16/04/01. (16th april 2001)
With my dateformat set to dd/mm/yyyy, datefrom gets 16/04/2001 and then
DateFROM2 gets 04/16/20
With my date format set to MM/dd/yyyy, datefrom gets 04/01/2016 and then
DateFROM2= 01/04/20

It has a lot to do with you attemting to format the date and then coerce
that new date into a date datatype.
Instead of all that, why not cut out the DateFROM completely and use:
DateFROM2 = Format(Date - PurchaseHistoryDeleteDays, "dd/mm/yy")

Much more robust.

Also, you could speed things up a lot by executing a delete command rather
than getting the recordset and deleting the individual records:

Set ADOConn=New adodb.connection
ADOConn.Open conStrMain
ADOConn.Execute "DELETE FROM PurchaseHistoryTable WHERE ArchivedDate < #" &
DateFROM2 & "#"
AdoPurchaseHistory.Refresh


Quote:
> Hi  Can anybody help ?
> I have  a Small network of 12 machines running on windows 2000 server
> All the machines are Dell's running  Windows2000 Pro, Windows98 and 2 with
> Windows M.E. except One. This Machine Is an e-Machine with a celerion chip
> which I have Re-Installed  with Windows2000 Pro for security
purposes.There
> are two Problems with this Installation
>  The code supplied below written in VB6 SP5 deletes everything older than
> 100 days and works fine on all the Dell's but RUN TIME ERRORS on the
> e-Machine.
> Has anyboby had a similar problem and how did they get over it

> Regards John Roy

> '------------------------------------------------------------
> Private Sub Form_Load()
> Dim SQLstr As String
> Dim DateFROM As Date
> Dim DateFROM2 As String

>     Me.Left = (Screen.Width - Me.Width) / 2
>     Me.Top = (Screen.Height - Me.Height) / 2
>     Me.Caption = Me.Caption & " for  " & companyName
>     Me.MousePointer = vbArrow

> DateFROM = Format(Date, "dd/mm/yy")
> DateFROM = DateFROM - PurchaseHistoryDeleteDays ' =100 days
> DateFROM2 = Mid(DateFROM, 4, 2) & "/" & Mid(DateFROM, 1, 2) & "/" &
> Mid(DateFROM, 7, 2)

> AdoPurchaseHistory.ConnectionString = conStrMain
> SQLstr = "SELECT * FROM PurchaseHistoryTable WHERE ArchivedDate < #" &
> DateFROM2 & "#"
> AdoPurchaseHistory.RecordSource = SQLstr
> AdoPurchaseHistory.Refresh
> '-------------Delete everything over 6 months old------------------------
> With AdoPurchaseHistory.Recordset
> If .RecordCount > 0 Then .MoveFirst
> Do Until .EOF
> .Delete 1
> .MoveNext
> Loop
> End With
> '-------------Refresh
> AdoPurchaseHistory.ConnectionString = conStrMain
> AdoPurchaseHistory.RecordSource = "SELECT * FROM PurchaseHistoryTable
ORDER
> BY PurchaseNumber,ItemToPurchase"
> AdoPurchaseHistory.Refresh

> End Sub
> '-------------------------------------------------------------------
> re: Simple Date SQL Errors on One Machine but not all the rest



Fri, 03 Oct 2003 17:24:09 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Error 3071...Why on one machine and not on others

2. Simple jet SQL query will not work for one table but will for another

3. Two Questions, one simple, one not so!

4. Using one fill-in field as a bookmark reference for rest of document

5. V7 Access code runs on one machine and not another

6. Progam runs on one machine, not on another

7. Excel file opened in IE3 - on one machine starts Excel, others does not

8. Macro Works One Machine Not Another

9. Vb6 and Word working fine on one machine but not on other

10. Vb6 and Word working fine on one machine but not on other

11. File sharing Lock Count Error on one machine only

12. Runtime Error on One Machine

 

 
Powered by phpBB® Forum Software