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

 

[Andrew Poulsom]

You need to change the name of one of the variables here:

Dim rst As ADODB.Recordset
Dim rst As ADODB.Connection

 

[doofusboy]

You are declaring 'rst' as both a recordset object and as a connection object and Excel is confused. Just change one of the 'rst' to something else.

Dim [B][COLOR=red]rst[/COLOR][/B] As ADODB.Recordset
Dim [COLOR=red][B]rst [/B][/COLOR]As ADODB.Connection

 

[ukmikeb]

Hi

Try -

Dim rst As ADODB.Recordset
Dim conn As ADODB.Connection

Also, you need to define where the database is and then Open the database through the connection to retrieve your recordset.

hth

 

[tatendatiffany]

Ohh thank you guys for your help it works but anotherf quick question
so on

.openstrSQL1

it gives me the error "invalid or unqualified reference" i am thinking it is because is is not yet connected to the database or is it a syntax code error.

 

[doofusboy]

"invalid or unqualified reference"

Because you're giving an Open command without saying [qualifying] what it is you wan to open.

If you renamed your connection object as advised by 'ukmikeb' you could use:

conn.open strSQL1

 

[tatendatiffany]

I have changed it and now that part works but now i am getting "object required" on that line.
But i dont understand becuase when i hover over it it shows the sql query its suppose to run??

any ideas

 

[doofusboy]

.Open strSQL

You're not listening. You need to put something IN FRONT OF the .Open.

 

[tatendatiffany]

I'm sorry but i am really really confused and i dont seem to understand.
What do you mean put something in from of .open i change it to

cnn.Open strSQL but it is giving me object required.
So do you mean put another query??

(i'm sorry i am beginner and i just cant seem to get a grasp of this)

Thank you

 

[doofusboy]

Don't be sorry or get frustrated. We've all been there. You seem to be trying and that's the most important thing.

Please post your full code exactly as you have it now. And PLEASE use code tags.