Refresh ADODB Recordset

[oteixeira]

Hello to all.
I’m trying to use Excel 2007 to extract data from an Access 2007 database using ADODB. I know how to connect to the database. What I’m trying to achieve is to write the SQL query with parameters (using the worksheet cells) and then write a user defined function to refresh the recordset.
Does anyone have a piece of code to share where I can learn the basics to get this?
Thanks in advance for any kind help.
Octavio.

[Kyle123]

Hi,

Can you post the code that you already have and I'll adapt it to make it a bit more dynamic

[oteixeira]

Thanks for answering. Well, at this time i don't have much more than the conenction and the query. I'm not sure if i can refer to the parameters as "?"
Thank you

Please Login or Register  to view this content.

[Kyle123]

see if this answers your questions:

Please Login or Register  to view this content.

It will dump the results in Sheet1, starting at A1

[oteixeira]

Thank you again for you kind help. This is not exactly what i'm looking for but gives me some clues. I wrote a lot of code to get out data from an oracle database but I'm struggling whith Access. Will it be possible to use input and output parameters with Access. Just let me show the code for oracle, perhaps you know to do it with Access:

To get the data:

Please Login or Register  to view this content.

The function that puts the data into the cell:

Please Login or Register  to view this content.

Sorry to bother you with this but it will be great if i could achieve this using ADODB and Access.

Octavio

[Kyle123]

Ah got you now, I understand, unfortunately as far as I know output parameters are not supported in Access

[oteixeira]

I think ADODB supports output parameters, because you can define:

Please Login or Register  to view this content.

Now, in the SELECT statement how can i, if possible, use the INTO clause?

Thanks again.

Octavio

[Kyle123]

ADODB can, but it can interact with SQL server, I don't think the access database engine can however

[oteixeira]

Ok Kyle, thanks a lot for your help, anyway.

Octavio

[JohnM3]

Hi OT

I will need 3 posts for this lenght issue . This is post 1 of 3
Copy and paste the code in new WB. Create Blank Access DB and put
in same Dir as WB, Call DB MyDB.accdb.

Code tested and works with 2007 and shd work with 2003 and prior as Provider
is the only real change.

There is only 1 subroutine called Sub Main

It will:
create table
insert records
create stored procs
create ADO params
write recs to sheet1

WARNING:
Every other time the Sub runs it will DELETE table and immediately exit sub. By design.
Lets you tinker with CreateTable / Inserts / and modify Stored Procs
Stored Procs only created once. You must manually delete if u need to. If you change the
table you must change the StoredProc definitions. There are 3 stored proc - TEXT - DATE - DECIMAL.
Refer to links for deeper think.

hth
regards
John

Post 1 of 3

Please Login or Register  to view this content.

[JohnM3]

Post 2 of 3

Please Login or Register  to view this content.

[JohnM3]

post 3 of 3

Please Login or Register  to view this content.

[Kyle123]

Hi John, great bit of code

Just to reiterate, John's code only works for input parameters, unfortunately it can't be used for output parameters.