E-mail query to recipients in table - performance and security

Hello,


I have created a database in Access 2003 with a table of suppliers and a 
table containing my orders with those suppliers. Now suppose I want to send 
out lists with outstanding orders to those individual suppliers as a sort of 
reminder, only containing their "own" orders. I have written following code:

---

Dim rs As Recordset

Set rs = DBEngine(0)(0).OpenRecordset("tblSuppliers")

Do While Not rs.EOF

If DCount("[SupplierID]", "qryOutstanding", "[SupplierID] = '" & 
rs.Fields(0) & "'") <> 0 Then
    
CurrentDb.QueryDefs("qryOutstandingList").SQL = "SELECT 
qryOutstanding.[Purchase order] FROM qryOutstanding WHERE 
qryOutstanding.SupplierID = '" & rs.Fields(0) & "';"
     
DoCmd.SendObject acSendQuery, "qryOutstandingList", acFormatXLS, 
rs.Fields(1), , , "Outstanding purchase orders", "See attached", False
    
End If
rs.MoveNext
Loop

rs.Close

---

This works.

However I still have two questions:

1. Performance is rather poor. Is there a more efficient way to accomplish 
the same?
2. For every e-mail created by this code, Outlook asks me to confirm sending 
after a delay of five seconds as a security measure. For 500 e-mails that's 
rather awkward. Is there a way to bypass this?


Thanks in advance,

Gerwin
0
Utf
5/11/2010 7:34:01 PM
access.modulesdaovba 1670 articles. 0 followers. Follow

1 Replies
1073 Views

Similar Articles

[PageSpeed] 23

Gerwin,
Thanks for this.  It's working great for me.  I only need to tweak the sql 
to send only certain rows and not everything in the table.

"Gerwin" wrote:

> Hello,
> 
> 
> I have created a database in Access 2003 with a table of suppliers and a 
> table containing my orders with those suppliers. Now suppose I want to send 
> out lists with outstanding orders to those individual suppliers as a sort of 
> reminder, only containing their "own" orders. I have written following code:
> 
> ---
> 
> Dim rs As Recordset
> 
> Set rs = DBEngine(0)(0).OpenRecordset("tblSuppliers")
> 
> Do While Not rs.EOF
> 
> If DCount("[SupplierID]", "qryOutstanding", "[SupplierID] = '" & 
> rs.Fields(0) & "'") <> 0 Then
>     
> CurrentDb.QueryDefs("qryOutstandingList").SQL = "SELECT 
> qryOutstanding.[Purchase order] FROM qryOutstanding WHERE 
> qryOutstanding.SupplierID = '" & rs.Fields(0) & "';"
>      
> DoCmd.SendObject acSendQuery, "qryOutstandingList", acFormatXLS, 
> rs.Fields(1), , , "Outstanding purchase orders", "See attached", False
>     
> End If
> rs.MoveNext
> Loop
> 
> rs.Close
> 
> ---
> 
> This works.
> 
> However I still have two questions:
> 
> 1. Performance is rather poor. Is there a more efficient way to accomplish 
> the same?
> 2. For every e-mail created by this code, Outlook asks me to confirm sending 
> after a delay of five seconds as a security measure. For 500 e-mails that's 
> rather awkward. Is there a way to bypass this?
> 
> 
> Thanks in advance,
> 
> Gerwin
0
Utf
5/14/2010 4:16:01 PM
Reply:

Similar Artilces:

Can I use a Report expression for queries?
Is it possible to use the following expression that is used in reports on queries? =[Sales]/Sum([Sales])*100 Or some expression to get the result. Thank you. No. In your query, you can refer to text boxes on a form, but not on a report. That's because of the way reports are formatted (sequentially.) Forms have a current record, which identifies which value you want. Reports don't. In the context of a query, there are other ways to get totals, such as DLookup(): http://allenbrowne.com/casu-07.html or a subquery: http://allenbrowne.com/subquery-01.html -- Allen Browne ...

