Field Validation Rule and required parameter setup to import #NA into double field

Question

I need to import over 1 million Excel rows with ~10 columns each into Access records/fields.

My problem is some of the fields are calculated in Excel in VBA from other rows and columns (easily done in Excel, slow and not so easy in Access) prior to importing into Access. And some of the rows (records) calculated fields can not be calculated because data for the calculated field does not exist. These fields should be null, or something easily identifiable, like #NA, so that when statistics are later calculated these records and fields can be filtered and excluded.

However, I have problems inserting nulls in Excel prior to importing. And problems importing #NA into an Access double field.

Access Help, supplemented with web searches, indicate that an Access field can be configured to do so with the parameters: 1. Required (no or yes?), 2) Validation Rule, and, 3) Validation Text (friendly error message when error occurs).

I need some help in defining the Validation Rule in an access field to allow double imports and a #NA string. A Null is Probably a don't care. Should the Required Parameter be set to yes or No?

Thanks you.

Answer 1

Thanks.

You are telling me what I seem to be experiencing. Although I intermittently get errors on Null imports for reasons I haven't figured yet.

I have had some success using a loop for Excel Row to Access records in VBA:

rst!Field1 = WS.Sh.Range("A5") etc for other columns to a record.

Actually, Nulls would work after import if I can reliably use an SQL Query to exclude those records from inclusion in statistical analysis of the data not having Nulls.

I am getting input that the Access TransferSpreadSheet ... operation does not reliably import formula values in cells from Excel. It has been suggested that I directly put VBA calculated double values or a Null in the cells and try to import. As this seems much faster, it is preferred.

Any comments?

Thanks.

Answer 2

If you leave Required set to NO you can put Null into the field.

If the field is of Number or Currency type then you CANNOT - absolutely can not! - store the text string "#NA", or any other text string, in the field. A Number field does not store characters; it's stored as a binary bitstring.

If you allow Nulls you can use a Format property to display #NA if the value is Null, but that won't help much with the import!

I'd recommend linking to - not importing - the spreadsheet, and running an Append query to populate the data into a non-required Double or Currency datatype field, using an expression like

IIF([fieldname] = "#NA", Null, [fieldname])

or

IIF(IsError([fieldname]), Null, [fieldname])

to insert NULL values.

Answer 3

Thank you for your response.

The calculated fields in Excel do not change once calculated. I am only interested in the resulting value being stored in the Access Table. So I suppose that I could do the calculations in VBA and only store the values in the Excel Cells before importing to Access?

But I would like a method to indicate that in Access that a field in a record does not have available available data and should not be used in statistical analysis. Hence, an interest in inserting a null or a #NA text value in these fields, if possible. If I leave the Required Property No, can I add a validation field to allow the text string #NA?

Interestingly, I have been importing this data into access from Excel from several years by using a do loop and one row to a access record at a time using rst!Field1 = value for all the relevant columns in a row. This method seems to accept null with no errors. However, it is slow, but works well for incremental updates, less well for database initialization. And it executes faster if the VBA code is in Excel with connection to Access rather than having the VBA code in Access.

Hence, the interest in using the faster TransferSpreadsSheet in Access for database initialize.

Answer 4

If you set the Required property to Yes, it cannot be null. And there is no way in any relational database engine that I know of to put a string in a numeric field.

And importing excel data that contains calculations is always problematic. And since storing calculated fields in a database is not a good idea in most cases, maybe you should skip those fields and display the calculations in unbound text boxes on forms and reports. Storing a calculated field is dangerous because a user can change the data in one of the dependent fields and the calculation will not be updated as it is in Excel. It must be forced either through VBA, a macro or a manual effort.