MS Access 2003: Importing files and validating them

[lrheeza]

Hello everyone,

I am a newbie at MS Access and I need help!!!

I am importing an excel file using Import functionality in MS Access, all the fields are required but there are instances in the excel sheet wherein some of the fields are not populated. The fields that are being imported are: Full Name, first name, last name, initial (optional), userID and email address. How do i inform the users that the following records were not imported because the following values are not available.

How do I display the list of users which do not have the required values available?

I am importing the file to a temporary table. Is there an easy way of validating data from the temp table and then returning error messages to the user? Where can this be coded?

Because after validating the file that was imported, I have to check if this the main repository is empty. If it is empty, copy the data to the main repository table. If it is not empty, then compare the temp table with the existing table and then return a message what has been changed. If the user selects, YES then changes are copied to the main table.

thanks,
rhiz

[Stewart Ross]

...I am importing an excel file using Import functionality in MS Access, all the fields are required but there are instances in the excel sheet wherein some of the fields are not populated. The fields that are being imported are: Full Name, first name, last name, initial (optional), userID and email address. How do i inform the users that the following records were not imported because the following values are not available..

Hi Irheeza. Before delving into validation of missing rows, you need to check whether the import into Access is correctly transferring the original Excel data. Because Excel cells can contain numbers, text, or mixed alpha numeric values the import routines in Access can become confused over the right type of the data, which is guessed from the contents of the first row of data in the excel sheet.

As an example, if the first field of the first row imported has a number in it the Access import routine will create a numeric field in that position of the import table. If a later row in Excel has a text value in that cell instead of a number the Access import will stumble, because it can't put the text value into the numeric field. This will result in a missing field value in your Access table (and possibly an entry for that row in an error table, depending on how you are importing or pasting the data).

It could be that it is not the users that are at fault over missing data, but the Access import itself. Let's eliminate that first, so could you check the imported data against the Excel original and let us know, please?

Regards

Stewart

[lrheeza]

thanks, stewart, i will do that.