 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / General 2 / July 2008Error when converted to 2007
After converting to MA 2007 from MA 2002 I am getting this error when I try to Create a Holding Invoice Run-Time Error '3709' The connection cannot be used to preform this operation.It is either closed or Invalid in this context PLEASE HELP!  SignatureThanks in advance for any help with this......Bob WindowsXP..MS Access 2007 1. Make sure you have a reference set to ADO if you are using it (it is not the default in 2007) 2. More likely is that the reference is there (since you converted) but that you are using code like Dim rst As Recordset when it should be explicit: Dim rst As ADODB.Recordset SignatureBob Larson Access MVP Access World Forums Administrator Utter Access VIP Tutorials at http://www.btabdevelopment.com __________________________________ > After converting to MA 2007 from MA 2002 I am getting this error when I try > to Create a Holding Invoice > Run-Time Error '3709' > The connection cannot be used to preform this operation.It is either > closed or Invalid in this context > PLEASE HELP! Hi Bob, > 1. Make sure you have a reference set to ADO if you are using it (it is not > the default in 2007) Really? If you create a new database in Access 2007, you will not have a checked reference to ADO? That's news to me. (If you convert an existing database, one would not expect a reference to ADO to be added, if it was not present). > 2. More likely is that the reference is there (since you converted) but that > you are using code like [quoted text clipped - 4 lines] > > Dim rst As ADODB.Recordset While I agree with your advice that recordsets should be explicitely declared, I have never seen Run-Time Error '3709' in this context. The only run-time error I have seen when the wrong library is used, because of priority, is Run-time error '13' Type mismatch. I have an article that discusses this error: ADO and DAO Library References in Access Databases http://www.accessmvp.com/TWickerath/articles/adodao.htm Tom Wickerath Microsoft Access MVP http://www.accessmvp.com/TWickerath/ http://www.access.qbuilt.com/html/expert_contributors.html __________________________________________ > 1. Make sure you have a reference set to ADO if you are using it (it is not > the default in 2007) [quoted text clipped - 7 lines] > > Dim rst As ADODB.Recordset Hi Bob, Here are some possibilities: Northwind sample database opens with a "Run-time error '3709'" error message (in Access 2002) http://support.microsoft.com/kb/276389 Note: This likely applies to Access 2003 and 2007 as well. Error message when you enter a large amount of text in a Memo field that contains an index in Access: "Run-time error '3709'" http://support.microsoft.com/kb/302525 Probably not a high probability on this one: PRB: "Class Not Registered" Error Opening an XML Recordset http://support.microsoft.com/kb/198533 Tom Wickerath Microsoft Access MVP http://www.accessmvp.com/TWickerath/ http://www.access.qbuilt.com/html/expert_contributors.html __________________________________________ > After converting to MA 2007 from MA 2002 I am getting this error when I try > to Create a Holding Invoice > Run-Time Error '3709' > The connection cannot be used to preform this operation.It is either > closed or Invalid in this context > PLEASE HELP! Thanks Tom, I am getting the yellow line error on this: recInvoice_ItMdt.Open "SELECT * FROM tblInvoice_ItMdt", cnnStableAccount, adOpenDynamic, adLockOptimistic And have the same references that I had in 2002, when it was working fine, Thanks Bob > Hi Bob, > [quoted text clipped - 26 lines] >> closed or Invalid in this context >> PLEASE HELP! What is the rest of your code, including variable declarations? SignatureBob Larson Access MVP Access World Forums Administrator Utter Access VIP Tutorials at http://www.btabdevelopment.com __________________________________ > Thanks Tom, I am getting the yellow line error on this: > recInvoice_ItMdt.Open "SELECT * FROM tblInvoice_ItMdt", cnnStableAccount, [quoted text clipped - 32 lines] > >> closed or Invalid in this context > >> PLEASE HELP! Thanks Bob Here is my Code, Regards Bob: Private Sub cmdCreateHoldingInvoices_Click() 'It creates the holding Invoices for selected horses. Dim recInvoice_ItMdt As ADODB.Recordset Set recInvoice_ItMdt = New ADODB.Recordset Dim recHorseInfo As ADODB.Recordset Set recHorseInfo = New ADODB.Recordset Dim recTmpInvoice_ItMdt As ADODB.Recordset Set recTmpInvoice_ItMdt = New ADODB.Recordset Dim lngIntermediateID As Long Dim nloop As Long 'To Save the record in alphabetical order. recInvoice_ItMdt.Open "SELECT * FROM tblInvoice_ItMdt", cnnStableAccount, adOpenDynamic, adLockOptimistic 'ERROR LINE YELLOW For nloop = 0 To lstActiveHorses.ListCount - 1 If lstActiveHorses.Selected(nloop) = True Then Debug.Print lstActiveHorses.Column(1, nloop) 'Prints the Horse Name Debug.Print lstActiveHorses.Column(0, nloop) 'Prints the Horse Id recHorseInfo.Open "Select * from tblHorseInfo where HorseID=" _ & lstActiveHorses.Column(0, nloop) & ";", cnnStableAccount, adOpenDynamic, adLockOptimistic If recHorseInfo.BOF = False And recHorseInfo.EOF = False Then With recInvoice_ItMdt If recInvoice_ItMdt.BOF = False And recInvoice_ItMdt.EOF = False Then .MoveLast lngIntermediateID = Nz(.Fields("IntermediateID"), 0) + 1 Else lngIntermediateID = 1 End If .AddNew .Fields("IntermediateID") = lngIntermediateID .Fields("dtDate") = Format(Now, "dd/mm/yyyy") .Fields("HorseName") = lstActiveHorses.Column(1, nloop) .Fields("HorseID") = lstActiveHorses.Column(0, nloop) .Fields("FatherName") = Nz(recHorseInfo.Fields("FatherName"), "") .Fields("MotherName") = Nz(recHorseInfo.Fields("MotherName"), "") .Fields("HorseDetailInfo") = Nz(recHorseInfo.Fields("FatherName"), "") _ & "--" & Nz(recHorseInfo.Fields("MotherName"), "") & "--" _ & funCalcAge(Format(Nz(recHorseInfo.Fields("DateOfBirth"), "") _ , "dd-mmm-yyyy"), Format("01-Aug-" & Year(Now()), "dd-mmm-yyyy"), 1) _ & "-" & Nz(recHorseInfo.Fields("Sex"), "") .Fields("Sex") = Nz(recHorseInfo.Fields("Sex"), "") .Fields("DateOfBirth") = Nz(recHorseInfo.Fields("DateOfBirth"), "") 'Set the default value to GSTOptionText field. .Fields("GSTOptionsText") = "Plus Tax" .Fields("GSTOptionsValue") = 0 .Fields("SubTotal") = 0 .Fields("TotalAmount") = 0 Application.SysCmd acSysCmdSetStatus, "Horse Name=" & .Fields("HorseName") .Update .Requery End With End If recHorseInfo.Close End If Next Me.lstActiveHorses.Requery Application.SysCmd acSysCmdClearStatus End Sub > What is the rest of your code, including variable declarations? > [quoted text clipped - 37 lines] >> >> closed or Invalid in this context >> >> PLEASE HELP! Sorry Guys, My fault did not set cnnStableaccount on load form duuuuuuuuuuuuur Bob > Thanks Bob Here is my Code, Regards Bob: > Private Sub cmdCreateHoldingInvoices_Click() [quoted text clipped - 115 lines] >>> >> closed or Invalid in this context >>> >> PLEASE HELP! I see you using cnnStableAccount in the code: recInvoice_ItMdt.Open But nowhere are you setting the connection (at least not here). How are you opening the connection? SignatureBob Larson Access MVP Access World Forums Administrator Utter Access VIP Tutorials at http://www.btabdevelopment.com __________________________________ > Thanks Bob Here is my Code, Regards Bob: > Private Sub cmdCreateHoldingInvoices_Click() [quoted text clipped - 114 lines] > >> >> closed or Invalid in this context > >> >> PLEASE HELP! Hi Bob and Bob, To Bob Vance - I believe I have an identical copy of your code, from last summer when you sent me a copy of your database. I will try running it in Access 2007 tomorrow afternoon, and let you know the result. (I only have Access 2007 installed on my laptop PC, which is shut down right now. I'd test it tonight, but it's already midnight, and I have to start my day tomorrow in about 5 hours). Question for you: Did you run your database in Access 2007 as a .mdb file, or did you convert it to the new .accdb file format? I'll try to duplicate exactly what you did. To Bob Larson - The answer to your follow-on question, "How are you opening the connection?", appears to be (from my copy of Bob V's database dated 7/31/2007) as follows: Option Compare Database Dim cnnStableAccount As Connection <---Module level variable Private Sub Form_Load() Set cnnStableAccount = CurrentProject.Connection End Sub Tom Wickerath Microsoft Access MVP http://www.accessmvp.com/TWickerath/ http://www.access.qbuilt.com/html/expert_contributors.html __________________________________________ > Thanks Bob Here is my Code, Regards Bob: <snip> Sorry Guys I must have commered out when I was trying to load the new references Private Sub Form_Load() 'Set cnnStableAccount = CurrentProject.Connection End Sub Anyway took the commer out and now alls GOOD :) Tom, I did convert it to .accdb but still have a back up in .mdb A question you might be able to answer if I want to distrubute a version should I a) KeyedAccess lock it b) Put it on a server Front End and Back End What are your thoughts........Regards Bob > Hi Bob and Bob, > [quoted text clipped - 31 lines] > > <snip> Hi Bob, > A question you might be able to answer if I want to distrubute a version > should I > a) KeyedAccess lock it > b) Put it on a server Front End and Back End > What are your thoughts Sorry, I missed your latest reply earlier.... I don't know what "KeyedAccess" is. Is this some type of commercial add-on? My thoughts on running a successful multiuser application are pretty much summed up in this article: Implementing a Successful Multiuser Access/JET Application http://www.accessmvp.com/TWickerath/articles/multiuser.htm If you are talking a Local Area Network (LAN), then I'd use Access MVP Tony Toews free AutoFE Updater utility: http://www.granite.ab.ca/access/autofe.htm Tom Wickerath Microsoft Access MVP http://www.accessmvp.com/TWickerath/ http://www.access.qbuilt.com/html/expert_contributors.html __________________________________________ > Sorry Guys I must have commered out when I was trying to load the new > references [quoted text clipped - 8 lines] > b) Put it on a server Front End and Back End > What are your thoughts........Regards Bob |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.