Table format
Hi When I run the code below I get "External table is not in the expected format" error at line rsData.Open sSQL, sConnect, adOpenForwardOnly, adLockReadOnly, adCmdText Option Explicit Public Sub SelectFromAccess() Dim rsData As ADODB.Recordset Dim sPath As String Dim sConnect As String Dim sSQL As String 'Clear the destination worksheet Sheets(1).UsedRange.Clear 'Get the database path (same as this workbook) sPath = ThisWorkbook.Path If Right$(sPath, 1) <> "\" Then sPath = sPath & "\" 'Create the connection s...

moving mail/contacts from outlook express_ to_ outlook
I can not find any of my Outlook Express saved items: Thought they were at a location similar to below: C:\Windows\Application Data\Microsoft\Outlook Express\Mail Searched by *.dbx / *.ibx / *.pst and opening every subfolder. Got a little ahead of ourselves and "switched" from OE to Outlook - Comcast claimed OE was the same or better, but we missed some of the Outlook features. Now we have lost our "address books" and some important attachments that were still within emails...the emails are downloaded somewhere on this hardrive as we have not deleted a thing...right ? ...

Hiding KB976002 results in security warning
Windows XP SP3 Windows update allows you to hide an update you don't want to install. Of course I have hidden KB976002 which is presented as a critical update but is not a security update and is only needed when the user wants to have it. However, now Windows Update complains that I have hidden a critical security update and that my computer will be in great danger until I restore this update. This is of course incorrect. KB976002 is not a security update. Please update the Windows Update site so that KB976002 can be hidden without further messages and incorrect warnings...

Tables/ queries question
I have a table for recording college attendances, some of the courses are at Campus 1 and some are held at Campus 2. I've got a field for course code, course anme and a check box to show if the student is at campus 1. When I enter the student ID number into the form, at the moment the name of the course comes up automatically in the text book, as I think it's reading the information from another database on the system. At the moment I've got a check box on the Form to enter manually into the table whether the student is at Campus 1 or Campus 2. What I wondered is if...

Cannot insert explicit value for identity column in table x
Sql Server 2005 9.00.4035. We have a hub and spoke topology which includes 6 tables with identity columns. I need to add a new subscription to a new server (same version/sp). I took backup from another subscriber and restored to new server, ran snapshot, push subscription. To make sure a new record would replicate, I added a record at the publisher with the followig text: Insert into tblicimagefolder (imagefolder) values ('dummy') A conflict occurs with the following: This failure can be caused by a constraint violation. Cannot insert explicit value for identity c...

3.0 Mail Merge
Is anyone experiencing this issue post upgrade to 3.0.....? When attempting to Print Quote for Customer in Outlook, we select the quote template, then Microsoft Word opens. From here the mail merge functions would begin. However, the mail merge toolbar is grayed out and we cannot merge to new document. We can't do anything. Is the process for Print Quote for Customer different in CRM 3.0? Or is this an issue? If so, can anyone provide assistance on this. Thank you! Cayla Will the templates we had set up to use in CRM 1.2 (for mail merging) still function in 3.0? Or are there ...

How to account for mail back rebate in money
I have bought bunch of stuff where i pay up front money but on a later date i receive mail back rebate. How do i account for this refund in the Microsoft money? I did not see any category or feature which supports this very common practice of mail back rebate. Is there any add on feature which support tracking of mail back rebate (whether i received or not and flags when its past due after few weeks) Thanks Parikh Enter a similar cost entry [spend, increase, etc] but with a minus value. This will give the net cost of the item you purchased. "Parikh" <anonymous@discussio...

