New Query Def works in '97, not in XP

[crpjaviman]

Hello everyone,

I have a unique problem with my code in VBA in Access. I have created a database with a mod in Access '97 and it works perfectly. Here is a brief on the process:
1. Delete all tables
2. Import certain tables from a directory
3. Create crosstab and make-table queries thru dynamic code, the make-table combines 3 crosstabs into one table
4. Export to Excel spreadsheets for further manipulation.

I am in the process of making a copy of the database in XP. I was certain that by copying the code from the '97 mod to an XP mod should work, with some verbage problems that may need to be corrected.

Here is my problem: Once the code is put into the XP mod, steps 1 thru 3 run just fine until it tries to open the crosstabs. The crosstabs are created correctly except they do not have results to show. The code that I have used is below:

sqla = “TRANSFORM Sum(CPA_NOV_OPEN.cpa_weekly_avg) AS [The Value]
SELECT CPA_NOV_OPEN.Account_Age, CPA_NOV_OPEN.market, CPA_NOV_OPEN.stat, Sum(CPA_NOV_OPEN.total_calls) AS [Total Of total_calls]
FROM CPA_NOV_OPEN
WHERE (((CPA_NOV_OPEN.market) Like "New Middle*") AND ((CPA_NOV_OPEN.stat)="OA"))
GROUP BY CPA_NOV_OPEN.Account_Age, CPA_NOV_OPEN.market, CPA_NOV_OPEN.stat
ORDER BY CPA_NOV_OPEN.Account_Age
PIVOT CPA_NOV_OPEN.queue;”

strqueryname = "CPA_NOV_OPEN_Crosstab_cpa_Avg"
fexisttable1 = False
dbs.QueryDefs.Refresh
For i = 0 To dbs.QueryDefs.Count - 1
If strqueryname = dbs.QueryDefs(i).Name Then
fexisttable1 = True
DoCmd.DeleteObject acQuery, strqueryname
Exit For
End If
Next i

‘Used first
Set qdfTRANSFORM = dbs.CreateQueryDef "CPA_NOV_OPEN_Crosstab_cpa_Avg", sqla)

‘Then converted to below code to test methods

Set qdfTRANSFORM = New DAO.QueryDef
qdfTRANSFORM.Name = "CPA_NOV_OPEN_Crosstab_cpa_Avg"
qdfTRANSFORM.SQL = sqla
dbs.QueryDefs.Append qdfTRANSFORM

Using the same queries that were created, I delete the criteria, run for results, results are shown correctly. I then type in the exact same criteria, run for results, results are shown correctly.

I am dumb-founded at this time. Has anyone run into this kind of problem?

Let me know what you think.


Thanks,
crpjaviman

 

[CajunCenturion]

You may be running into an ADO vs DAO issue. With A97, DAO was the internal DB engine, but I believe, that with XP, ADO is the default engine. You may have to include references to the DAO objects in order to use them, and code accordingly.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein