Delete a table using VBA

I have some code that opens a couple of make-table querys, and then executes 
some more code.  I would like to insert the line 'DoCmd.DeleteObject acTable, 
"Table to Delete"' to delete those tables that are created, but it doesn't 
work.  The tables don't delete.  I'm assuming it has something to do with the 
loop, but I can't figure out where to put the deleteobject code to make it 
work.  Here's the code that I have now:

Private Sub btnEmailVolunteers_Click()
DoCmd.OpenQuery "Community Project1"
    DoCmd.Close acQuery, "Community Project1"
    
    Dim strDocName As String
    strDocName = "queemailtable"
    DoCmd.OpenQuery strDocName
    

On Error GoTo ErrHandle

    Dim dbs         As DAO.Database
    Dim rst         As DAO.Recordset
    Dim strAddress  As String 'this creates the Email addresses
    Dim strTo       As String 'this is needed to populate the TO block on 
the EMail form
    Dim strCC       As String 'this is needed to populate the CC block on 
the EMail form
    Dim strBCC      As String 'this is needed to populate the BCC block on 
the EMail form
    Dim strSubj     As String 'this is needed to populate the SUBJ block on 
the EMail form
    Dim strText     As String 
    
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("tblemailtable", dbOpenSnapshot)
            
    If rst.BOF = True And rst.EOF = True Then
        MsgBox "There must be an error in the query as there are no EMail 
Addresses listed!"
        GoTo ErrExit
    End If

    With rst
        
        .MoveFirst      'go to the first record
        
        strAddress = .Fields("EmailName").Value
               
        strBCC = strAddress
        
        .MoveNext           
        Do While .EOF = False
            strAddress = .Fields("EmailName").Value
            strBCC = strBCC & "; " & strAddress
            .MoveNext
                    Loop
        End With
        
    strTo = Nz(DLookup("[EmailAddr]", "[tblEmailSetup]"), "")
       
    strCC = Nz(DLookup("[AltEmailAddr]", "[tblEmailSetup]"), "")
 
    strBCC = strBCC
 
    strSubj = "Volunteer Opportunities"
  
    strText = Chr$(13) & Chr$(13) & Nz(DLookup("[EmailSig]", 
"[tblEmailSetup]"), "")
      
    DoCmd.SendObject acSendNoObject, , , strTo, strCC, strBCC, strSubj, 
strText, True
    DoCmd.DeleteObject acTable, "Volunteers"
    DoCmd.DeleteObject acTable, "tblemailtable"
 
ErrExit:
    Exit Sub
    
ErrHandle:
    MsgBox Err.Description
    Resume ErrExit
    Resume

End Sub
0
Utf
6/28/2007 5:34:01 PM
access 16762 articles. 3 followers. Follow

1 Replies
805 Views

Similar Articles

[PageSpeed] 26

On Jun 28, 1:34 pm, Gntlhnds <Gntlh...@discussions.microsoft.com>
wrote:
> I have some code that opens a couple of make-table querys, and then executes
> some more code.  I would like to insert the line 'DoCmd.DeleteObject acTable,
> "Table to Delete"' to delete those tables that are created, but it doesn't
> work.  The tables don't delete.  I'm assuming it has something to do with the
> loop, but I can't figure out where to put the deleteobject code to make it
> work.  Here's the code that I have now:
>
> Private Sub btnEmailVolunteers_Click()
> DoCmd.OpenQuery "Community Project1"
>     DoCmd.Close acQuery, "Community Project1"
>
>     Dim strDocName As String
>     strDocName = "queemailtable"
>     DoCmd.OpenQuery strDocName
>
> On Error GoTo ErrHandle
>
>     Dim dbs         As DAO.Database
>     Dim rst         As DAO.Recordset
>     Dim strAddress  As String 'this creates the Email addresses
>     Dim strTo       As String 'this is needed to populate the TO block on
> the EMail form
>     Dim strCC       As String 'this is needed to populate the CC block on
> the EMail form
>     Dim strBCC      As String 'this is needed to populate the BCC block on
> the EMail form
>     Dim strSubj     As String 'this is needed to populate the SUBJ block on
> the EMail form
>     Dim strText     As String
>
>     Set dbs = CurrentDb
>     Set rst = dbs.OpenRecordset("tblemailtable", dbOpenSnapshot)
>
>     If rst.BOF = True And rst.EOF = True Then
>         MsgBox "There must be an error in the query as there are no EMail
> Addresses listed!"
>         GoTo ErrExit
>     End If
>
>     With rst
>
>         .MoveFirst      'go to the first record
>
>         strAddress = .Fields("EmailName").Value
>
>         strBCC = strAddress
>
>         .MoveNext          
>         Do While .EOF = False
>             strAddress = .Fields("EmailName").Value
>             strBCC = strBCC & "; " & strAddress
>             .MoveNext
>                     Loop
>         End With
>
>     strTo = Nz(DLookup("[EmailAddr]", "[tblEmailSetup]"), "")
>
>     strCC = Nz(DLookup("[AltEmailAddr]", "[tblEmailSetup]"), "")
>
>     strBCC = strBCC
>
>     strSubj = "Volunteer Opportunities"
>
>     strText = Chr$(13) & Chr$(13) & Nz(DLookup("[EmailSig]",
> "[tblEmailSetup]"), "")
>
>     DoCmd.SendObject acSendNoObject, , , strTo, strCC, strBCC, strSubj,
> strText, True
>     DoCmd.DeleteObject acTable, "Volunteers"
>     DoCmd.DeleteObject acTable, "tblemailtable"
>
> ErrExit:
>     Exit Sub
>
> ErrHandle:
>     MsgBox Err.Description
>     Resume ErrExit
>     Resume
>
> End Sub

