Database Connection string: Run-time error 3709

[tatendatiffany]

Yes i have quickly become a regular on this board but could someone please help me now its with the conection string. I am connected to the database but when i run the code it gives me run time error 3709 "the connection cannot be used to perform this operation. it is either closed or invalid in this context". But i am already commected to the database (through a login userform):

Public Sub Queries()
Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection
Dim strSQL1 As String
Set rs = New ADODB.Recordset
Set cn = New ADODB.Connection<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
With MFcnn
.Provider = "IBMDADB2.DB2COPY1"
.Mode = adReadWrite
.ConnectionString = "Password=" & password & ";Persist Security Info=True;User ID=" & username & ";Data Source=" & DatabaseEnv & ";Mode=ReadWrite;"
'.Open

End With<o></o>
strSQL1 = "SELECT COUNT (INUNACPT) FROM AIS3AM4.KTPT80T WHERE CDPRODCO=RETAIL_MAP"
rs.Open strSQL1, cn
Index.Range("AC2").CopyFromRecordset rs
rs.Close<o></o>
strSQL2 = "SELECT SUM (POGEN153) FROM AIS3AM4.KTPT81T WHERE CDPRODCO=RETAIL_MAP"
rs.Open strSQL2, cn
Index.Range("AC3").CopyFromRecordset rs
rs.Close<o></o>
strSQL3 = "SELECT SUM (NUDRWEH) FROM AIS3AM4.KTPTDRT WHERE CDPRODCO=RETAIL_MAP"
rs.Open strSQL3, cn
Index.Range("AC4").CopyFromRecordset rs
rs.Close<o></o>
strSQL4 = "SELECT SUM (NUMODSCE) FROM AIS3AM4.KTPTW1T WHERE CDPRODCO =RETAIL_MAP"
rs.Open strSQL4, cn
Index.Range("AC5").CopyFromRecordset rs
rs.Close<o></o>
strSQL5 = "SELECT SUM (NUMODCNT) FROM AISA3M4.KTPTZIT WHERE CDPRODCO =RETAIL_MAP"
rs.Open strSQL5, cn
Index.Range("AC6").CopyFromRecordset rs
rs.Close<o></o>
strSQL6 = "SELECT COUNT (INACCEPT) FROM AIS3AM4.KTPTZIT WHERE CDPRODCO =RETAIL_MAP"
rs.Open strSQL6, cn
Index.Range("AC7").CopyFromRecordset rs
rs.Close<o></o>
strSQL7 = "SELECT SUM (NUCLAPER) FROM AIS3AM4.KERT38T WHERE CDPRODCO =RETAIL_MAP"
rs.Open strSQL7, cn
Index.Range("AC8").CopyFromRecordset rs
rs.Close<o></o>
strSQL8 = "SELECT SUM (NUCLAYEA) FROM AIS3AM4.KERT39T WHERE CDPRODCO =RETAIL_MAP"
rs.Open strSQL8, cn
Index.Range("AC9").CopyFromRecordset rs
rs.Close<o></o>
strSQL9 = "SELECT COUNT (INACCEPT) FROM AIS3AM4.KTPTD9T WHERE CDPRODCO =RETAIL_MAP"
rs.Open strSQL9, cn
Index.Range("AC10").CopyFromRecordset rs
rs.Close<o></o>
strSQL10 = "SELECT COUNT (INRATDRV) FROM AIS3AM4.KTPT7MT WHERE CDPRODCO =RETAIL_MAP"
rs.Open strSQL10, cn
Index.Range("AC11").CopyFromRecordset rs
rs.Close<o></o>
strSQL11 = "SELECT COUNT (INRATDRV) FROM AIS3M4.KTPT7NT WHERE CDPRODCO =RETAIL_MAP"
rs.Open strSQL11, cn
Index.Range("AC12").CopyFromRecordset rs
rs.Close<o></o>
strSQL12 = "SELECT COUNT (INACCEPT) FROM AIS3M4.KTPT7OT WHERE CDPRODCO =RETAIL_MAP"
rs.Open strSQL12, cn
Index.Range("AC13").CopyFromRecordset rs
rs.Close<o></o>
strSQL13 = "SELECT COUNT (INACCEPT) FROM AIS3M4.KTPTDCT WHERE CDPRODCO =RETAIL_MAP"
rs.Open strSQL13, cn
Index.Range("AC14").CopyFromRecordset rs
rs.Close<o></o>
strSQL14 = "SELECT SUM (VAVEHICM) FROM AIS3M4.KTPT68T WHERE CDPRODCO =RETAIL_MAP"
rs.Open strSQL14, cn
Index.Range("AC15").CopyFromRecordset rs
rs.Close<o></o>

