Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2009
    Posts
    37

    Unanswered: Deleting from a table

    Hi

    I am trying to delete some records from a table which I have previously created using sql and then populated by content of a spreadsheet using vb. I am now trying to delete all records which are duplicates comparing it to another table in my db.

    here is the code:

    'dim strDeleteDuplicates As String
    'strDeleteDuplicates = "DELETE [(SE)_tbl_Exchange_Rates]*.* FROM [(SE)_tbl_Exchange_Rates]INNER JOIN [tbl_holding] ON ([(SE)_tbl_Exchange_Rates].CODE = [tbl_holding].f1) AND ([(SE)_tbl_Exchange_Rates].Month = [tbl_holding].date_updated);"

    'DoCmd****nSQL strDeleteDuplicates

    whenever I run this code I get this message: cannot delete from table

    I have also tried to delete from table using a query in access but I get the same message. I can however delete blank rows. So I only seem to have a problem when I am trying to delete where linked fields are identical. HELP!!!!!

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    DELETE [(SE)_tbl_Exchange_Rates]*.*...

    DOS days were big for you weren't they?!

    Look out for missing spaces too... I see one before the word INNER.

    It might also be true that you just cannot delete with that SQL for some other reason... hard for me to tell because my mind turns to mush when I look at SQL and there are table/field names such as (SE)_tbl_Exchange_Rates.
    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

  3. #3
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Yeah. Those (SE)_tbl... naming type conventions you're using will be the cause of many, many headaches for you in the long run (or when you need to come back and edit the mdb a year or so later.)

    But at least you used _ instead of just a space.

    Easiest way - use the query designer and set everything up and test. If it all works good and is editable, view the SQL for the query, copy and paste the SQL statement into your code (you'll have to tweak a few of the punctuation) or just compare it with what you have.

    Query designer is great for visually creating SQL statements!! (especially complex ones with multiple joined tables and criteria.)

    Note: You switch from designer view to SQL view using the dropdown in the upper left corner in query designer (shows a right (or left - I forget) triangle type icon for design mode and SQL for Sql mode.) Design your query and then select SQL to see the SQL statement.
    Last edited by pkstormy; 06-25-09 at 01:27.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    persoanlly I don't see a problem with prefixing your tables if it floats your boat. one reason is to make certain you know what type of object you are using in Access.. mind you I wouldn't be using anything like (SE)_tbl. but thats just my ratbag of prejudices.

    poersoanlly if I were you, I'd want to check the spelling of your SQL/code to make certain there are no typo's
    fer instance
    Code:
    [(SE)_tbl_Exchange_Rates]INNER JOIN [tbl_holding] ON
    there should be a space before the INNER JOIN
    persoanlly I don't like using macros as they are nto that helpfull when they don't work.

    If you get this sort of message again a good approach is to copy the text of the query into the SQL pane on the query browser and run it there.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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