V4 Mail Merge on Custom entities
Hello We have noticed that new entities added at v4 appear in the mail merge list but that custom entities carried over when upgrading a v3 system do not. Does anyone have any information on how this is controlled and whether there is an option to set an entity as included once it has been created ? All info gratefully received Thanks...Phil That should help: http://blog.sonomapartners.com/2008/01/enabling-mail-m.html Cheers -- George Doubinski CRM Certified Professional - Developer Blog: http://crm.georged.id.au/ "Phil Kedward" <Phil Kedward@discussions.microsoft.com...

Creating an Excel table from Access
Hi, I have an Access database which I use to log downtime for systems. I have a requirement to produce a monthly report based on this data, however, this needs to be exported to excel in a specific format. Down the left side of the report need to go the names of the services, with the days (numerically like: 1, 2, 3 .. 29, 30 etc) across the top. Then I need to count the number of times each service was down on a give day, and insert that information into the necessary cell - so if intranet services had been down twice on 16th March, for example, there would be a 2 in column 16, whi...

problem with pivot table
Hello all: I am trying to make a pivot table from my date. My data includes Names, Dates (which I formatted to month, "m" in the format cells option)and Amount due. In designing the pt, I have "Name" in the row field, "Date" in the columnn field and "Amount due" in the Data field. I want my data to be grouped as follows: Month 1 2 3 4 Name $100 $20 $30 $20 Instead I am getting more than 1 month column i.e. Month 1 1 2 2 2 3 Name $80 $20 $30 $20 Do you know why...

Not possible to remove mail reminders in outlook 2002
Hiya, I have a big problem with old mail that keeps popping up as reminders. I've tried to start outlook with /cleanreminders and another parameter without luck. Is there anyway that I can delete these reminders for sure? My mail originally comes from outlook in office 97, I know use outlook 2002. The mail that I get reminders on are mails from that period. Very grateful for any help that you can give me. - Kjell Arne ...

Strange problem in BP Portal for GP10 with Queries
I just had a user come to me and tell me he is getting an error on the sales page saying the query does not exist. This is the out of the box query that has not been modified. When I go into query organizer I find the Purchase Order Line Item folder but it now has a (1) beside it with the query inside it. What causes the folders to be renamed. While looking at the queries I multiple folders with the (1) out beside them. Thanks for any information you can shed on this. Robert Fann rfann@nospam.omnipackaging.com ------=_NextPart_0001_BD46EE76 Content-Type: text/plain Content-Transfer-...

Querying Excel data without another program
Hello, I'm an intermediate user of Excel, but I have lots of experience using Access. If you can give me clues on how to handle the information below using only Excel, I'd be grateful! I have a spreadsheet in Excel 97 in which there's a Worksheet named, "Datasource" with a column "B" containing repetitive data. I'd like to create a new worksheet in the same Excel file which shows a single instance of each item used in Column "B". If I were writing the query in SQL, I'd say "SELECT DISTINCT [Column B] FROM [MySpreadsheet]![Datasource...

Payroll w/h tables
When can we expect new payroll withholding tables for Ohio? In December our gov announced a late year change repealing a prior payroll credit previously announced. We have been told that the withholding tables are not correct yet GP/Microsoft has not released new tables yet. When can these be expected since we are practically done with January now??? Thanks. According the information I have, there are no pending changes for OH. You may need to contact Support about this issue. -- Charles Allen, MVP "INC" wrote: > When can we expect new payroll withh...

Dynamics 7.0 tables in SQL server 2000
I need to know what should be the content of each of the tables with names starting with "GL" in SQL Server 2000 when using 7.0. Is there a reference that I can go to in Customersource that tells me this information? Thanks. -- Dave Christman System Developer There is an online reference: Tools >> Resource Descriptions >> Tables. Also there is a SDK avaailable on the Great Plains CD's. "Dave Christman" wrote: > I need to know what should be the content of each of the tables with names > starting with "GL" in SQL Server 2000 when...

