QueryTables, some basic questions

[Maury Markowitz]

I was wondering if someone can help me get up and running with
QueryTables under VBA. The dox on the web are fairly lightweight.

Normally I do my DB access using VBA code and ADODB. I find that
processing the result set in VBA gives me capabilities that I lack
using things like MSQuery, especially things like control over the
timing of the queries and such.

But on this current system, ADODB simply won't run, it complains about
the connection string no matter what I do. In desperation I came
across some code using QueryTables to build a SQL query in VBA, and
when I tried it, presto, no problem at all. The reason one works and
the other doesn't will remain a mystery it seems.

But that's about as far as that document went, and there doesn't seem
to be a lot more out there. Experimenting further I found that every
time I use the .Add method to run the query, I would get another
QueryTable that seemed to me magically saved into the sheet without my
intervention.

So, can someone point me, or help me, get a handle on the QueryTable
object? My basic goal is to use them in an ADODB fashion - that is,
run some code that produces the output, waits while this happens, and
then processes it in code to produce the exact results I need.

 

[GS]

It happens that Maury Markowitz formulated :

I was wondering if someone can help me get up and running with
QueryTables under VBA. The dox on the web are fairly lightweight.

Normally I do my DB access using VBA code and ADODB. I find that
processing the result set in VBA gives me capabilities that I lack
using things like MSQuery, especially things like control over the
timing of the queries and such.

But on this current system, ADODB simply won't run, it complains about
the connection string no matter what I do. In desperation I came
across some code using QueryTables to build a SQL query in VBA, and
when I tried it, presto, no problem at all. The reason one works and
the other doesn't will remain a mystery it seems.

But that's about as far as that document went, and there doesn't seem
to be a lot more out there. Experimenting further I found that every
time I use the .Add method to run the query, I would get another
QueryTable that seemed to me magically saved into the sheet without my
intervention.

So, can someone point me, or help me, get a handle on the QueryTable
object? My basic goal is to use them in an ADODB fashion - that is,
run some code that produces the output, waits while this happens, and
then processes it in code to produce the exact results I need.

Have a look at the sample files for using ADODB with VBA here...

http://www.appspro.com/conference/DatabaseProgramming.zip

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[Maury Markowitz]

These were examples of ADO under VBA.

As I noted, ADO is *not working*. It doesn't work on the Mac at all,
and is not working correctly on the PC either.

 

[GS]

Maury Markowitz was thinking very hard :

These were examples of ADO under VBA.

Well.., then it sounds appropriate since this is an Excel VBA forum!

As I noted, ADO is *not working*. It doesn't work on the Mac at all,
and is not working correctly on the PC either.

I base my use of ADODB on those samples and all my apps work just fine!
(Can't speak to how it would work on a Mac)

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[GS]

Maury Markowitz expressed precisely :

These were examples of ADO under VBA.

As I noted, ADO is *not working*. It doesn't work on the Mac at all,
and is not working correctly on the PC either.

Okay, I might be able to find a solution if you can tell me what OS the
website uses, AND what db server is on it...

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[GS]

Here's a link to an article on using ADODB with VB to access a remote
db...(watch for linewrap)

http://forums.devshed.com/mysql-help-4/disconnected-recordsets-27858.html?forumid=4

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion