Data Validation when pasting data into tables

[David Ball]

Hi,

I have a table that I paste data into every week. The data comes in a large Excel spreadsheet that I receive from another department.
I only need three columns of this data and I delete everything else and just paste the three columns directly into the table.
I would like to be certain that the columns pasted into the table are:

1. The correct columns
2. In the correct order

The columns have headings that never change and I was wondering if there is any way that Access can check that the columns I paste in are the correct ones.

I don't paste the columns headings into the table as two of the fields in the table are date fields and the headings are all text.



How would I do this in Access?

Thanks very much

Dave

 

[John W. Vinson]

Hi,

I have a table that I paste data into every week. The data comes in a large Excel spreadsheet that I receive from another department.
I only need three columns of this data and I delete everything else and just paste the three columns directly into the table.
I would like to be certain that the columns pasted into the table are:

1. The correct columns
2. In the correct order

The columns have headings that never change and I was wondering if there is any way that Access can check that the columns I paste in are the correct ones.

I don't paste the columns headings into the table as two of the fields in the table are date fields and the headings are all text.



How would I do this in Access?

Thanks very much

Dave

Don't copy and paste at all!

Instead, use External Data... Link to LINK to the spreadsheet, rather than
import it. Then create an Append query, selecting those three columns (by
name), and appending them to your local Access table.

You can keep the same query longterm, just use the same tablename for the
linked spreadsheet.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Gloops]

David Ball wrote, on 16th Apr. 2012 08:40 UTC + 1 :

Hi,

I have a table that I paste data into every week. The data comes in a large Excel spreadsheet that I receive from another department.
I only need three columns of this data and I delete everything else and just paste the three columns directly into the table.
I would like to be certain that the columns pasted into the table are:

1. The correct columns
2. In the correct order

The columns have headings that never change and I was wondering if there is any way that Access can check that the columns I paste in are the correct ones.

I don't paste the columns headings into the table as two of the fields in the table are date fields and the headings are all text.



How would I do this in Access?

Thanks very much

Dave

Hello,

John has a very good idea, to verify the names of the columns.

Supposing some records do not respect the integrity constraints (which
you did not mention, but can happen), you should get the errors in an
error table, that you are supposed to empty before the import to avoid
accumulating error reports for several imports. It would be a good idea
to test that, to be sure of a good integration. It is generally a good
idea to test the existence of the error table to empty of suppress it
(verify with tests), and let it being created when applied.

There can be cases where you are not satisfied to get the automatic
error reports in the standard table, and you prefer to write code
yourself to read the Excel sheet, control the contents, write data to
the table if it is correct, and report errors to the user the way you
feel proper. There can be cases where the criteria are easier to express
in such a context than in the definition of the destination table. That
being said, if you can refine the definition of the table, it is
generally considered to be better (and more simple).