G
Guest
Hi Guys
I need some help. My export of a query as a comma delimited text file worked
fine until i modified the query yesterday. Now I get "Run-time Error 3011,
Microsoft Jet database engine could not find the object
<filename>......................."
Why has chaging the query caused this error. If I put the old query back it
works fine? How do I fix it. I burned the midnight oil (and then some) but to
no avail.
Database is a single entity, Access 2000 file format running on Access 2003,
not split yet because I am still commissioning it.
The code which calls the query
DoCmd.TransferText acExportDelim, "samout_filespec", "q_SamUpload",
[Forms]![f_FlatFileOut]![filename2], False
The old query
SELECT t_MainData.ProductionSubsidiaryCode AS [Company Code],
t_MainData.ASC_Code AS [Repair Centre Code], t_MainData.Amk_ID AS [Claim
Number], t_MainData.RepairType AS [Service type], t_MainData.DefectType, ""
AS Authorisation_No, t_Customers.Name AS [Consumer Name],
t_Customers.Address1, t_Customers.Address2, t_Customers.TownCity AS Address3,
t_Customers.PostCode, t_Customers.Telephone1 AS [Consumer Tel],
t_Customers.Fax, t_MainData.RunningModel AS [Model name],
t_MainData.ProductSerialNo AS [Serial number], t_MainData.CRT_LCD_SerialNo AS
[CRT Serial No], t_MainData.CRT_LCD_Maker AS [CRT Manufacturer Name],
t_MainData.ConditionCode1 AS [IRIS Condition Code], t_MainData.DefectCode1 AS
[IRIS Defect Code], t_MainData.SymptomCode1 AS [IRIS Symptomn Code],
t_MainData.RepairCode1 AS [IRIS Repair Code], "" AS IRIS_Flag, "" AS
IRIS_Section, "" AS Accessory, Year([PurchaseDate]) & Right(("0" &
Month([PurchaseDate])),2) & Right(("0" & Day([PurchaseDate])),2) AS [Purchase
Date], Year([RequestedDate]) & Right(("0" & Month([RequestedDate])),2) &
Right(("0" & Day([RequestedDate])),2) AS [SVC Request Date],
Year([UnitDespatchDate]) & Right(("0" & Month([UnitDespatchDate])),2) &
Right(("0" & Day([UnitDespatchDate])),2) AS [SVC Complete Date],
Year([FG_Date]) & Right(("0" & Month([FG_Date])),2) & Right(("0" &
Day([FG_Date])),2) AS [Delivery date], t_MainData.Dealer_Name AS [Dealer
name/Code], "" AS Distance, "" AS [Delivery Cost], "" AS [Parts Amount], ""
AS [Other Cost], "" AS [Other Amount Description],
Left$([DetailSymptomDesc],50) AS [Defect Description],
t_MainData.DetailRepairDesc AS [Repair Description], t_MainData.Remark, "" AS
invoicedate, "" AS invoicenumber, t_MainData.TransactionNo AS [Contact Centre
Call Number], "O" AS CallType, "" AS PrintedPages, t_MainData.Parts1Code1 AS
[Part Number], t_MainData.Location1No1 AS Location, IIf((Len([Part
Number])>0),1,"") AS [Used Quantity], "" AS [Parts Amount 1], "" AS [Document
Number 1], t_MainData.Parts1Serial1 AS [Part Serial Number 1],
t_MainData.Parts2Code1 AS [Part Number 2], t_MainData.Location2No1 AS
[Location 2], IIf((Len([Part Number 2])>0),1,"") AS [Used Quantity 2], "" AS
[Parts Amount 2], "" AS [Document Number 2], t_MainData.Parts1Serial2 AS
[Part Serial Number 2]
FROM t_Customers INNER JOIN t_MainData ON t_Customers.CustomerNo =
t_MainData.CustomerNo
WHERE (((t_MainData.DefectType)<>"BS") AND ((t_MainData.Uploaded)=No) AND
((Len([FG_date]))>6) AND ((t_MainData.Manufacturer)="Samsung"));
The new query
SELECT t_MainData.ProductionSubsidiaryCode AS [Company Code],
t_MainData.ASC_Code AS [Repair Centre Code], t_MainData.Amk_ID AS [Claim
Number], t_MainData.RepairType AS [Service type], t_MainData.DefectType, ""
AS Authorisation_No, t_Customers.Name AS [Consumer Name],
t_Customers.Address1, t_Customers.Address2, t_Customers.TownCity AS Address3,
t_Customers.PostCode, t_Customers.Telephone1 AS [Consumer Tel],
t_Customers.Fax, t_MainData.RunningModel AS [Model name],
t_MainData.ProductSerialNo AS [Serial number], t_MainData.CRT_LCD_SerialNo AS
[CRT Serial No], t_MainData.CRT_LCD_Maker AS [CRT Manufacturer Name],
t_MainData.ConditionCode1 AS [IRIS Condition Code], t_MainData.DefectCode1 AS
[IRIS Defect Code], t_MainData.SymptomCode1 AS [IRIS Symptomn Code],
t_MainData.RepairCode1 AS [IRIS Repair Code], "" AS IRIS_Flag, "" AS
IRIS_Section, "" AS Accessory, Year([PurchaseDate]) & Right(("0" &
Month([PurchaseDate])),2) & Right(("0" & Day([PurchaseDate])),2) AS [Purchase
Date], Year([RequestedDate]) & Right(("0" & Month([RequestedDate])),2) &
Right(("0" & Day([RequestedDate])),2) AS [SVC Request Date],
Year([UnitDespatchDate]) & Right(("0" & Month([UnitDespatchDate])),2) &
Right(("0" & Day([UnitDespatchDate])),2) AS [SVC Complete Date],
Year([FG_Date]) & Right(("0" & Month([FG_Date])),2) & Right(("0" &
Day([FG_Date])),2) AS [Delivery date], t_MainData.Dealer_Name AS [Dealer
name/Code], "" AS Distance, "" AS [Delivery Cost], "" AS [Parts Amount], ""
AS [Other Cost], "" AS [Other Amount Description],
IIf(Len([DetailSymptomDesc])>49,Left$([DetailSymptomDesc],49),[DetailSymptomDesc])
AS [Defect Description],
IIf(Len([DetailRepairDesc])>49,Left$([DetailRepairDesc],49),[DetailRepairDesc])
AS [Repair Description], IIf(Len([Remark])>64,Left$([Remark],64),[Remark]) AS
Remarks, "" AS invoicedate, "" AS invoicenumber, t_MainData.TransactionNo AS
[Contact Centre Call Number], "O" AS CallType, "" AS PrintedPages,
t_MainData.Parts1Code1 AS [Part Number], t_MainData.Location1No1 AS Location,
IIf((Len([Part Number])>0),1,"") AS [Used Quantity], "" AS [Parts Amount 1],
"" AS [Document Number 1], t_MainData.Parts1Serial1 AS [Part Serial Number
1], t_MainData.Parts2Code1 AS [Part Number 2], t_MainData.Location2No1 AS
[Location 2], IIf((Len([Part Number 2])>0),1,"") AS [Used Quantity 2], "" AS
[Parts Amount 2], "" AS [Document Number 2], t_MainData.Parts1Serial2 AS
[Part Serial Number 2]
FROM t_Customers INNER JOIN t_MainData ON t_Customers.CustomerNo =
t_MainData.CustomerNo
WHERE (((t_MainData.DefectType)<>"BS") AND ((t_MainData.Uploaded)=No) AND
((Len([FG_date]))>6) AND ((t_MainData.Manufacturer)="SMG"));
In the middle of the query i needed to do some formatting so I changed this
extract
Left$([DetailSymptomDesc],50) AS [Defect Description],
t_MainData.DetailRepairDesc AS [Repair Description], t_MainData.Remark
to
IIf(Len([DetailSymptomDesc])>49,Left$([DetailSymptomDesc],49),[DetailSymptomDesc])
AS [Defect Description],
IIf(Len([DetailRepairDesc])>49,Left$([DetailRepairDesc],49),[DetailRepairDesc])
AS [Repair Description], IIf(Len([Remark])>64,Left$([Remark],64),[Remark]) AS
Remarks
If I change this extract back then it works again - this is a real
headbuster for me, I'm a bit of a newbie at the database game. I hope one of
you guys can save me on this one, as my boss is really busting my head about
not being able to make the customer upload yesterday
Thanks
Alec
I need some help. My export of a query as a comma delimited text file worked
fine until i modified the query yesterday. Now I get "Run-time Error 3011,
Microsoft Jet database engine could not find the object
<filename>......................."
Why has chaging the query caused this error. If I put the old query back it
works fine? How do I fix it. I burned the midnight oil (and then some) but to
no avail.
Database is a single entity, Access 2000 file format running on Access 2003,
not split yet because I am still commissioning it.
The code which calls the query
DoCmd.TransferText acExportDelim, "samout_filespec", "q_SamUpload",
[Forms]![f_FlatFileOut]![filename2], False
The old query
SELECT t_MainData.ProductionSubsidiaryCode AS [Company Code],
t_MainData.ASC_Code AS [Repair Centre Code], t_MainData.Amk_ID AS [Claim
Number], t_MainData.RepairType AS [Service type], t_MainData.DefectType, ""
AS Authorisation_No, t_Customers.Name AS [Consumer Name],
t_Customers.Address1, t_Customers.Address2, t_Customers.TownCity AS Address3,
t_Customers.PostCode, t_Customers.Telephone1 AS [Consumer Tel],
t_Customers.Fax, t_MainData.RunningModel AS [Model name],
t_MainData.ProductSerialNo AS [Serial number], t_MainData.CRT_LCD_SerialNo AS
[CRT Serial No], t_MainData.CRT_LCD_Maker AS [CRT Manufacturer Name],
t_MainData.ConditionCode1 AS [IRIS Condition Code], t_MainData.DefectCode1 AS
[IRIS Defect Code], t_MainData.SymptomCode1 AS [IRIS Symptomn Code],
t_MainData.RepairCode1 AS [IRIS Repair Code], "" AS IRIS_Flag, "" AS
IRIS_Section, "" AS Accessory, Year([PurchaseDate]) & Right(("0" &
Month([PurchaseDate])),2) & Right(("0" & Day([PurchaseDate])),2) AS [Purchase
Date], Year([RequestedDate]) & Right(("0" & Month([RequestedDate])),2) &
Right(("0" & Day([RequestedDate])),2) AS [SVC Request Date],
Year([UnitDespatchDate]) & Right(("0" & Month([UnitDespatchDate])),2) &
Right(("0" & Day([UnitDespatchDate])),2) AS [SVC Complete Date],
Year([FG_Date]) & Right(("0" & Month([FG_Date])),2) & Right(("0" &
Day([FG_Date])),2) AS [Delivery date], t_MainData.Dealer_Name AS [Dealer
name/Code], "" AS Distance, "" AS [Delivery Cost], "" AS [Parts Amount], ""
AS [Other Cost], "" AS [Other Amount Description],
Left$([DetailSymptomDesc],50) AS [Defect Description],
t_MainData.DetailRepairDesc AS [Repair Description], t_MainData.Remark, "" AS
invoicedate, "" AS invoicenumber, t_MainData.TransactionNo AS [Contact Centre
Call Number], "O" AS CallType, "" AS PrintedPages, t_MainData.Parts1Code1 AS
[Part Number], t_MainData.Location1No1 AS Location, IIf((Len([Part
Number])>0),1,"") AS [Used Quantity], "" AS [Parts Amount 1], "" AS [Document
Number 1], t_MainData.Parts1Serial1 AS [Part Serial Number 1],
t_MainData.Parts2Code1 AS [Part Number 2], t_MainData.Location2No1 AS
[Location 2], IIf((Len([Part Number 2])>0),1,"") AS [Used Quantity 2], "" AS
[Parts Amount 2], "" AS [Document Number 2], t_MainData.Parts1Serial2 AS
[Part Serial Number 2]
FROM t_Customers INNER JOIN t_MainData ON t_Customers.CustomerNo =
t_MainData.CustomerNo
WHERE (((t_MainData.DefectType)<>"BS") AND ((t_MainData.Uploaded)=No) AND
((Len([FG_date]))>6) AND ((t_MainData.Manufacturer)="Samsung"));
The new query
SELECT t_MainData.ProductionSubsidiaryCode AS [Company Code],
t_MainData.ASC_Code AS [Repair Centre Code], t_MainData.Amk_ID AS [Claim
Number], t_MainData.RepairType AS [Service type], t_MainData.DefectType, ""
AS Authorisation_No, t_Customers.Name AS [Consumer Name],
t_Customers.Address1, t_Customers.Address2, t_Customers.TownCity AS Address3,
t_Customers.PostCode, t_Customers.Telephone1 AS [Consumer Tel],
t_Customers.Fax, t_MainData.RunningModel AS [Model name],
t_MainData.ProductSerialNo AS [Serial number], t_MainData.CRT_LCD_SerialNo AS
[CRT Serial No], t_MainData.CRT_LCD_Maker AS [CRT Manufacturer Name],
t_MainData.ConditionCode1 AS [IRIS Condition Code], t_MainData.DefectCode1 AS
[IRIS Defect Code], t_MainData.SymptomCode1 AS [IRIS Symptomn Code],
t_MainData.RepairCode1 AS [IRIS Repair Code], "" AS IRIS_Flag, "" AS
IRIS_Section, "" AS Accessory, Year([PurchaseDate]) & Right(("0" &
Month([PurchaseDate])),2) & Right(("0" & Day([PurchaseDate])),2) AS [Purchase
Date], Year([RequestedDate]) & Right(("0" & Month([RequestedDate])),2) &
Right(("0" & Day([RequestedDate])),2) AS [SVC Request Date],
Year([UnitDespatchDate]) & Right(("0" & Month([UnitDespatchDate])),2) &
Right(("0" & Day([UnitDespatchDate])),2) AS [SVC Complete Date],
Year([FG_Date]) & Right(("0" & Month([FG_Date])),2) & Right(("0" &
Day([FG_Date])),2) AS [Delivery date], t_MainData.Dealer_Name AS [Dealer
name/Code], "" AS Distance, "" AS [Delivery Cost], "" AS [Parts Amount], ""
AS [Other Cost], "" AS [Other Amount Description],
IIf(Len([DetailSymptomDesc])>49,Left$([DetailSymptomDesc],49),[DetailSymptomDesc])
AS [Defect Description],
IIf(Len([DetailRepairDesc])>49,Left$([DetailRepairDesc],49),[DetailRepairDesc])
AS [Repair Description], IIf(Len([Remark])>64,Left$([Remark],64),[Remark]) AS
Remarks, "" AS invoicedate, "" AS invoicenumber, t_MainData.TransactionNo AS
[Contact Centre Call Number], "O" AS CallType, "" AS PrintedPages,
t_MainData.Parts1Code1 AS [Part Number], t_MainData.Location1No1 AS Location,
IIf((Len([Part Number])>0),1,"") AS [Used Quantity], "" AS [Parts Amount 1],
"" AS [Document Number 1], t_MainData.Parts1Serial1 AS [Part Serial Number
1], t_MainData.Parts2Code1 AS [Part Number 2], t_MainData.Location2No1 AS
[Location 2], IIf((Len([Part Number 2])>0),1,"") AS [Used Quantity 2], "" AS
[Parts Amount 2], "" AS [Document Number 2], t_MainData.Parts1Serial2 AS
[Part Serial Number 2]
FROM t_Customers INNER JOIN t_MainData ON t_Customers.CustomerNo =
t_MainData.CustomerNo
WHERE (((t_MainData.DefectType)<>"BS") AND ((t_MainData.Uploaded)=No) AND
((Len([FG_date]))>6) AND ((t_MainData.Manufacturer)="SMG"));
In the middle of the query i needed to do some formatting so I changed this
extract
Left$([DetailSymptomDesc],50) AS [Defect Description],
t_MainData.DetailRepairDesc AS [Repair Description], t_MainData.Remark
to
IIf(Len([DetailSymptomDesc])>49,Left$([DetailSymptomDesc],49),[DetailSymptomDesc])
AS [Defect Description],
IIf(Len([DetailRepairDesc])>49,Left$([DetailRepairDesc],49),[DetailRepairDesc])
AS [Repair Description], IIf(Len([Remark])>64,Left$([Remark],64),[Remark]) AS
Remarks
If I change this extract back then it works again - this is a real
headbuster for me, I'm a bit of a newbie at the database game. I hope one of
you guys can save me on this one, as my boss is really busting my head about
not being able to make the customer upload yesterday
Thanks
Alec