Error when converted to 2007

[Bob Vance]

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!

 

[boblarson]

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


--
Bob Larson
Access MVP
Access World Forums Administrator
Utter Access VIP

Tutorials at http://www.btabdevelopment.com

__________________________________

 

[Tom Wickerath]

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

Dim rst As Recordset

when it should be explicit:

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
__________________________________________

 

[Tom Wickerath]

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
__________________________________________

 

[Bob Vance]

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

 

[boblarson]

What is the rest of your code, including variable declarations?
--
Bob Larson
Access MVP
Access World Forums Administrator
Utter Access VIP

Tutorials at http://www.btabdevelopment.com

__________________________________

 

[Bob Vance]

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
recHorseInfpen "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?
--
Bob Larson
Access MVP
Access World Forums Administrator
Utter Access VIP

Tutorials at http://www.btabdevelopment.com

__________________________________

 

[Bob Vance]

Sorry Guys, My fault did not set cnnStableaccount on load form
duuuuuuuuuuuuur Bob

 

[boblarson]

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?
--
Bob Larson
Access MVP
Access World Forums Administrator
Utter Access VIP

Tutorials at http://www.btabdevelopment.com

__________________________________

 

[Tom Wickerath]

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>

 

[Bob Vance]

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

 

[Tom Wickerath]

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
__________________________________________