How do you "do" Access queries in VB 
Author Message
 How do you "do" Access queries in VB

I'm a VB newbie, but an old hand at Access coding. We want to migrate a
large Access application to VB 6.0. This app. is at hundreds of client sites
all over the world. It is a traditional front-end/back-end split, but not
client server. We want to make it so that it is multi-user and the user can
configure so that the front and back ends can be in any directory (which is
why I hate the hard-links that Access has... but that is another story.)

Can anyone tell us (in brief) how to do the following:

1. We want to continue using Access for the back-end data. Do you guys keep
the queries in the same MDB as the data? If so, how do you do updates in the
field? Do you send an Access program that will copy in new queries?

2. In Access we have queries that "call" queries, (that call other queries).
Assuming that the queries have to be moved to the code (what a pain!) how do
you write SQL that will do what Access does so well.

What is the technique for doing heavy database applications in VB...
applications that have complex queries that are dependent on "earlier"
queries.

Thanks,

Paul Raymond

============================
If you are in the publishing business, you must see PUB123, the newest and
most affordable back-office software system available. You can find it on
http://www.*-*-*.com/ .
============================



Wed, 31 Oct 2001 03:00:00 GMT  
 How do you "do" Access queries in VB
In VB 6 you can create an ADO Data Environment containing commands which
reference Access queries.  This is in fact what I am working on, and if you work
with the bugginess of the Data Designer, it works pretty well.  The recordsets
created by the query commands are easy to manipulate within VB.  I would expect
your structure of nested Access queries not to cause problems in VB; that's all
on the Access side anyway.  Commands are contained within connections in the
Data Environment, and there is a lot of flexibility in specifying connections.
In summary I would look and ADO and RDO; these should allow you to use your
Access data structure without changes.  Good luck!
Quote:

> I'm a VB newbie, but an old hand at Access coding. We want to migrate a
> large Access application to VB 6.0. This app. is at hundreds of client sites
> all over the world. It is a traditional front-end/back-end split, but not
> client server. We want to make it so that it is multi-user and the user can
> configure so that the front and back ends can be in any directory (which is
> why I hate the hard-links that Access has... but that is another story.)

> Can anyone tell us (in brief) how to do the following:

> 1. We want to continue using Access for the back-end data. Do you guys keep
> the queries in the same MDB as the data? If so, how do you do updates in the
> field? Do you send an Access program that will copy in new queries?

> 2. In Access we have queries that "call" queries, (that call other queries).
> Assuming that the queries have to be moved to the code (what a pain!) how do
> you write SQL that will do what Access does so well.

> What is the technique for doing heavy database applications in VB...
> applications that have complex queries that are dependent on "earlier"
> queries.

> Thanks,

> Paul Raymond

> ============================
> If you are in the publishing business, you must see PUB123, the newest and
> most affordable back-office software system available. You can find it on
> http://www.adams-blake.com.
> ============================



Wed, 31 Oct 2001 03:00:00 GMT  
 How do you "do" Access queries in VB

I played a bit with the Data Environment, but since I don't know beans about
ADO (but I do know DAO well) I am rather confused on the whole thing. I have
some books which I will look at but none of them seem to mention the Data
Envirnoment much. What exactly IS it.

As you stated, the queries will be in Access. But do they have to be in the
MDB with the data? It makes sense until you look at the concept of having to
send out updates to the field. I would assume the easiest way to update
(replace) a query would be to write an Access "update" program that would
Docmd.Transfer a query into the MDB. Is there a way to do that in VB 6.0?
Perhaps via code that refs a QueryDef for the MDB? (I am very weak on
QueryDefs as I never much messed with them before since I never had
applications out in the field. In the office I can just copy them in by
hand!). I don't suppose you have some code that will replace the SQL in an
Access query? There must be a way to do it.

The real problem is that when using Access, when we make an update in the
field, we just send them a whole new front-end to replace the one the have
now. It is a simple Zip extract to the correct folder. With VB 6.0, IF the
queries are going to be in with the data, I have to do something else.

I was thinking I could have a seperate MDB with just queries. But there
would have to be links to the data MDB. I don't think there is a way to set
Access links from VB 6.0, do you? One of our concepts is that the user can
put their data MDB and their VB front end in any directory or server they
want and via a parm file that knows the paths, all will be well. I know I
can easily connect (and change connections) via ADO to MDBs from VB 6. But
how the hell would I change a link from Query.MDB to Data.MDB???

