Transfer Database problem with Access 2000 1

[vamoose]

The first action performed is to import an Excel text file. Then I drop the unnecessary columns. Then I rename and modify the remaining columns using the following code:

CurrentDb().TableDefs("SAP_Import").Fields("F1").Name = "PDate"
CurrentDb().TableDefs("SAP_Import").Fields("F2").Name = "Priority"
CurrentDb().TableDefs("SAP_Import").Fields("F9").Name = "Shortage"
CurrentDb().TableDefs("SAP_Import").Fields("F11").Name = "Hours"
CurrentDb().TableDefs("SAP_Import").Fields("F14").Name = "Exists"
CurrentDb().TableDefs("SAP_Import").Fields("Page:").Name = "Wound"

CurrentDb().Execute "alter Table SAP_Import alter column PDate Date"
CurrentDb().Execute "alter Table SAP_Import alter column Priority Double"
CurrentDb().Execute "alter Table SAP_Import alter column Shortage Double;"
CurrentDb().Execute "alter Table SAP_Import alter column Hours Double;"
CurrentDb().Execute "alter Table SAP_Import alter column Exists Double;"
CurrentDb().Execute "alter Table SAP_Import alter column Wound Double;"

Now when I try to use this code to export a copy:

Filename = "c:\Winding\Working\Copy.mdb"
DoCmd.TransferDatabase acExport, "Microsoft Access", Filename, acTable, "Flex_Winder", "Flex_Winder", False

I get an error: "Cannot place this validation expression on this field."

Now, if I go to the actual table and delete just these 6 modified columns it will transfer just fine. I don't understand what I am missing here. Is there anything obvious to any of you ? Thank you for any assistance.

 

[JerryKlmns]

vamoose

Link the excel file and append the fields of the records you would like to a table of your favor. It is extra work to import excel, drop+rename fields and then export the table. A more radical solution would be to use a query that selects only the fields of the linked excel file using aliases for any field names you need.

One step more is to use a make table query to the Copy.mdb form your database along with the linked excel and aliases and no more exporting needed!

SELECT LinkedExcelFile.Field1 As TheFirstField,
       LinkedExcelFile.Field2 As TheSecondField,
       LinkedExcelFile.Field6 As TheThirdField
  INTO Flex_Winder IN "c:\Winding\Working\Copy.mdb"
FROM LinkedExcelFile;