Two dbs, with a common be db...is this trouble?

[GBalcom]

I wasn't sure where to post this....moderators please move as necessary.

I've created a nice little app (dubbed app#1) that automatically runs via task scheduler to pull some information into a report and email it (Via VBA Code). This is scheduled to run once per week, and sends information to our customers. To date, this has shared a common back end (.accdb) with another app (I'll dub it app#2). App #2 updates our google calendar nightly with events that were entered into our SQL server based ERP system that day. It also sends 1 email if certain conditions are met.

Both Apps are set to open a form, then the forms on Load events start the code rolling. (tried an AutoExec Macro, but didn't like it)

This morning, I noticed some unintended behavior in App#2. Opened it up using shift click to bypass the opening code. checked it out and needed to run it again manually to confirm some things....When I ran it manually, App#1 fired and sent an email out to customers! :banghead:

How/Why did this occur?!? I've thought long and hard about this. I'm 99% sure the events happened as portrayed above. I also opened the backend directly however. Either way, I'm stumped.

Should I make two backends and keep them separate?

The really crazy thing is this: App#1 has code in it to prevent it from running on any day other than Wednesday. It is Monday. I even put in a breakpoint and stepped through the code to ensure that Function worked, and it did.

I'm happy to post any of this code if it will help, but it's quite long. If anyone can let me know what they'd like to see, I'll happily oblige.

 

[The_Doc_Man]

(tried an AutoExec Macro, but didn't like it)

Just as an "aside" - you DO know you can use an AutoExec macro to open a form as well as one that could do a RunCode... right?

The BEST way to automate things like this is to have your Task Scheduler run a command-line with the /X option to execute a macro that - eventually - contains the Application.Quit reference. Of course, your triggered code could also contain that. There is no reason why one couldn't start the ball rolling with a Macro but then let the code do the rest.

If your forms in App#1 and App#2 leave behind "tracks" to show that they have done something, and if either App can be triggered by the other App because of side-effects on variables they both would use, that is your problem.

To answer the overarching question, there is no requirement that the two Apps have ANYTHING in common except a pointer to that shared BE file. If they both would touch the same tables, that is still OK as long as they don't step on each other through side-effects.

But here is my counter-question. Are the two apps embodied as two separate .FE files, OR as two different forms in the same .FE file; OR as the same form in the same .FE file with code that tries to sense your run conditions and do different things at different times?

A second question... OK, so you used the shift-click method to bypass the opening form. Does that form's OnOpen, OnLoad, or OnCurrent event do something to establish your context variables that TELL you when it is Monday? Opening the app in a mode that obscures or never initializes your starting conditions might account for your oddball mail behavior.

 

[GBalcom]

Doc_Man,
Thank you for responding...To answer your counter questions:

#1:
I have the start forms being automatically opened through File => Options => Current Database => Display Form.

These forms start my code through the onLoad Event. Is that acceptable practice?


#2:
These are two separate accdb files that have nothing in common other than some linked tables. They are not intended to call upon one another in any way.

#3:

"A second question... OK, so you used the shift-click method to bypass the opening form. Does that form's OnOpen, OnLoad, or OnCurrent event do something to establish your context variables that TELL you when it is Monday? Opening the app in a mode that obscures or never initializes your starting conditions might account for your oddball mail behavior.

Perhaps your right...as I described above, I'm counting on the on load event of that form to kick things off. I haven't noticed any odd behavior before though.

What would be the acceptable way of handling something that we need automated 99% of the time, but 1% you want to walk through as a developer?


Disclaimer:
I'm not a pro at this, I'm here to learn and become better

 

[GBalcom]

For the record, I'm still having issues with this.

For weeks, both programs have been happily running as they should.

Yesterday, I went in and made a quick change to app#2 (changed an email address in the code). app#2 has been saved as just a .accdb file, and I continued with that. Just changed the email address in the code, saved, and closed the file.

This morning I find that App#1 fired (sent the email to our customers unintentionally) TWICE at 3:15am, and once at 5:00am (when it's supposed to).

App#2 is set in task scheduler to run at 3:15am, App#1 is set to run at 5:00am on Wednesdays only.

My question is this---Last time this occured, I deleted all my tasks from task manager, and recreated them....Then it appeared to be fine until yesterday when I made the change above. After said change, I did not delete/recreate the tasks in task manager.....Should I have to? Any other clues as to what is happening?

 

[gemma-the-husky]

Can you amend the bit of code that sends the email?

perhaps you could also send yourself a different email, with more information about the run time environment that caused the email to be sent.

 

[GBalcom]

Gemma,
Thanks for your suggestion...To be honest, I still have no idea what caused the issue. I can say this; I changed my "SendEmail" Function in both programs from an outlook automation to using the CDO method, and they are running fine...I'm pretty sure it had nothing to do with Task Scheduler now, and more to do with Outlook. But either way, the problem seems to have evaporated. If I'm feeling frisky later, I may even change the db's back to the .accde file format, and hope they still work fine. But, right now they are humming along at .accdb's. If it helps anyone, I'm using Gmail for this.

Thanks to all for your help on this!!

 

[jdraw]

I don't have any insight as to why this is happening. However, I would suggest a modification to your code for development and testing.
I think a routine that does NOT do the send email to Customers, but sends one to yourself with a "special message". That will stop the unintended emails to customers which must be an issue.
Get the logic under control before sending unintended emails to clients/customers.

When I had Outlook installed, I would send emails via Outlook from Access. Being retired I don't do any emailing from vba/Access, but wouldn't mind seeing how you have set up to use Gmail and CDO.

Good luck.

 

[GBalcom]

Jdraw,
I'm sure you don't remember, but you were one of the first people to help me hone my VBA skills on here. It was much appreciated! Anyways, here's a thread following my challenges and finally resolution on the CDO method(?).

http://www.access-programmers.co.uk/forums/showthread.php?t=284646

 

[jdraw]

Yes, I do remember the name --not the subject at the moment. I have seen some of your posts re web and sql server as I recall. Haven't done those areas with M$oft products since retiring a few years back.

Thanks for the link.

Good luck with your project(s).


Update:
Just reviewed the link and adjusted slightly. Got runtime error as per the attached jpg???

I just adjusted the code, used your configuration statements, but have not done any research. Just wondering if there is more that the code required.