Thread: Deleting from a table
06-24-09, 09:08 #1Registered User
- Join Date
- Mar 2009
Unanswered: Deleting from a table
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);"
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!!!!!
06-24-09, 22:33 #2L33t Helpa Munky
- Join Date
- Nov 2007
- Adelaide, South Australia
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
06-25-09, 01:15 #3Moderator
- Join Date
- Dec 2004
- Madison, WI
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)
06-25-09, 04:30 #4Jaded Developer
Provided Answers: 59
- Join Date
- Nov 2004
- out on a limb
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
[(SE)_tbl_Exchange_Rates]INNER JOIN [tbl_holding] ON
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