November 22, 2011 at 3:25 pm
I have an Access 2007 database that kicks off a sql stored procedure. The SP kicks off an email program. Runs fine most of the time. Once in a while the email program locks up.
We think it because the Access db is still connected. How do I kill the connection in the code? Here is my code.
Private Sub Command4_Exit(Cancel As Integer)
Set con = New ADODB.Connection
con.ConnectionString = "Provider=SQLOLEDB.1;Password=xx;User ID =XXXXXXX;Initial Catalog=XXXframework20;Data Source=XXX1SQl08"
con.Open
Set cmd = New ADODB.Command
cmd.ActiveConnection = con
cmd.CommandText = "INSERT INTO sv_process_event_tbl (process_guid,process_event_data_path, create_user) VALUES ('XXX52156X-X226-52X7-X781-X98X
6D85F569 ','\\XXFILESRV1\DATA\EmailXML.XML','CC2007_caccdb')"
cmd.Execute
End Sub
Thanks in advance for any help.
Tom
November 22, 2011 at 3:28 pm
tuhlig (11/22/2011)
I have an Access 2007 database that kicks off a sql stored procedure. The SP kicks off an email program. Runs fine most of the time. Once in a while the email program locks up.We think it because the Access db is still connected. How do I kill the connection in the code? Here is my code.
A continuously open connection should not hang another software or connection's access unless the record is locked by the first connection. The statement you've shown us is a single row insert that should never need escalation nor should it have a concurrency delay unless something else has taken exclusive table locks.
Unless another process in the AccessDB is opening/closing transactions and this is a subcomponent, this should have no affect on your email software.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 28, 2011 at 10:47 am
A continuously open connection should not hang another software or connection's access unless the record is locked by the first connection. The statement you've shown us is a single row insert that should never need escalation nor should it have a concurrency delay unless something else has taken exclusive table locks.
Unless another process in the AccessDB is opening/closing transactions and this is a subcomponent, this should have no affect on your email software.
Thanks for the info!
I added the code line Set con = Nothing
just in case. I still need to test it.
Tom
November 29, 2011 at 1:35 pm
Tom,
As Craig pointed out an open connection shouldn't interfere with anything.
However, when using VB/VBA I find that explicitly closing and/or destroying objects before exiting the routine is a good practice. Objects are supposed to be destroyed when they go out of scope, but sometimes it takes a while.
Todd Fifield
December 13, 2011 at 12:07 pm
Code fails now. Don't know why. Last time I used the code 7/2011 it worked fine. Now when I run the code, I get an error.
"Complile error. User-defined type not difined."
I checked to make sure the right library was refereced, and it is.
The code stops at "Set con = New ADODB.Connection"
Any suggestions?
Private Sub Command4_Exit(Cancel As Integer)
Set con = New ADODB.Connection
con.ConnectionString = "Provider=SQLOLEDB.1;Password=xx;User ID =XXXXXXX;Initial Catalog=XXXframework20;Data Source=XXX1SQl08"
con.Open
Set cmd = New ADODB.Command
cmd.ActiveConnection = con
cmd.CommandText = "INSERT INTO sv_process_event_tbl (process_guid,process_event_data_path, create_user) VALUES ('XXX52156X-X226-52X7-X781-X98X
6D85F569 ','\\XXFILESRV1\DATA\EmailXML.XML','CC2007_caccdb')"
cmd.Execute
End Sub
December 14, 2011 at 7:22 am
1. Check the references of your project (in the VBA Editor open the "Tools" menu and select "References"). There should be a reference to the "Microsoft ActiveX Data Objects 2.x Library", with x between 1 and 8 (7 or 8 being the best). Add it if its missing.
2. Declare the variable "con" before using it:
Private Sub Command4_Exit(Cancel As Integer)
Dim con As ADODB.Connection
Set con = New ADODB.Connection
Have a nice day!
December 14, 2011 at 9:25 am
Thanks! that was the problem.
🙂
rf44 (12/14/2011)
1. Check the references of your project (in the VBA Editor open the "Tools" menu and select "References"). There should be a reference to the "Microsoft ActiveX Data Objects 2.x Library", with x between 1 and 8 (7 or 8 being the best). Add it if its missing.2. Declare the variable "con" before using it:
Private Sub Command4_Exit(Cancel As Integer)Dim con As ADODB.Connection
Set con = New ADODB.Connection
Have a nice day!
December 14, 2011 at 9:59 am
You're welcome!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply