MS Access Query in VB6

4 Jun 2009 10:22 PM

Brian

I just started using "Replicated" access databases for my VB6 applications.
This has created a bit of a problem... I use to be able to run basic queries
with the "*" wildcard character and parse the recordset as needed.

Now, that same query recordset from the replicated database comes back with
several additional fields that are added by Access for replicated DB
synchronization. So I am forced to specifically request fields in my queries.

That isn't too bad except for the times that I want to get generic DB
structure, ie., Field Names or actual field counts for a specific table.
There are several instances when this is needed verses using hard coded
queries.

My question??? is there a way around getting theses Access included fields
in my specific queries? I looked into trying to process the field types or
other field attributes for some of these fields and there wasn't a good flag
that I could use to exclude them...

Thanks,

5 Jun 2009 1:22 AM

Ron Weiner

Yes there is a way, don't use SELECT * FROM... Instead use SELECT
ThisColumn, ThatColumn, AndTheOtherColumn FROM... Frankly this is what you
should have been doing from the beginning.

Using the Wild Card character in a Select statement is a lazy man's
programming practice that should only be used in the rare case that you
actually need data from every column in the table. Why bring back oodles of
data from columns that you intend to ignore anyway.

Rdub

Show quoteHide quote

"Brian" <Br***@discussions.microsoft.com> wrote in message
news:06AFF03A-B626-4CC6-B939-01EA2486004C@microsoft.com...
>I just started using "Replicated" access databases for my VB6 applications.
> This has created a bit of a problem... I use to be able to run basic
> queries
> with the "*" wildcard character and parse the recordset as needed.
>
> Now, that same query recordset from the replicated database comes back
> with
> several additional fields that are added by Access for replicated DB
> synchronization. So I am forced to specifically request fields in my
> queries.
>
> That isn't too bad except for the times that I want to get generic DB
> structure, ie., Field Names or actual field counts for a specific table.
> There are several instances when this is needed verses using hard coded
> queries.
>
> My question??? is there a way around getting theses Access included
> fields
> in my specific queries? I looked into trying to process the field types
> or
> other field attributes for some of these fields and there wasn't a good
> flag
> that I could use to exclude them...
>
>
> Thanks,
>
>
>

5 Jun 2009 1:41 PM

Jeff Johnson

Show quote Hide quote

"Brian" <Br***@discussions.microsoft.com> wrote in message
news:06AFF03A-B626-4CC6-B939-01EA2486004C@microsoft.com...

>I just started using "Replicated" access databases for my VB6 applications.
> This has created a bit of a problem... I use to be able to run basic
> queries
> with the "*" wildcard character and parse the recordset as needed.
>
> Now, that same query recordset from the replicated database comes back
> with
> several additional fields that are added by Access for replicated DB
> synchronization. So I am forced to specifically request fields in my
> queries.
>
> That isn't too bad except for the times that I want to get generic DB
> structure, ie., Field Names or actual field counts for a specific table.
> There are several instances when this is needed verses using hard coded
> queries.

> My question??? is there a way around getting theses Access included
> fields
> in my specific queries? I looked into trying to process the field types
> or
> other field attributes for some of these fields and there wasn't a good
> flag
> that I could use to exclude them...

Aren't these fields always named exactly the same? If so, just put in extra
code to ignore fields with those names when you are parsing the structure.

What data access method are you using? ADO? DAO? There are ways in each to
get the structure of a table without running a query, although there's
nothing really wrong with the old workhorse of SELECT * FROM MyTable WHERE 1
= 2, except maybe to purists.

5 Jun 2009 4:03 PM

Brian

I'm using ADO... and yes I can ignore the fields as they do look to be the
same for every table. I was just wondering if there was some sort of hidden
attribute associated with those added fields that could be used to exclude
the unwanted fields.

I guess I can just create a function to strip out those unwanted fields...
I know there is a way to avoid getting the "hidden" tables when you query a
database for tables... I assume there is something similar for fields... ie.,
Select * from table where fieldname not like "s_*";

The added fields all appear to start with "s_".

OTher ideas ???

Thanks

Show quoteHide quote

"Jeff Johnson" wrote:

> "Brian" <Br***@discussions.microsoft.com> wrote in message
> news:06AFF03A-B626-4CC6-B939-01EA2486004C@microsoft.com...
>
> >I just started using "Replicated" access databases for my VB6 applications.
> > This has created a bit of a problem... I use to be able to run basic
> > queries
> > with the "*" wildcard character and parse the recordset as needed.
> >
> > Now, that same query recordset from the replicated database comes back
> > with
> > several additional fields that are added by Access for replicated DB
> > synchronization. So I am forced to specifically request fields in my
> > queries.
> >
> > That isn't too bad except for the times that I want to get generic DB
> > structure, ie., Field Names or actual field counts for a specific table.
> > There are several instances when this is needed verses using hard coded
> > queries.
>
> > My question??? is there a way around getting theses Access included
> > fields
> > in my specific queries? I looked into trying to process the field types
> > or
> > other field attributes for some of these fields and there wasn't a good
> > flag
> > that I could use to exclude them...
>
> Aren't these fields always named exactly the same? If so, just put in extra
> code to ignore fields with those names when you are parsing the structure.
>
> What data access method are you using? ADO? DAO? There are ways in each to
> get the structure of a table without running a query, although there's
> nothing really wrong with the old workhorse of SELECT * FROM MyTable WHERE 1
> = 2, except maybe to purists.
>
>
>

5 Jun 2009 6:02 PM

Jeff Johnson

"Brian" <Br***@discussions.microsoft.com> wrote in message
news:89971ACE-1D1A-4FA6-8BE0-D30F9FC2CD67@microsoft.com...

> I'm using ADO... and yes I can ignore the fields as they do look to be
> the
> same for every table. I was just wondering if there was some sort of
> hidden
> attribute associated with those added fields that could be used to exclude
> the unwanted fields.

In ADO you can use the Connection.OpenSchema() method.

If you were using DAO, there's a dbSystemField attribute [description: "The
field stores replication information for replicas; you can't delete this
type of field (Microsoft Jet workspaces only)."]. Let's see if I can find
something similar for ADO....

Hmmm, have a look at adFldRowID and adFldRowVersion.

> I guess I can just create a function to strip out those unwanted fields...
> I know there is a way to avoid getting the "hidden" tables when you query
> a
> database for tables... I assume there is something similar for fields...
> ie.,
> Select * from table where fieldname not like "s_*";

No, you can't do that. You don't have access to metadata like field names in
SQL (at least not in the manner you're trying to use them).