By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,667 Members | 2,611 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,667 IT Pros & Developers. It's quick & easy.

Problems Converting 97 Query into 2003 format

P: n/a
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_Preformance_Policing_Table ( 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_Vendor_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_Vendor_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_Table.Price_code) AS MaxOfPrice_code, Max(Details_Table.nsn) AS
MaxOfnsn, Max(Details_Table.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_Table.FC_DATE) AS MaxOfFC_DATE,
COOP_Purchase_Orders_Table.Keyword
FROM (MMS_Vendor_Roll LEFT JOIN Details_Table ON (MMS_Vendor_Roll.Link =
Details_Table.MMS_Link) AND (MMS_Vendor_Roll.Link_Seq = Details_Table.Seq))
LEFT JOIN COOP_Purchase_Orders_Table ON Details_Table.PO_Number =
COOP_Purchase_Orders_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_Orders_Table.Keyword
HAVING (((MMS_Vendor_Roll.IPT)<>"civil"));

Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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
Nov 13 '05 #2

P: n/a
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****@PurplePandaChasers.Moertherium> 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

Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.