Drop table

Posted on 2003-03-11
Medium Priority
Last Modified: 2010-05-01

How to drop a table from Access mdb?  My program work in this way;
I delete records from a table, then drop the whole table from mdb.
Where the table name is key-in by users. My coding as below, but
is working!  pls assist.

sqlDelBData = "delete * from " & Trim(txtYear.Text)
rs2.Open sqlDelBData, cn1, adOpenForwardOnly, adLockOptimistic, adCmdText

'Drop a table
sql = "Drop table " & Trim(txtYear.Text)
cn1.Execute sql
sql = "Drop unique index indLotNo"
sql = sql & "  on " & Trim(txtYear.Text) & "(LotNo)"
sql = sql & " with primary"
cn1.Execute sql
Question by:laysee
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 53

Expert Comment

by:Ryan Chong
ID: 8116805
>>My coding as below, but is working!  pls assist.
Your code is working or not?? What do you try to do?

Isn't it the code below working?
sql = "Drop table " & Trim(txtYear.Text)
cn1.Execute sql

Author Comment

ID: 8117009
Oops,  is NOT working.

Expert Comment

ID: 8117291
Can u Exaplian is there any Error Message? Or Table is Not Drppped Simply?
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.


Author Comment

ID: 8117573
no error message, it just cannot drop the table.
It be able to copy records to another table(2),
and delete the records from the table(1). Just
the last part cannot drop the table.

Accepted Solution

happel earned 400 total points
ID: 8117682

You could check if you have the rights (when connected other then Admin) to perform a drop table.

If user=Admin: Could you post the exact SQL statements for wich DROP table fails, thus including your DROP Index SQL (from immediate pane)
LVL 49

Expert Comment

ID: 9664432
Hi laysee,
It appears that you have forgotten to close this question. I will ask Community Support to close it unless you finalize it within 7 days. I will ask a Community Support Moderator to:

    Accept happel's comment(s) as an answer.

laysee, if you think your question was not answered at all or if you need help, just post a new comment here; Community Support will help you.  DO NOT accept THIS comment as an answer.

EXPERTS: If you disagree with that recommendation, please post an explanatory comment.
DanRollins -- EE database cleanup volunteer

Featured Post


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question