Re: ADO data control

Wow, thanks for the great advice! I'll definitely check in on those books
you recommended.

I do follow a number of Access newsgroups (as well as a bunch of VB ones),
it's been help like yours that has gotten me to where I am today.

Thanks again!

Darhl
"Earl" <brikshoe@xxxxxxxxxxxxxxxxx> wrote in message
news:u8OVnSyUGHA.6048@xxxxxxxxxxxxxxxxxxxxxxx
I do a lot of work with SQL Server, and it's been awhile since I wrote
anything for Access or for JET, but I do recall a few keywords would not
work in VB because they were distinct to the Access environment. This may
also hold true for VBA. Another way to test your query would be to try
joining some of the tables instead of all.

On the normalization topic, the idea is simply to reduce inaccuracies and
duplicated information. The process itself is more precise. But if you
consider a table to represent an entity (or "object"), a row as an
instance of that object, and the columns as attributes of that object,
then you can begin to see your structure better. While it's possible for
an entity to have many attributes, my experience has been that if I look
at a table and see much more than 20-30 attributes (columns), I
instinctively think that the database is probably not normalized. When I
see multiple columns prefaced by the same prefix, then it's also likely
the tables represent multiple entities. The hardest concept for me to
accept was that in a given database, no column name should ever be the
same. Indeed, it almost always should not even be similar (e.g., columns
of HomePhone, CellPhone, Fax should often be represented by a table of
Phones or PhoneNumbers, with attributes of PhoneNumber and PhoneType --
possibly keyed to a table of PhoneTypes).

But I cannot tell you whether your structure is correct. You can only know
whether your database is "normalized" properly by working through the
process of First Normal, Second Normal, etc. There are plenty of books
written on the topic of normalization, and one you might like is "Database
Design for Mere Mortals" by Michael Hernandez. It's a language-agnostic
approach that is a good start. Another book I liked was SQL Server
Database Design by Louis Davidson. Even if you are not using SQL Server,
there is much in there on database design that applies to any project.

On the table naming issue, I name my tables using the plural form of noun,
"Contacts", "Addresses", "Owners", "PhoneTypes", etc. (probably an equal
number of developers and SQL types would say that you should name your
tables singular). Consider strongly the names for your tables -- they can
tell you at a glance when you are writing a query whether they contain the
columns you need. Lets think about "StoreData". On its surface, that
sounds like a pretty good name if it contains only the data about stores.
I would probably call it "Stores" or "StoreHistory", but that's just a
preference, and thus the beauty of writing it yourself -- you gotta live
with it. Within my "Stores", I would have all of the attributes of the
Store as columns, including an FK to the Addresses table, an FK to the
Phones table, and an FK to the Owners table (for example). Within the
"Owners" table, I would have an FK to the Addresses table, to the Phones
table, etc.

Now the schizophrenic side of all this: It IS easier to not normalize
Addresses, Phones, Contacts, etc.! Easier for reporting, easier to
mentally keep all the data in view, easier to not have to do all those
joins. However, as an example, as yourself what will happen when you have
to add another phone type? Do you add another column? What if only one of
your stores uses a TollFree number column? You end up with a wider table
and a bunch of nulls for your effort.

I think by this time, you might agree that if you are writing a query,
it's pretty obvious that you are pulling data from tables, and joining on
tables, so quickly the "tbl" becomes a nuisance to both read and write.
But perhaps, as you say, that is "an Access thing".

Incidentally, you might get some fresh ideas in the Access forums or the
microsoft.public.vb.database forum.

"Darhl Thomason" <darhlt@xxxxxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:%23CCC7brUGHA.5660@xxxxxxxxxxxxxxxxxxxxxxx
It's an Access db, and I used the Access query designer to build the SQL
string.

My WHERE and ORDER BY are build with code. I use this with no problems
in my Access DB, I built the same basic app with VBA, but I'm converting
it to VB now.

When I have my SQL statement trimmed down to:
strSQL = "SELECT tblStoreData.*, tblOwners.* " & _
"FROM tblOwners RIGHT JOIN tblStoreData ON tblOwners.OwnerID =
tblStoreData.OwnerID"

Then the SQL statement and the existing WHERE and ORDER BY calculations
work correctly.

Also, please say more about "normalizing" my database. When I originally
built the database, I thought it was normalized. My main table is
tblStoreData, and along with store specific data, it also has as fields
(for example) RegionID, DistrictID, FCID, DMAID, OwnerID, etc. Each of
these has it's own table, example tblRegion has RegionName and RegionID,
where tblRegion.RegionID is related back to tblStoreData.RegionID. The
rest of the ancillary forms follow the same basic structure.

Also, I thought it was good form to include descriptors in my table
names, etc. Or is that just an Access thing? I also don't understand
about aliases for the table names.

