I have a form plus associated module that merges data from one table and updates the same fields in the main table. This worked fine when running Access97 but after upgrading to Access2000 the main table is not getting updated. the Merge operation does not crash, it just fails to write data to the main table. Any ideas on how to edit the code would be really appreciated.

Here is the current code, takes data from table called Clean and updates table called Symbols.Option Compare Database
Option Explicit

Public vSysdateDateDiff

Function Merger()
On Error GoTo ErrorHandler

Dim vFileDate
Dim Myfile

DoCmd.SetWarnings False


On Error Resume Next
Myfile = Dir("C:\Program Files\QuoteSpeed Extraction\Data" & Format(Date, "yyyymmdd") & ".csv")

If Myfile = "" Then
        GoTo ErrorHandler
End If



On Error Resume Next
DoCmd.DeleteObject acTable, "CleanFile"

'Import .CSV File
DoCmd.TransferText acImportDelim, , "CleanFile", "C:\Program Files\QuoteSpeed Extraction\Data" & Format(Date, "yyyymmdd") & ".csv", True

DoCmd.RunSQL "Alter Table CleanFile Add Column FileDate DATE"

vFileDate = Format(Date, "mm/dd") & "/2000"

DoCmd.RunSQL "Update CleanFile " & _
                            "Set FileDate = # " & vFileDate & " # "

DoCmd.RunSQL "Update Symbols Inner Join CleanFile " & _
                            "On Symbols.Symbol = CleanFile.Symbol " & _
                            "And Symbols.SymbolUpdated = CleanFile.FileDate " & _
                            "Set Symbols.Open = CleanFile.OpenTrade, " & _
                            " Symbols.High= CleanFile.HighTrade, " & _
                            " Symbols.Low = CleanFile.LowTrade, " & _
                            " Symbols.Close = CleanFile.Close "

DoCmd.SetWarnings True

MsgBox "Click OK to finish", vbOKOnly, "Your data has been merged succesfully"

Exit Function
ErrorHandler:

DoCmd.SetWarnings True

MsgBox "Please make sure that your system date is set to the the date on which " & vbCrLf & _
               "you wish to merge data and the appropriately named clean file is in it's folder.", vbCritical, "Error in Merge Process"

End Function

Public Function AlterSysdate()

Date = Date + vSysdateDateDiff

DoCmd.Close acForm, "Merge Manager", acSaveNo

DoCmd.OpenForm "Merge Manager", acNormal

End Function






GRPinkney

It does not crash because you have "On Error Resume Next" twice. you only need it once. Comment that out and step through your code to find out what step is not working.

I do notice that you are deleting the table (instead of deleting the records, do not drop the table). Import into an existing table. MS Access 2000 will not allow you to add any new items if another user has the database open.



Sal