REcordset wont work

[tatendatiffany]

someone please help me i have created a recordset which is suppose to run an sql but it is giving me an error "duplicate declaration in current scope".
all i have done is used: adodb.recordset again but what can i change it because i need abother recordset??

Public Sub Queries()
Dim rst As ADODB.Recordset
Dim rst As ADODB.Connection
Dim strSQL1 As String
strSQL1 = "SELECT COUNT (INUNACPT) FROM AIS3AM4.KTPT80T WHERE CDPRODCO=RETAIL_MAP"
.Open strSQL
Sheet1.range("AC2").CopyFromRecordset
.Close
End Sub

 

[tatendatiffany]

Thank you very much ok so this is what i have done so far:

"Public sub queries ()
Dim rst1 as ADODB.Recordset
Dim rst1 as ADODB.Connection
Dim str SQL1 as string
strSQL1="SELECT COUNT (INUNACPT) FROM AIS3.QM4.KTPT80T WHERE CDPRODCO=RETAIL_MAP"
Index. Range ("AC2"). worksheet ' this is were i want the result to be displayed.

I am thinking it is mainly to do with how i have structured the recordset, could it be missing other key fields
btw the longer code work (which connects to the database), its just this part that i am struggling on.

 

[xenou]

With ADO you need to dim the variables, create the objects, open the objects, then use the objects and finally close them. All in turn. Neither your connection nor recordset are created or opened when you try to use them.


Yes, it will help to see where you are at now with your revisions, as noted in the last post.

 

[xenou]

Something like this is needed:

Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection
Dim strSQL1 As String
strSQL1 = "SELECT COUNT (INUNACPT) FROM AIS3.QM4.KTPT80T WHERE CDPRODCO=RETAIL_MAP"

Set rs = New ADODB.Recordset
Set cn = New ADODB.Connection

cn.Open CONNECTION_STRING '//You must provide a connection string here
rs.Open strSQL1, cn

rs.CopyFromRecordset Index.Range("AC2")

rs.Close
cn.Close

I'm not sure where you looked to get a sample of ADO code but you must go back and re-read the lesson - there's a lot missing in your attempt, and even bad syntax that won't even compile in VBA.

 

[tatendatiffany]

Thank you very much, I must admit I haven't been properly checking all the syntax and structure of the formulas i had been using I am going to go back to my notes (practice more).


thank you again

 

[tatendatiffany]

I have used the code you gave me but now i am getting the error "methos or data member not found" on the line

rs."CopyFromRecordset"

I have checked to see if i'm logged into the database and indeed I am.

 

[xenou]

Sorry, that's backwards. It's method of the range. I think it should be (but I haven't checked as I'm not at my pc):

Index.Range("AC2").CopyFromRecordSet rs

 

[xenou]

There's also a samples here:

http://www.erlandsendata.no/english/index.php?d=envbadacexportado

http://www.datawright.com.au/excel_resources/excel_access_and_ado_part_4.htm

And good ADO general info here:
http://www.w3schools.com/ado/default.asp