All of my code (up to this point, it's still in the development stage,
can be found on my site at www.dnjhome.com/vb.txt.

Thanks for your ideas and help!

Darhl


"Earl" <brikshoe@xxxxxxxxxxxxxxxxx> wrote in message
news:%23DiC1BlUGHA.4952@xxxxxxxxxxxxxxxxxxxxxxx
You don't say if you are using SQL Server, but if so, run your query in
the Query Analyzer to find your problem. With a bit more work, you can
also reconstruct the query in Access if you are using a JET database.
Also, you didn't show your strings for the WHERE and ORDER BY clauses,
so that may also be the issue. If you have control over the database,
you should consider normalizing, dropping the "tbl" prefix, and using
aliases for your table names in the query. You will not only improve the
performance, but also make the queries much easier to read.

"Darhl Thomason" <darhlt@xxxxxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:u5MdwyUUGHA.4792@xxxxxxxxxxxxxxxxxxxxxxx
I'm having problems with my ADO data control. When I pass a very
simple SQL statement to it, it works fine. But I have a db I need to
open with multiple tables that has relationships that need to be
created. So, my SQL statement is built with multiple INNER JOIN's.
Note that strWhere and strOrder are defined in different subs. The
error I get is "No value given for one or more required Parameters."
The line that is highlighted when I debug it is:

cnPOSData.Refresh

Here is my SQL:

cnPOSData.RecordSource = "SELECT tblStoreData.PmiNumber,
tblStoreData.Store, tblStoreData.Address, tblStoreData.City, " & _
"tblStoreData.Zip, tblStoreData.InstallDate,
tblStoreData.InstallDateChanged, tblStoreData.OpenDate, " & _
"tblStoreData.OpenDateChanged, tblStoreData.StoreOpen,
tblStoreData.NewStore, tblStoreData.FirstContactDate, " & _
"tblStoreData.PackageSentDate, tblStoreData.Phone,
tblStoreData.PO, tblStoreData.Processor, tblStoreData.MerchNum, " & _
"tblStoreData.EFSNetLogin, tblStoreData.EFSNetPW,
tblStoreData.EFSNetID, tblStoreData.EFSNetKey, " & _
"tblStoreData.PCChargeSerial,
tblStoreData.PCChargeSecurityCode, tblStoreData.PCChargeSystemCode,
tblStoreData.DSL, " & _
"tblStoreData.Static_IP, tblStoreData.POC,
tblStoreData.Comments, tblStoreData.NAFEligible,
tblStoreData.NAFRefund, " & _
"tblStoreData.FASigned, tblStoreData.FAExpires,
tblCheckListStatus.WeekName, tblStatus.StatusName, tblDMA.DMAName, " &
_
"tblType.TypeName, tblFC.FCEmail, tblFC.FCName,
tblOwners.OwnerName, tblOwners.OwnerContact, tblOwners.OwnerPhone, " &
_
"tblOwners.OwnerEmail, tblDistrict.DistrictName,
tblDistrict.DomName, tblDistrict.DomEmail, tblRegion.RegionName, " & _
"tblStoreData.DistrictID, tblStoreData.DMAID,
tblStoreData.FCID, tblStoreData.RegionID, tblStoreData.OwnerID, " & _
"tblStoreData.StatusID, tblStoreData.TypeID,
tblStoreData.WeekID " & _
"FROM tblType INNER JOIN (tblCheckListStatus INNER JOIN
(tblStatus INNER JOIN (tblDMA INNER JOIN (tblFC " & _
"INNER JOIN (tblDistrict INNER JOIN (tblOwners INNER JOIN
(tblRegion INNER JOIN tblStoreData " & _
"ON tblRegion.RegionID = tblStoreData.RegionID) ON
tblOwners.OwnerID = tblStoreData.OwnerID) " & _
"ON tblDistrict.DistrictID = tblStoreData.DistrictID) ON
tblFC.FCID = tblStoreData.FCID) " & _
"ON tblDMA.DMAID = tblStoreData.DMAID) ON tblStatus.StatusID
= tblStoreData.StatusID) " & _
"ON tblCheckListStatus.WeekID = tblStoreData.WeekID) ON
tblType.TypeID = tblStoreData.TypeID " & _
"WHERE " & strWhere & " ORDER BY " & strOrder

Any ideas where I'm going wrong?

Thanks,

Darhl









.

Relevant Pages

  • Re: Design View
    ... Can we assume that you put your database is a "trusted" location? ... Try the query again. ... it should print the SQL statement of the query into ... The same query opens fine in design view in Access 2000. ...
    (microsoft.public.access.queries)
  • Re: Design View
    ... Someone else may be able to provide a suggestion. ... I also checked references and they seemed fine for that database, ... Okay, Bob, the query contains the reserved word DATE as a field name. ... you may be able to get the SQL statement in A2007 opening the ...
    (microsoft.public.access.queries)
  • Re: Involved query / possible over Access assistance.
    ... and that it related to normalization. ... >> This is the idea I want to try to implement in an access database. ... I want to store the dayof the week that the event ... >> I want to then do a query for a given week. ...
    (microsoft.public.access.queries)
  • Re: Design View
    ... If it would be any help, I could e-mail you the database. ... Switch the problem query to SQL View, ... you may be able to get the SQL statement in A2007 opening the ... The same query opens fine in design view in Access 2000. ...
    (microsoft.public.access.queries)
  • Re: Design View
    ... I also checked references and they seemed fine for that database, ... Okay, Bob, the query contains the reserved word DATE as a field name. ... you may be able to get the SQL statement in A2007 opening the ... The same query opens fine in design view in Access 2000. ...
    (microsoft.public.access.queries)