Re: ADO data control

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: complex filter and calculations in access
    ... switch to SQL view. ... query by switching to datasheet view, ... of your database using the from address in this post. ... pre-op infections yes/no ...
    (microsoft.public.access.queries)
  • Re: A little more meat this week
    ... implementation with a query processor capable of returning a result set ... then we can call it a result bag (SQL ... I'm looking at the data model and not database tools at this ... for a s/w developer and a s/w developer simplifies for the end user). ...
    (comp.databases.pick)
  • Re: complex filter and calculations in access
    ... when using a subquery on the same table as the main query, ... switch to SQL view. ... of your database using the from address in this post. ...
    (microsoft.public.access.queries)
  • Re: A little more meat this week
    ... said data includes lists." ... implementation with a query processor capable of returning a result set ... then we can call it a result bag (SQL ... I'm looking at the data model and not database tools at this ...
    (comp.databases.pick)
  • Re: DBMS and lisp, etc.
    ... Naively implemented with SQL, again for 10 ... (1 query for the initial orders, 1 query for each order for its ... soon as you upgrade to the SQL database. ... (eq (order-customer orderA) ...
    (comp.lang.lisp)
Loading