I've been programming for 25 years and I know a lot of stuff, but I just
don't see any "easy" way to have apps. in the field OTHER than via Access97
which just lets me copy in a whole new front end (we often add fields to
tables via SQL in a start-up routine.)

Sorry to ramble, but I'm just so frustrated
in all of this.

Paul Raymond

Quote:
-----Original Message-----

Newsgroups: comp.lang.basic.visual.database

Date: Saturday, May 15, 1999 1:34 PM
Subject: Re: How do you "do" Access queries in VB


>In VB 6 you can create an ADO Data Environment containing commands which
>reference Access queries.  This is in fact what I am working on, and if you
work
>with the bugginess of the Data Designer, it works pretty well.  The
recordsets
>created by the query commands are easy to manipulate within VB.  I would
expect
>your structure of nested Access queries not to cause problems in VB; that's
all
>on the Access side anyway.  Commands are contained within connections in
the
>Data Environment, and there is a lot of flexibility in specifying
connections.
>In summary I would look and ADO and RDO; these should allow you to use your
>Access data structure without changes.  Good luck!


>> I'm a VB newbie, but an old hand at Access coding. We want to migrate a
>> large Access application to VB 6.0. This app. is at hundreds of client
sites
>> all over the world. It is a traditional front-end/back-end split, but not
>> client server. We want to make it so that it is multi-user and the user
can
>> configure so that the front and back ends can be in any directory (which
is
>> why I hate the hard-links that Access has... but that is another story.)

>> Can anyone tell us (in brief) how to do the following:

>> 1. We want to continue using Access for the back-end data. Do you guys
keep
>> the queries in the same MDB as the data? If so, how do you do updates in
the
>> field? Do you send an Access program that will copy in new queries?

>> 2. In Access we have queries that "call" queries, (that call other
queries).
>> Assuming that the queries have to be moved to the code (what a pain!) how
do
>> you write SQL that will do what Access does so well.

>> What is the technique for doing heavy database applications in VB...
>> applications that have complex queries that are dependent on "earlier"
>> queries.

>> Thanks,

>> Paul Raymond

>> ============================
>> If you are in the publishing business, you must see PUB123, the newest
and
>> most affordable back-office software system available. You can find it on
>> http://www.adams-blake.com.
>> ============================



Wed, 31 Oct 2001 03:00:00 GMT  
 How do you "do" Access queries in VB
Yes you can do it all in vb using querydefs and dao, you can use parametric
queries for looking up data as well as writing data, i keep all my nested
queries in the same database also, you might also want to look at using
undound data access, it's a lot more flexible than the data controls..

Chris

Quote:

>I'm a VB newbie, but an old hand at Access coding. We want to migrate a
>large Access application to VB 6.0. This app. is at hundreds of client
sites
>all over the world. It is a traditional front-end/back-end split, but not
>client server. We want to make it so that it is multi-user and the user can
>configure so that the front and back ends can be in any directory (which is
>why I hate the hard-links that Access has... but that is another story.)

>Can anyone tell us (in brief) how to do the following:

>1. We want to continue using Access for the back-end data. Do you guys keep
>the queries in the same MDB as the data? If so, how do you do updates in
the
>field? Do you send an Access program that will copy in new queries?

>2. In Access we have queries that "call" queries, (that call other
queries).
>Assuming that the queries have to be moved to the code (what a pain!) how
do
>you write SQL that will do what Access does so well.

>What is the technique for doing heavy database applications in VB...
>applications that have complex queries that are dependent on "earlier"
>queries.

>Thanks,

>Paul Raymond

>============================
>If you are in the publishing business, you must see PUB123, the newest and
>most affordable back-office software system available. You can find it on
>http://www.adams-blake.com.
>============================



Wed, 07 Nov 2001 03:00:00 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. Doing "Stuff" in the background

2. Wait until done "thinking"?

3. Wait until done "thinking"?

4. Wait until done "thinking"?

5. Newbie question: "undefined function in query", Access db

6. ACCESS ADO vs a "STORED QUERY"

7. ""Declare" query

8. ADO thinks DBMS is done, but DBMS ain't done

9. Doing a Query in VB.NET

10. *"*-.,._,.-*"* I"LL TRADE VISUAL C++ FOR VBASIC *"*-.,_,.-*"*

11. Importing a MS Access form to a VB Project can it be done

12. "Access/VB/Bitmap/Wave Question?"

 

 
Powered by phpBB® Forum Software