ADO Connection question

[AlexanderBB]

Hi,

I have a connection between Excel and Access. From Excel, I query Access and use the Recordset method to put the data into a sheet.

Can I use this to somehow Update or Edit an actual query that's in Access ?

Please advise yes or no. I think its No !

Thanks, ABB

 

[Jon von der Heyden]

remember when I said "DAO is generally faster and easier so if you are using Access, I'd use DAO"...

Yeh yeh

 

[AlexanderBB]

Jon,

I can't figure this, or see how it worked here once and always works for you.

If you want to investigate I've trimmed it back to bare bones-

http://dl.dropbox.com/u/49222457/test.rar

This has an mdb file with one query and an Excel module with just the code shown here previously. I've tried it on 3 different computers, using Excel 2003 and 2007 and it always fails with error 3265.

It it also fails for you, what is different to your one that works, and if it does work for you - well, I don't want to think about that !

Cheers, ABB

 

[Jon von der Heyden]

Can you upload again please, but as a zip rather?

Are you using DAO as a fall-back?

 

[AlexanderBB]

Sure, Jon.

http://dl.dropbox.com/u/49222457/test2.zip

I have also got Rorys routine in and working. It's the only thing that uses DAO. Not sure which to eventually keep, but certainly keen to see what's happening with the one that fails.

 

[RoryA]

is it me or is the source table for the query missing? that's why the error occurs.

 

[Jon von der Heyden]

Hi Again

OK you're not going crazy! I get exactly the same error. Yet if I create a new database and try it, it works fine.

Honest answer right now is 'I don't know', but I will play some more.

 

[RoryA]

It appears that if the SQL refers to an invalid table, the query becomes part of the Procedures collection, otherwise it is part of the Views.

 

[AlexanderBB]

It appears that if the SQL refers to an invalid table, the query becomes part of the Procedures collection, otherwise it is part of the Views.

Ahhhhh, YES !! Just proved that true. Wow. My usage meant I was changing various things, using different tables etc, hence it working once.

Funny, the underlying reason for editing the query is to add a just-created table into a Union Query.

Sanity returns

Thank you BOTH so much !!!!!

 

[Jon von der Heyden]

It appears that if the SQL refers to an invalid table, the query becomes part of the Procedures collection, otherwise it is part of the Views.

Smarty-pants!

So, another day, another lesson!

 

[RoryA]

That's why we're here - I learned something new today too. That's two things already this week.