Results 1 to 15 of 15
  1. #1
    Join Date
    Jan 2008
    Posts
    74

    Red face Unanswered: Delete query: '#deleted' message instead of deleting records

    Hi, everyone!

    I'm writing a VBA code which should delete the outdated records and replace them with the new ones. All the data comes from Excel first into a temporary table, which is being reconstructed then, and after that the temporary table is being appended at the real table.

    The problem is that my delete query doesn't work - instead of deleting these records it replaces them with message #deleted.

    Here's the code:

    Code:
    'delets old records
    DoCmd.RunSQL "DELETE tblEinzelposten.Von FROM tblEinzelposten WHERE (tblEinzelposten.Von) like '01.10.*';"
    'fills out the temporary table
    strSQL = "UPDATE Temporary SET [Von]= #10/01/" & sJahr & "#;"
    DoCmd.RunSQL strSQL
    'transfers contents of the temporary table into the actual table
    strSQL = "INSERT INTO tblEinzelposten" _
                                         & " SELECT Temporary.* FROM Temporary;"
    I'm working with Ms Access 2003, the code is set as the on-click event of a button on a form.

    Thank you so much in advance!

    Best,
    OfficeDummy

  2. #2
    Join Date
    Jul 2003
    Location
    Amsterdam, Nederland
    Posts
    450

    re

    Close the table and reopen it .
    Don't have the table open to check deletes and updates while SQL is running

    OR
    your procedure is still running, so access marks the "rows" as deleted
    Last edited by Marvels; 02-21-08 at 08:15.
    Greetz Marvels -^.^-
    Developments : VB4 Through .Net; Basic; DOS ; CNC ; Sinclair
    Databases : SQL Server Through 2005; Access 3 Through 2003 ; Oracle 8 & 9.i ;
    OS : Win 3.11 Through XP ; NortonComander ; DOS

  3. #3
    Join Date
    Jan 2008
    Posts
    74
    Thanks, Marvels! Now the #deleted marking is gone. Really stupid.

    But somehow Access doesn't delete all the records - miraculously, it leaves about 197 out of 14'000. Why, and how can I fix it?

  4. #4
    Join Date
    Jul 2003
    Location
    Amsterdam, Nederland
    Posts
    450

    re

    What is the value ( tblEinzelposten.Von) of those (197) fields

    Your deleting everythig where von = 1.10.XXXX

    so if there are fields with value 2.10.XXXX or 1.09.XXXX it would bo correct
    Greetz Marvels -^.^-
    Developments : VB4 Through .Net; Basic; DOS ; CNC ; Sinclair
    Databases : SQL Server Through 2005; Access 3 Through 2003 ; Oracle 8 & 9.i ;
    OS : Win 3.11 Through XP ; NortonComander ; DOS

  5. #5
    Join Date
    Jan 2008
    Posts
    74
    The whole column "Von" is filled with records like '01.10.2007' or '01.11.2007' and so on, data type is Date/Time. I want my code to delete all records '01.10.2007' and replace them with '01.10.2008'.

    The programme does that for about 95% of the records, but completely ignores the other 5%(= 197 rows). I'm mystified.

  6. #6
    Join Date
    Jul 2003
    Location
    Amsterdam, Nederland
    Posts
    450
    [QUOTE=OfficeDummy]The whole column "Von" is filled with records ........ '01.11.2007' and so on, data type is Date/Time. I want QUOTE]

    Your only deleting records with '01.10.****' (where **** can be 2009 or 1999 or whatever)

    In anny case , if they are different it is correct.

    if not

    do a SQL where you ask the von field and as
    dd mmm yyyy and see what you get (i.e.: 1 apr 2006 )
    if a field is marked as 01.10.2007 and the field next to it shows 10 Jan 2007
    you can see your prob.
    Greetz Marvels -^.^-
    Developments : VB4 Through .Net; Basic; DOS ; CNC ; Sinclair
    Databases : SQL Server Through 2005; Access 3 Through 2003 ; Oracle 8 & 9.i ;
    OS : Win 3.11 Through XP ; NortonComander ; DOS

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    If you are replacing them... why not UPDATE the records?

    By the way, you should NOT use like comparisons on dates!
    George
    Home | Blog

  8. #8
    Join Date
    Jan 2008
    Posts
    74
    Why is 'like' wrong with dates? I just don't know how else I can point out what record to delete.

    Okay, some background before I confuse everyone here: I'm importing an Excel sheet on a monthly basis, which is called 'Einzelposten_Kst_VWL_yyyy.mm.xls'. Since I need a date field in Access, I create a new column and fill it with the correspondent date - date is being extracted from the Excel file name. One year later, the programme deletes the records from the previous year and writes the new ones into the Access table.

    Or is there a simpler way to do this other than the code I posted in the first post?

  9. #9
    Join Date
    Jul 2003
    Location
    Amsterdam, Nederland
    Posts
    450

    so

    your sheet is called say : *_10_2008
    and you want to delete all records of 01-10-2007

    why not do that, don't do 01-10.*
    just do a delete form table where date = #01-10.2007#
    Greetz Marvels -^.^-
    Developments : VB4 Through .Net; Basic; DOS ; CNC ; Sinclair
    Databases : SQL Server Through 2005; Access 3 Through 2003 ; Oracle 8 & 9.i ;
    OS : Win 3.11 Through XP ; NortonComander ; DOS

  10. #10
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    ^ I think he's using a text field, which is why Like is fine.

    You shouldn't need to close and re-open the table either, just requery the form instead.

    As for why it's missing 197 records, beats me. Without looking at your data and which records are deleted and which records are not, I have no idea.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  11. #11
    Join Date
    Feb 2008
    Posts
    7
    without seeing the data it is hard to say..... but my guess would probably be date formats (e.g. 1.10.2007 is either 1 Oct or 10 Jan depending on date settings) Access always assumes US date format, so you would need to ensure that the dates you are matching on are formatted similarily

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by OfficeDummy
    Why is 'like' wrong with dates?
    I'm glad you asked.
    Like is used in comparison for strings. When you are atempting to do it with dates (I'm suprised it's even lting you!) the engine is converting your dates into text before comparing them. Often this gives you unexpected results because you don't know what dformat it's converting the dates to!
    Use functions such as Year() (lok up smilar ones in the helpfile) that extract specific dateparts for comparison.

    I.e Year(2007-01-01) = 2007
    George
    Home | Blog

  13. #13
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Meh.

    I use Like for dates quite a lot and don't experience any problems, but you do have to account for the date format issue.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  14. #14
    Join Date
    Jan 2008
    Posts
    74
    So far the code works with 'like'... but do you reckon it might just "break" suddenly?

    By the way, I solved the problem - I just created a new form, attached the new code to the buttom, and now everything works perfectly! I guess VBA will always stay a cause of confusion for me...

    Thanks for everything, people!

  15. #15
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    No. If it works then it works. It won't break suddenly.

    Glad you sorted the problem
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •