Query pulls different results when in VB/Access???

[Paladyr]

Okay, I have a simple query where all I do is select multiple fields from multiple tables and after the WHERE, I basically just say that the primary keys (a unique number) must be equal in each table. When I run this query in access, it works fine, when I run it using ADO, it returns duplicate records and also doesn't even pull the correct data for each record???? What could be wrong here??? I am exporting to excel and am looping through the fields and sending them to cells, then when I am done with a record i move next. Thanks in advance if anyone knows what could be wrong???

 

[Paladyr]

I changed my code from ADO to DAO, and also instead of opening the recordset using a query as an arguement, I am opening the recordset by opening the query that is inside access. It returns the correct records now but can anyone explain why it doesn't work using ADO??? Also, this is VBA code not VB, does that make a difference?

 

[UnkUser]

Without seeing the SQL statement that you use, it is difficult to guess what might be the problem. Did you remember to add a semi-colon (";&quot at the end of the statement?

 

[Paladyr]

It's huge, and like I said, it does return results, they are just not accurate, so the semicolon wouldn't be the problem . You asked, and here it is, also, this database is poorly design (and not by me I might add!!):

strSQL = "SELECT CUSTOMERS.Complete, CUSTOMERS.[HOSPITAL NUMBER] AS [Hosp#], CUSTOMERS.[HOSPITAL NAME] AS [Hosp Name], CUSTOMERS.YEAR, CUSTOMERS.CITY, CUSTOMERS.STATE, CUSTOMERS.[CONTRACT GROUP] AS Contract, CUSTOMERS.Specialist AS LMC, CUSTOMERS.[SMG Lic Beds] AS [Lic Beds], CUSTOMERS.[occupied beds] AS [Occ Beds], CUSTOMERS.[SALES #] AS HTC, CUSTOMERS.Region, CUSTOMERS.[hospital type code] AS [Hosp Type Code], CUSTOMERS.[Laundry Type], CUSTOMERS.[average daily census] AS [Avg Daily Census], [NORMS QUERY2].pounds, [NORMS QUERY2].[Average Length of Patient Stay] AS [Avg Length of Stay]"
  strSQL = strSQL + ", [NORMS QUERY2].[CLEAN/SOILED LBS] AS [Clean or Soiled Code], [NORMS QUERY2].pounds, [NORMS QUERY2].[PATIENT DAYS] AS [Pat Days], [NORMS QUERY2].[pounds per PD] AS [Pounds Per PD], [NORMS QUERY2].[adjusted patient days] AS APD, [NORMS QUERY2].[pounds per APD] AS [Pounds Per APD], [NORMS QUERY2].[Production Labor] AS [Inhouse Production Labor], [NORMS QUERY2].[Production FTEs], [NORMS QUERY2].[Distribution Labor] AS [Inhouse Distribution Labor], [NORMS QUERY2].[Distribution FTEs], [NORMS QUERY2].Utilities AS [Inhouse Utilities], [NORMS QUERY2].[Chemicals, Supplies] AS [Inhouse Chemicals, Supplies], [NORMS QUERY2].[other direct] AS [Inhouse Other Direct], [NORMS QUERY2].[OUTSIDE LAUNDRY COST], [NORMS QUERY2].[Tot Laundry Exp],"
  strSQL = strSQL + " [NORMS QUERY2].[LINEN ACQUISITION], [NORMS QUERY2].[Tot Laundry/Linen Cost], [NORMS QUERY2].[Pounds/Prod FTE], [NORMS QUERY2].[Pounds/Dist FTE], [NORMS QUERY2].[OR Linen], [NORMS QUERY2].[Laundry Cost/Pound], [NORMS QUERY2].[Linen Cost/Pound], [NORMS QUERY2].[Dist Labor/Pound], [NORMS QUERY2].[Total Cost/Pound], [NORMS QUERY2].[Laundry Cost/PD], [NORMS QUERY2].[Linen Cost/PD], [NORMS QUERY2].[Dist Labor/PD], [NORMS QUERY2].[Total Cost/PD], [NORMS QUERY2].[Laundry Cost/APD], [NORMS QUERY2].[Linen Cost/APD], [NORMS QUERY2].[Dist Labor/APD], [NORMS QUERY2].[Total Cost/APD], [NORMS QUERY USAGE].[BATH BLANKET], "
  strSQL = strSQL + "[NORMS QUERY USAGE].[2-BATH BLANKET], [NORMS QUERY USAGE].[3-bath blanket], [NORMS QUERY USAGE].[BATH TOWEL], [NORMS QUERY USAGE].[2-BATH TOWEL], [NORMS QUERY USAGE].[3-bath towel], [NORMS QUERY USAGE].DRAWSHEET, [NORMS QUERY USAGE].[2-DRAWSHEET], [NORMS QUERY USAGE].[3-drawsheet], [NORMS QUERY USAGE].[PATIENT GOWN], [NORMS QUERY USAGE].[2-PATIENT GOWN], [NORMS QUERY USAGE].[3-patient gown], [NORMS QUERY USAGE].[PRECAUTION GOWN], [NORMS QUERY USAGE].[2-PRECAUTION GOWN], [NORMS QUERY USAGE].[3-precaution gown], [NORMS QUERY USAGE].[HAMPER BAG], "
  strSQL = strSQL + "[NORMS QUERY USAGE].[2-HAMPER BAG], [NORMS QUERY USAGE].[3-hamper bag], [NORMS QUERY USAGE].[MATTRESS PAD], [NORMS QUERY USAGE].[2-MATTRESS PAD], [NORMS QUERY USAGE].[3-mattress pad], [NORMS QUERY USAGE].PILLOWCASE, [NORMS QUERY USAGE].[2-PILLOWCASE], [NORMS QUERY USAGE].[3-pillowcase], [NORMS QUERY USAGE].[PJ PANTS], [NORMS QUERY USAGE].[2-PJ PANTS], [NORMS QUERY USAGE].[3-pj pants], [NORMS QUERY USAGE].[BATH ROBE], [NORMS QUERY USAGE].[2-BATH ROBE], [NORMS QUERY USAGE].[3-bath robe], [NORMS QUERY USAGE].[FITTED SHEET], [NORMS QUERY USAGE].[2-FITTED SHEET], [NORMS QUERY USAGE].[3-fitted sheet], [NORMS QUERY USAGE].[FLAT SHEET], [NORMS QUERY USAGE].[2-FLAT SHEET], [NORMS QUERY USAGE].[3-flat sheet], "
  strSQL = strSQL + "[NORMS QUERY USAGE].[SPREAD/BLANKET], [NORMS QUERY USAGE].[2-SPREAD/BLANKET], [NORMS QUERY USAGE].[3-SPREAD/BLANKET], [NORMS QUERY USAGE].UNDERPAD, [NORMS QUERY USAGE].[2-UNDERPAD], [NORMS QUERY USAGE].[3-underpad], [NORMS QUERY USAGE].WASHCLOTH, [NORMS QUERY USAGE].[2-WASHCLOTH], [NORMS QUERY USAGE].[3-washcloth], [CUSTOMERS REPLACEMENT DATA].[LBS PER PATIENT DAY], [CUSTOMERS REPLACEMENT DATA].[BATH BLANKET], [CUSTOMERS REPLACEMENT DATA].[BATH TOWEL], [CUSTOMERS REPLACEMENT DATA].DRAWSHEET, [CUSTOMERS REPLACEMENT DATA].[PATIENT GOWN], [CUSTOMERS REPLACEMENT DATA].[PRECAUTION GOWN], [CUSTOMERS REPLACEMENT DATA].[HAMPER BAG], [CUSTOMERS REPLACEMENT DATA].[MATTRESS PAD], [CUSTOMERS REPLACEMENT DATA].PILLOWCASE, "
  strSQL = strSQL + "[CUSTOMERS REPLACEMENT DATA].[PJ PANTS], [CUSTOMERS REPLACEMENT DATA].[BATH ROBE], [CUSTOMERS REPLACEMENT DATA].[FITTED SHEET], [CUSTOMERS REPLACEMENT DATA].[FLAT SHEET], [CUSTOMERS REPLACEMENT DATA].[SPREAD/BLANKET], [CUSTOMERS REPLACEMENT DATA].UNDERPAD, [CUSTOMERS REPLACEMENT DATA].WASHCLOTH"
  strSQL = strSQL + " FROM ((CUSTOMERS INNER JOIN [CUSTOMERS REPLACEMENT DATA] ON CUSTOMERS.[HOSPITAL NUMBER] = [CUSTOMERS REPLACEMENT DATA].[HOSPITAL NUMBER]) INNER JOIN [NORMS QUERY2] ON CUSTOMERS.[HOSPITAL NUMBER] = [NORMS QUERY2].[HOSPITAL NUMBER]) INNER JOIN [NORMS QUERY USAGE] ON CUSTOMERS.[HOSPITAL NUMBER] = [NORMS QUERY USAGE].[HOSPITAL NUMBER] WHERE (((CUSTOMERS.Complete)='x'));"
[\CODE]

 

[WilMead]

How does the VB SQL compare to the ACCESS generated SQL?

Wil Mead
wmead@optonline.net

 

[Paladyr]

Exactly the same. I don't use Access' auto-generated SQL. The results are entirely different. Access = correct results, VB(SQL) = Wrong numbers in wrong records, duplicate records, etc..

 

[TimLarkin]

just a wild guess (without in-depth study) , but I'd say the # signs are what is giving you the problem. # is a reserved character. Tim

Remember the KISS principle:
Keep It Simple, Stupid!

 

[Paladyr]

Even though it is inside brackets??? Anyone know if it could??? I'll do some testing if I get time today. Any other suggestions?

 

[UnkUser]

Wow! You are right. It is quite lengthy. That in itself may be a problem for ADO. Most SQL engines have a maximum number of characters in a query. I don't know what it is for ADO, but it is worth checking. Other than that, I did not notice any obvious errors in the SQL statement.

I noticed that you have two tables (CUSTOMERS and [CUSTOMERS REPLACEMENT DATA]) and two queries ([NORMS QUERY2] and [NORMS QUERY USAGE]) that are used in this query. If these two queries also reference queries or if they too are quite lengthy, this also may pose a problem for the ADO SQL engine.

If you are referencing almost every field in a table (or query), it is usually to your advantage to use the "*" in the SELECT statement (e.g. CUSTOMERS.*, [CUSTOMERS REPLACEMENT DATA].*). Although you get a few more fields of data, it simplifies the query considerably. Also unless there is an absolute need for using an alias for a field, it is generally not a good practice. Fields are easily distinguished by table (or query) name with the field name.

Tim's comment about KISS is very true. The simpler the better. This is especially true for anyone who follows you in supporting the software.

 

[siqual]

Another thing you migh consider is if the query runs fine in the database, can you parameterize it? If so then it simplifies things enormously and also speeds up the execution. For example:

Dim cmdQuery As ADODB.Command
Dim rstInfo As ADODB.Recordset
Dim prmQuery As ADODB.Parameter

Set cmdQuery = New ADODB.Command
With cmdQuery
.ActiveConnection = connectionObjectToYourDB
.CommandText = "YourQueryName"
.CommandType = adCmdStoredProc
Set prmQuery = .CreateParameter("ParameterName&quot
With prmQuery
.Direction = adParamInput
.Type = adInteger
.Value = ValueOfParameter
End With
.Parameters.Append prmQuery
Set prmQuery = .CreateParameter("NextParameter&quot
With prmQuery
.Direction = adParamInput
.Type = adDate
.Value = MinDate
End With
.Parameters.Append prmQuery
End With

Set rstInfo = New ADODB.Recordset
With rstInfo
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Open cmdQuery
.
.
.
.
End With

Set cmdQuery = Nothing
Set prmQuery = Nothing
Set rstInfo = Nothing
Dan Grogan
dan@siqual.com

"Absit prudentia nil rei publicae profitur."
Without common sense you ain't gonna have nothing.