Problems Converting 97 Query into 2003 format

[John Ortt]

Hi All,

I have a database in Access 97 which works fine but our company is
converting from NT4 to XP Pro and in the process changing Office 97 for
Office 2003. I have tried to convert the 97 database into Access 2003 but
the Autoexec macro falls over on the query below. I have tried running it
separayely and the computer just hangs.

I realise there is a lot of code below, but I'm hoping somebody may know
what they are looking for...

Any help would be massively appreciated.

Thanks in Advance,

John
INSERT INTO Vendor_Preforma nce_Policing_Ta ble ( TYPE, IPT, CUSTOMER_2,
MaxOfGR_Temp, [PO CREATE DATE], [ORDER REQUEST], [OR LINE], GR_Date,
Supply_Source, [QTY REQD], [QTY O/S], Proj_No, [PART NUMBER], [PO NO], [PO
LINE], Release_No, BUYER, STATUS, REQDT, AQUISITION, PROM_Date, CONT_Date,
Cost_Stat, Cost_Price, Price_code, NSN, PRI, Sell_PRICE, [Key], FC_DATE,
Keyword )
SELECT MMS_Vendor_Roll .TYPE, MMS_Vendor_Roll .IPT,
MMS_Vendor_Roll .CUSTOMER_2, Max(MMS_Vendor_ Roll.GR_Temp) AS MaxOfGR_Temp,
MMS_Vendor_Roll .[PO CREATE DATE],
IIf([Details_Table]![IPT]="Tornado2",Mid ([Details_Table]![COL_Link],9,10),[M
MS_Vendor_Roll]![ORDER REQUEST]) AS Expr2, MMS_Vendor_Roll .[OR LINE],
IIf([MaxOfGR_Temp]=Date(),"",[MaxOfGR_Temp]) AS GR_Date,
MMS_Vendor_Roll .VENDOR, Sum(MMS_Vendor_ Roll.[QTY REQD]) AS [SumOfQTY REQD],
Sum(MMS_Vendor_ Roll.[QTY O/S]) AS [SumOfQTY O/S],
First(MMS_Vendo r_Roll.Proj_No) AS FirstOfProj_No, MMS_Vendor_Roll .[PART
NUMBER], MMS_Vendor_Roll .[PO NO], MMS_Vendor_Roll .[PO LINE],
Max(MMS_Vendor_ Roll.[OR SCHED]) AS [MaxOfOR SCHED],
First(MMS_Vendo r_Roll.BUYER) AS FirstOfBUYER, Min(MMS_Vendor_ Roll.STATUS) AS
MinOfSTATUS, MMS_Vendor_Roll .[REQD DATE], MMS_Vendor_Roll .AQUISITION,
Max(MMS_Vendor_ Roll.[PROM DATE]) AS [MaxOfPROM DATE], MMS_Vendor_Roll .[CONT
DATE], Max(MMS_Vendor_ Roll.[PRICE TYPE]) AS [MaxOfPRICE TYPE],
Max(MMS_Vendor_ Roll.[TOTAL STG PRICE]) AS [MaxOfTOTAL STG PRICE],
Max(Details_Tab le.Price_code) AS MaxOfPrice_code , Max(Details_Tab le.nsn) AS
MaxOfnsn, Max(Details_Tab le.PRI) AS MaxOfPRI,
Max(IIf(Left([MMS_Vendor_Roll]![Proj_No],3)="39V" Or
Left([MMS_Vendor_Roll]![Proj_No],3)="39N",0,[Details_Table]![Sell_PRICE]))
AS Sell_PRICE, [ORDER REQUEST] & [OR LINE] AS Expr1,
Max(Details_Tab le.FC_DATE) AS MaxOfFC_DATE,
COOP_Purchase_O rders_Table.Key word
FROM (MMS_Vendor_Rol l LEFT JOIN Details_Table ON (MMS_Vendor_Rol l.Link =
Details_Table.M MS_Link) AND (MMS_Vendor_Rol l.Link_Seq = Details_Table.S eq))
LEFT JOIN COOP_Purchase_O rders_Table ON Details_Table.P O_Number =
COOP_Purchase_O rders_Table.PO_ Number
GROUP BY MMS_Vendor_Roll .TYPE, MMS_Vendor_Roll .IPT,
MMS_Vendor_Roll .CUSTOMER_2, MMS_Vendor_Roll .[PO CREATE DATE],
IIf([Details_Table]![IPT]="Tornado2",Mid ([Details_Table]![COL_Link],9,10),[M
MS_Vendor_Roll]![ORDER REQUEST]), MMS_Vendor_Roll .[OR LINE],
MMS_Vendor_Roll .VENDOR, MMS_Vendor_Roll .[PART NUMBER], MMS_Vendor_Roll .[PO
NO], MMS_Vendor_Roll .[PO LINE], MMS_Vendor_Roll .[REQD DATE],
MMS_Vendor_Roll .AQUISITION, MMS_Vendor_Roll .[CONT DATE], [ORDER REQUEST] &
[OR LINE], COOP_Purchase_O rders_Table.Key word
HAVING (((MMS_Vendor_R oll.IPT)<>"civi l"));

[Tim Marshall]

John Ortt wrote:

Hi All,

I have a database in Access 97 which works fine but our company is
converting from NT4 to XP Pro and in the process changing Office 97 for
Office 2003. I have tried to convert the 97 database into Access 2003 but
the Autoexec macro falls over on the query below. I have tried running it
separayely and the computer just hangs.

I realise there is a lot of code below, but I'm hoping somebody may know
what they are looking for...

I don't see any code at all, just an SQL statement.

Are you sure this is what the conversion fails on?

Did you compile the mdb in A97 first? Usually this will tell you were
problems may be in code (real code, not SQL statements) that can be
corrected.

I haven't used macros in many years, but can you remove the line that
has this query and successfully convert then? If so, have you tried
simply constructing the query right from scratch in A2003?
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[John Ortt]

Sorry Tim,

I didn't explain correctly. The Autoexec is a macro and it simply
references the SQL query I had attached.

I tried running the query on it's own in both 97 and 2003 and the 2003
version hangs while the 97 one works (albeit fairly slowly).

I did try creating the entire query again but it didn't work.

I guess I will just have to try deleting sections of the query to try to
narrow down the failure point.

Thanks anyway Tim.

"Tim Marshall" <TI****@PurpleP andaChasers.Moe rtherium> wrote in message
news:cu******** **@coranto.ucs. mun.ca...

John Ortt wrote:

Hi All,

I have a database in Access 97 which works fine but our company is
converting from NT4 to XP Pro and in the process changing Office 97 for
Office 2003. I have tried to convert the 97 database into Access 2003 but the Autoexec macro falls over on the query below. I have tried running it separayely and the computer just hangs.

I realise there is a lot of code below, but I'm hoping somebody may know
what they are looking for...

I don't see any code at all, just an SQL statement.

Are you sure this is what the conversion fails on?

Did you compile the mdb in A97 first? Usually this will tell you were
problems may be in code (real code, not SQL statements) that can be
corrected.

I haven't used macros in many years, but can you remove the line that
has this query and successfully convert then? If so, have you tried
simply constructing the query right from scratch in A2003?
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me