Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old November 6th, 2003, 01:26 PM
Registered User
Join Date: Nov 2003
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default Form upgrade Access97 to Access2000

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

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

Old November 7th, 2003, 12:08 AM
sal sal is offline
Friend of Wrox
Join Date: Oct 2003
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts

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.


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to use mscomct2.ocx in Access97 bacnk Access 5 April 26th, 2005 07:52 AM
How to use mscomct2.ocx in Access97 bacnk Access 0 April 4th, 2005 03:32 AM
Re: forms lock down problem on Access97 flyfish Access 3 March 17th, 2005 09:09 PM
Re: forms lock down problem on Access97 flyfish Access 0 March 16th, 2005 03:34 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.