drop table

0
zionsaal
6/28/2007 6:19:00 PM
Reply:

Similar Artilces:

deleting a conditional format
I have the followint formula in a conditional format. =ROW()=ROW(INDIRECT(CELL("address"))) I remember getting it from this group, it highlights the active cell in yellow. I cannot delete it and I believe it will not allow me to copy and paste data about the spreadsheet. I need to delete as I need to copy and paste more than I need the active cell highlighted. Hi see your other post -- Regards Frank Kabel Frankfurt, Germany <mark@southwestconst.com> schrieb im Newsbeitrag news:1102362753.480803.96910@c13g2000cwb.googlegroups.com... > I have the followint formula in a con...

IS IT POSSIBLE TO CANCEL/DELETE message from Newsgroup server??
Once email is sent, it cannot be deleted before reachign recipient's address (unless you own server at recipients' end which is extremely highly unlikely) So just as with spoken word, be careful and dont' allow emotional rushes to result in spontaneous email you may regret later. But my question is about Usenet/Newsgroups (NNTP protocol): Is there a way to cancel/delete you rown posted message, at leas tbefore any followups appear (or even after)? As you all know, most of us hate Web Forums and we remain loyal to Usenet. But I have to admit, there's ONE feature...

Use of CString key in CMap
Hi I have a question about the use of CString as a key in a CMap. Is that okay? I want to get an object using a string name. Is that okay or will I sometime get the wrong object because the haskey could be the same for two different strings? CMap<CString, CString &, csomecobject, csomecobject &> m_map csomecobject; This hash key I found in CodeGuru, but I am not able to see if it is a good has key. template<> inline UINT AFXAPI HashKey<CString&> (CString& strKey) { LPCSTR key = strKey; UINT nHash = 0; while (*key) { nHash = (nHash<<5) ...

Tables #3
I have created a publisher 2007 document with tables in it but I cannot see the actual table unless I go to print preview. What am I doing wrong? Read the third FAQ here Q: Why can I not see images/shapes/lines when editing my publication? http://ed.mvps.org/Static.aspx?=Publisher/FAQs -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Mommydi" <Mommydi@discussions.microsoft.com> wrote in message news:C3D3BC5E-9CBC-41F6-8420-EF0CC2CFCD4B@microsoft.com... >I have created a publisher 2007 document with tables in it ...

Error code when you use WU/MU to install updates: 0x737D
Error code when you use Windows Update or Microsoft Update to install updates: "0x737D" (30 Dec-09) <QP> SYMPTOMS When you install updates by using Windows Update or Microsoft Update, you receive the following error code: 0x737D Additionally, you may receive the following error message: SQL Server Setup cannot upgrade the specified instance because the previous upgrade did not complete. Note: Microsoft SQL Server 2005 may be installed on your computer when you install applications such as *Windows Live Photo Gallery* and *Windows Live Mail*. [Doh! S...

Inventory Transactions using Integration Manager
Hi I am trying to import inventory transactions (adjustmnet) using the IM, the key thing is I want all lines in a single transaction. I memic the sample in IM. but its not working imports all linesbut I am getting this error: Object 'Adjustment 1' already exists -- cannot Insert. I think the problem is related to te documnet number, I am using a cloumn that conatains the same number for all rows (in order to import all the lines in a single trx) Am I missing anything? any hint? Thanks Look at the "group by" function in the IM manual. An import will consist of hea...

Excel 2003
Whenever a colleague updates a spreadsheet I've created it comes back 10MBytes large whereas it was originally only 50KBytes. It looks to me as if the colleague accidentally adds tens or hundreds of thousands of empty rows. How can I delete all rows below a certain row. Delete does not work. Copying the real rows into a new worksheet loses the formatting. When I apply the formatting from the big spreadsheet (paste formatting) I end up with hundreds of thousands of rows again :-( Any suggestions? Tom Selecting all the rows below your actual data. Right-click, delete cells. (this is...

XP Style button using MFC 4.2
Hi, I am using Visual C++ 6, MFC 4.2. Windows XP SP2. My problem is, how could I get the XP Style buttons on the dialog. If i dynamically create the button using CreateWindow() the button still looks same. I've tried 'Button Controls' article on MSDN but no luck. Could anyone help me? Thanks, Fahad Themes are handled by the system, and not on a button-basis. The way to give your application running on XP the themed look is to include a manifest resource. The resource can either be a resource compiled into your EXE or it can be a separate file in the same directory as your ...

Oops! Deleted the wrong item
We were adding items on multiple stations and one got duplicated that was already on file. I deleted the duplicate but it turns out I deleted the wrong item. The one I removed was on four layaways. Now I can't recall the layaways (error 204) to fix them. I tried adding back the item with the original lookup code, but that didn't help. How can I fix those four layaways or remove them so they can be re-entered? Thanks. Mark S. when you deleted your item, a record was inserted into the RecordDeletedLog table in the database, if you look there it will tell you what the ID number w...

a sample question about excel VBA
How can I make msgbox like this? �� there are X sheets of this workbook the first page name is : Sheets(1).Name the secod page name is : Sheets(2).Name �� until to the last page the X page mane is : Sheets(X).Name How can I write this module? Sub WorksheetNames() Dim i As Long Dim strAnswer As String strAnswer = _ "There are " & Worksheets.Count & _ " sheets of this workbook" For i = 1 To Worksheets.Count strAnswer = strAnswer & vbCr & _ "Sheet " & i & " : " & _...

Access mouse wheel does not scroll in datasheet view of table, Win
I am aware of the issues with mouse wheel scrolling of forms. But those who have posted about those invariably say that mouse wheel scrolling works in datasheet view. For me it does not. On Win XP Access 2003, if I opened a table in datasheet view it initially would not scroll using the mouse wheel. But if one scrolled down a bit using the righthand scroll bar, thereafter the mouse wheel worked to scroll the records. I've just moved to Win 7 64-bit and have Access 2003 and 2007 installed. It won't scroll a table in datasheet view using the mouse wheel with either ve...

Recover deleted email
How do I do this as I delted all of my email in error. Also, I am using outlook 2002 not outlook express. Thanks Anthony ...

Using VBA to change Excel layouts
Our company has designed 4 different spreadsheet designs. The same data gets stored in any of the 4 spreadsheet layouts....the only difference is that some spreadsheets use different colors, have different fonts and cell sizes and etc... I thought it would be nice to create just ONE spreadsheet that contains a combo-box or list-box or drop-down box which allows the user to select 1 of the 4 layout choices, then I want VBA to programattically setup the spreadsheet layout according to the users choice. Does that make sense? Does anybody know any good ways to implement something lik...

Cannot delete mailbox/email account from Outlook 2003
Hello All - I'm a systems admin at a company that use exchange server. Someone added a mailbox to an e-mail account, and it appears as a second mailbox in the folder list. The only problem is, I can't delete it. If I right click the mail account, select "close mailbox...", I receive the error that the mailbox is associated with an account, and to remove it I need to go to Tools, E-mail accounts, view or change, blah blah blah. If I go to that route, I can delete the Microsoft Exchange Server account that exists. However, in the "Deliver new e-mail to the following loca...

How do I use a pivot table to get an average count? #2
I have a list of transactions with colum headers of Month, Date, Weekday, Time and the transaction data. I would like to see the average number of transaction per hour per day. Can this be done in a pivot table? ...

USING DATES IN FORMULAS #2
I foubd a problem with excel 2003 If you introduce a formula using dates (in spanish version) (dd/mm/yy), excel doesnt makes the calculation........... ???? Can somebody hep me In the USA version (English), I could use: =if(a1=date(2005,10,27),"It's Oct 27, 2005","it's not") to refer to a date. I don't know what the Spanish function is for =date(), though. Carlos Benavides wrote: > > I foubd a problem with excel 2003 > If you introduce a formula using dates (in spanish version) (dd/mm/yy), > excel doesnt makes the calculation........... ????...

Delegate constructor compiles in VB.NET, but not in Excel VBA
I have created a DLL callback through a delegate function. It works fine when the client is C#, or VB.NET. I can't get it to compile in Excel VBA. I have this problem both in Excel 2003 and Excel 2007. The DLL references setting is ok, because when I take the reference out the compiler errors out on RemoteProcess. What am I missing? The snippets below are the dll code, the working vb.net client code, and the failing vba code. =========================================== The remoteprocess class is compiled as dll: =========================================== Public Cl...

When collecting data by using e-mail messages
I'm getting the Warning message - these email messages might contain data that is of a confidential or sensitive nature - and it's not giving me access to the Create button in the wizard. The suggestion is to remove any fields in my message that might contain the confidential data, but there are none. How do I get around this? -- boniG When I said "there are none", I meant that none of the fields contain data of a sensitive or confidential nature. I even tried removing every field except the very first one which is simply a text field with the identificat...

continually getting send/receive errors using outlook 2002
Does anyone know why I am continually getting send/receive errors when trying to send an e-mail. I can receive e-mails from virtually anybody that is not blocked by Norton Anti-Spam(Norton Internet Security 2005). My account is a pop3 account with the 'Orange' mobile phone network, therefore it has an '@orange.net' address. I have tried 'repairing' outlook from add/remove programs - this did not fix the problem. Neither did uninstalling and re-installing (also from add/remove programs) this time it only allowed me to send a test e-mail to my '@aol.com' ad...

Printing Tables in Outlook
Can anyone help? When I create a table in an Outlook message and use shading within the table, when I print out the table the shading doesn't print. It does if I use Word and print out the same table, but not through Outlook. Any suggestions? Thanks In article <A4AF7275-4DC2-417A-9343-E0C32F86FECD@microsoft.com>, old_oak66 <oldoak66@discussions.microsoft.com> wrote: > Can anyone help? When I create a table in an Outlook message and use shading > within the table, when I print out the table the shading doesn't print. It > does if I use Word and print o...

How can I use wildcards in IF statements and Macros
I want to include in a Macro, something to take a particular action if the active cell contains text beginning with X. I thought something based on an IF statement, that included a wildcard, might work, e.g. IF(A1="X*,1,0). This does not work though. Sub JustDoIt() Dim s As String s = ActiveCell.Value If Left(x, 1) = "X" Then MsgBox "We should do something" End If End Sub -- Gary''s Student - gsnu200909 "AlanF" wrote: > I want to include in a Macro, something to take a particular action if the > active cell conta...

Sharepoint list using using Issue "Created at" in calculations
We are using SharePoint at work. I currently have a list that I use the following Calculation to indicate how long an event have been open. =DATEDIF(Date,"1/4/2010","d"), however I would like to key on the Item “Created at” that is stored for each item. Does anyone know how to pint to that within a calculation. The result would be it has been 15 days since a list item was opened. ...

Create Table in code
Hello all, I need to create a table in code with dynamics columns. Anyone do this before? can i see a sample many thanx DAO is best for creating tables. Here's an example showing the various field types and properties: http://allenbrowne.com/func-DAO.html#CreateTableDAO It is also possible to use ADOX: http://allenbrowne.com/func-ADOX.html#CreateTableAdox Or you could use DDL: http://allenbrowne.com/func-DDL.html#CreateTableDDL -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than ...

Deleting leads
Hey; We just had a bad import of several thousand records; anyone have any idea of a crm centric way of deleting them that do not damage the system? The user does not have leads so if it's a way to zap their lead data that would be fine as well. Oh, for the eager beavers I am not looking for third party tools; I'm hoping for an MS response. Please don't spam the thread with stuff. Thanks. One thing I've done in the past is to create a user or use someone who has no records in the system under their name and import with that user. The records are automatically applied ...

deleting items via supplier ID
what quary would I need to delete all items attached to a specific supplier. ie delete all items suppied by the xyz company Thanks, Ron Do you want to actually delete the items or do you want to just delete the association to that Supplier? To get X do this first: SELECT ID FROM Supplier WHERE SupplierName = 'XYZ Company' This will delete the Items from your database: DELETE FROM Item WHERE Supplier.ID = 'X' If you want to keep the items in the database but want to delete any associations between items and a specific supplier do this: DELETE FROM SupplierList WHERE Suppl...