modify the Type of Field in a Table From another MDB through a Command button
Hello to all! I hope can help me. By technical questions that would be to me very long to explain, the following necessity arises to me: I need To modify the Type of Field in a Table From another MDB through a Command button. The field at issue would happen to be "Number" to "TEXT". Is this possible one? I wait for alternatives. Thanks in advance, and sorry for my poor english. Dreamer. - Hi Is it not possible to open the other database and simply open the table in design view and then change the field. It seems very complex to do the codeing what will most pro...

Unmatch query
-- Thanks ...

Security and encryption issues when trying to convert to 2007
I currently have a 2003 database set up with a workgroup and user end securities. I know these won't work in 2007, so I've already followed the suggestions through a few other posts and essentially gotten rid of my securities by changing all the profiles to admin with full access and rights. But my database is still encrypted and I receive an error message when I try to convert it. I could just convert the version of the database I have before the securities were added, but I've made a lot of changes since then and don't want to have to redo all those hours of wo...

Scalability and Performance Baseline
Hi Folks, I just wonder if anyone of you had issues on Performance if you have the following databases sizes: Product Catalog - 15,000 records or more Accounts and Contacts - 1,500 records We all know that all these records will be replicated offline but my concern is that how long will it take for these records to be replicated to the local client CRM database? My users are concern about the time they have to wait. Another thing is the timeout if these replication will take longer that expected, is there any default time out settings for the replication? Your feedback is very much apprec...

Query based on multiple parameters
I would like to create a query where the user can input 2 or more parameters (part numbers), separated by comma and/or space and return information about that part. I already have a query that accepts one parameter. Is there a way to do this with more than one? I have read some posts that say to use a table to serve up the paraments and the user chooses one or more. However, a particular part or parts may not yet be in the table as they are new or unknown. Is there any way to do this without using an existing table but just accept input from the user on the fly? T...

Not Receiving Mail 03-28-10
I'm not receiving any messages, except TEST messages I send to my self. Just loaded Windows 7 (clean) a couple of weeks ago. Migrated all of my old stuff from Windows Mail (Vista). I've tried everything, including turning off email filtering. Only one email account and it is POP3. Can something be corrupted? If you receive test messages you send to yourself, then your Windows = Live=20 Mail is working normally. Who is your provider? If you go to the = provider's webmail site, do you see messages there that failed to download? --=20 Gary VanderMolen, Microsoft...

I E 7 Browser 06-21-10
I am experiencing a problem with IE 7 when I open the browser. The browser closes 3 times and then on the 4th try it stays open. I would truly appreciate any help with this problem. On 06/21/2010 04:57 PM, worf49 wrote: > I am experiencing a problem with IE 7 when I open the browser. > > The browser closes 3 times and then on the 4th try it stays open. > > I would truly appreciate any help with this problem. try disabling add ons http://pcsupport.about.com/od/fixtheproblem/ht/disableie7addon.htm ...

Help using Tables
Hi guys! I need some help. I have a table that has a percentage range. Like this. Less than 90% 0 90.0% 90.9% $ 2,625 91.0% 91.9% $ 3,413 92.0% 92.9% $ 4,200 93.0% 93.9% $ 4,988 94.0% 94.9% $ 5,775 95.0% 95.9% $ 6,563 96.0% 96.9% $ 7,350 97.0% 97.9% $ 8,138 98.0% 98.9% $ 8,925 99.0% 99.9% $ 9,713 100.0% 100.9% $ 10,500 101...

How to keep mail on the pop server when checking with Outlook
Hi Everyone, I have two machines. 1 Macintosh that I can set to leave the message on the server. 2 A PC that whenever I check mail it deletes all messages on the server. How do I set the PC to leave the messages on the server?? Jeff In your POP account properties in Outlook. Note - you're gonna have to delete the messages from the server somewhere/sometime, or your mailbox will get full & start bouncing mail. J. Moss wrote: > Hi Everyone, > > I have two machines. > > 1 Macintosh that I can set to leave the message on the > server. > > 2 A PC that wh...