strSQL15 = "SELECT COUNT (INRATDRV) FROM AIS3AM4.KTPTC0T WHERE CDPRODCO =RETAIL_MAP"
rs.Open strSQL15, cn
Index.Range("AC16").CopyFromRecordset rs
rs.Close<o></o>

strSQL16 = "SELECT COUNT (INRATDRV) FROM AIS3AM4.KTPTC3T WHERE CDPRODCO =RETAIL_MAP"
rs.Open strSQL16, cn
Index.Range("AC17").CopyFromRecordset rs
rs.Close<o></o>
strSQL17 = "SELECT SUM (INUNACPT) FROM AIS3AM4.KTPTC4T WHERE CDPRODCO =RETAIL_MAP"
rs.Open strSQL17, cn
Index.Range("AC18").CopyFromRecordset rs
rs.Close<o></o>

strSQL18 = "SELECT SUM (POGEN153 ) FROM AIS3M4.KTPTC6T WHERE CDPRODCO =RETAIL_MAP"
rs.Open strSQL, cn
Index.Range("AC19").CopyFromRecordset rs
rs.Close<o></o>
strSQL19 = "SELECT SUM (POGEN153 ) FROM AIS3M4.KTPTC6T WHERE CDPRODCO =RETAIL_MAP"
rs.Open strSQL19, cn
Index.Range("AC20").CopyFromRecordset rs
rs.Close<o></o>
strSQL20 = "SELECT SUM (INACCEPT) FROM AIS3M4.KTPT7JT WHERE CDPRODCO =RETAIL_MAP"
rs.Open strSQL20, cn
Index.Range("AC21").CopyFromRecordset rs
rs.Close<o></o>
strSQL21 = "SELECT SUM (NUMILLIM) FROM AIS3M4.KTPT7KT WHERE CDPRODCO =RETAIL_MAP"
rs.Open strSQL21, cn
Index.Range("AC22").CopyFromRecordset rs
rs.Close<o></o>

strSQL22 = "SELECT SUM (INOCWARN) FROM AIS3M4.KTPT7LT WHERE CDPRODCO =RETAIL_MAP"
rs.Open strSQL22, cn
Index.Range("AC23").CopyFromRecordset rs
rs.Close<o></o>
strSQL23 = "SELECT SUM (VARTDAGM) FROM AIS3M4.KTPT7PT WHERE CDPRODCO =RETAIL_MAP"
rs.Open strSQL23, cn
Index.Range("AC24").CopyFromRecordset rs
rs.Close<o></o>
strSQL24 = "SELECT SUM (CDMINSEC) FROM AIS3M4.KTPTK7T WHERE CDPRODCO =RETAIL_MAP"
rs.Open strSQL24, cn
Index.Range("AC25").CopyFromRecordset rs
rs.Close<o></o>

strSQL25 = "SELECT COUNT (CDSECSCO) FROM AIS3AM4.KTPTK8T WHERE CDPRODCO =RETAIL_MAP"
rs.Open strSQL25, cn
Index.Range("AC26").CopyFromRecordset rs
rs.Close<o></o>
strSQL26 = "SELECT COUNT (INACCEPT) FROM AIS3AM4.KTPTK9T WHERE CDPRODCO =RETAIL_MAP"
rs.Open strSQL26, cn
Index.Range("AC27").CopyFromRecordset rs
rs.Close<o></o>

