REcordset wont work

Andrew Poulsom

MrExcel MVP

Joined

Jul 21, 2002

Messages

73,092

• Apr 12, 2011

• #2

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

Rich (BB code):

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

 

Upvote 0

D

doofusboy

Well-known Member

Joined

Oct 14, 2003

Messages

1,325

• Apr 12, 2011

• #3

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.

Code:

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

 

Upvote 0

U

ukmikeb

Well-known Member

Joined

Jul 10, 2009

Messages

2,757

• Apr 12, 2011

• #4

Hi

Try -

Code:

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

 

Last edited: Apr 12, 2011

Upvote 0

T

tatendatiffany

Board Regular

Joined

Mar 27, 2011

Messages

103

• Apr 12, 2011

• #5

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.

 

Upvote 0

D

doofusboy

Well-known Member

Joined

Oct 14, 2003

Messages

1,325

• Apr 12, 2011

• #6

"invalid or unqualified reference"

Click to expand...

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

 

Upvote 0

T

tatendatiffany

Board Regular

Joined

Mar 27, 2011

Messages

103

• Apr 12, 2011

• #7

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

 

Upvote 0

D

doofusboy

Well-known Member

Joined

Oct 14, 2003

Messages

1,325

• Apr 12, 2011

• #8

.Open strSQL

Click to expand...

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

 

Upvote 0

T

tatendatiffany

Board Regular

Joined

Mar 27, 2011

Messages

103

• Apr 12, 2011

• #9

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

 

Upvote 0

D

doofusboy

Well-known Member

Joined

Oct 14, 2003

Messages

1,325

• Apr 12, 2011

• #10

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.

 

Upvote 0