strSQL27 = "SELECT COUNT (INACCEPT) FROM AIS3AM4.KTPTK9T WHERE CDPRODCO =RETAIL_MAP"
rs.Open strSQL27, cn
Index.Range("AC28").CopyFromRecordset rs
rs.Close<o></o>
<o></o>
strSQL28 = "SELECT COUNT (CDADDSEC) FROM AIS3AM4.KTPTKDT WHERE CDPRODCO =RETAIL_MAP"
rs.Open strSQL28, cn
Index.Range("AC29").CopyFromRecordset rs
rs.Close<o></o>
strSQL29 = "SELECT SUM (VAONGTXS) FROM AIS3AM4.KTPT8HT WHERE CDPRODCO =RETAIL_MAP"
rs.Open strSQL29, cn
Index.Range("AC30").CopyFromRecordset rs
rs.Close<o></o>
strSQL30 = "SELECT COUNT (INACCEPT) FROM AIS3AM4.KTPT9TT WHERE CDPRODCO =RETAIL_MAP"
rs.Open strSQL30, cn
Index.Range("AC31").CopyFromRecordset rs
rs.Close<o></o>
strSQL31 = "SELECT COUNT (INACCEPT) FROM AIS3M4.KTPT9UT WHERE CDPRODCO =RETAIL_MAP"
rs.Open strSQL31, cn
Index.Range("AC32").CopyFromRecordset rs
rs.Close<o></o>
strSQL32 = "SELECT COUNT (INRATDRV) FROM AIS3M4.KTPT9VT WHERE CDPRODCO =RETAIL_MAP"
rs.Open strSQL32, cn
Index.Range("AC33").CopyFromRecordset rs
rs.Close<o></o>

strSQL33 = "SELECT COUNT (INRATDRV) FROM AIS3M4.KTPT9WT WHERE CDPRODCO =RETAIL_MAP"
rs.Open strSQL33, cn
Index.Range("AC34").CopyFromRecordset rs
rs.Close<o></o>

strSQL34 = "SELECT COUNT (CDCNVCLA) FROM AIS3M4.KTPTCCT WHERE CDPRODCO =RETAIL_MAP"
rs.Open strSQL34, cn
Index.Range("AC35").CopyFromRecordset rs
rs.Close<o></o>

strSQL35 = "SELECT COUNT (INFLTIND) FROM AIS3AM4.KTPTCDT WHERE CDPRODCO =RETAIL_MAP"
rs.Open strSQL35, cn
Index.Range("AC36").CopyFromRecordset rs
rs.Close<o></o>
<o></o>
strSQL36 = "SELECT SUM (NUCONPTS) FROM AIS3AM4.KTPTDET WHERE CDPRODCO =RETAIL_MAP"
rs.Open strSQL36, cn
Index.Range("AC37").CopyFromRecordset rs
rs.Close<o></o>
<o></o>
strSQL37 = "SELECT SUM (NUDISQPD) FROM AIS3AM4.KTPTDFT WHERE CDPRODCO =RETAIL_MAP"
rs.Open strSQL37, cn
Index.Range("AC38").CopyFromRecordset rs
rs.Close<o></o>
<o></o>

strSQL38 = "SELECT SUM (VAUBDRNK) FROM AIS3AM4.KTPTKHT WHERE CDPRODCO =RETAIL_MAP"
rs.Open strSQL38, cn
Index.Range("AC39").CopyFromRecordset rs
rs.Close<o></o>
<o></o>
strSQL39 = "SELECT COUNT (INUNACPT) FROM AIS3AM4.KTPTD8T WHERE CDPRODCO =RETAIL_MAP"
rs.Open strSQL39, cn
Index.Range("AC40").CopyFromRecordset rs
rs.Close<o></o>
strSQL40 = "SELECT SUM (CDDRAGFR) FROM AIS3AM4.KTPTKIT WHERE CDPRODCO =RETAIL_MAP"
rs.Open strSQL40, cn
Index.Range("AC41").CopyFromRecordset rs
rs.Close<o></o>
rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing
End Sub<o></o>