Copying tabular data into a table

[Jennifer Murphy]

While watching this tutorial,

http://www.youtube.com/watch?v=0JL9ajkOavw

the author described a way to copy data from his website into an Access table. This seems like a useful mechanism, but after playing with it a bit, I discovered that it seems to have a few quirks. Here's a little summary I wrote for my own use in the future. I would appreciate any critiques or suggestions:

Copying Multiple Records from Tabular Data

Tabular data (from Word, Excel, website) can be (a) added to an Access table (new records) or (b) written over existing records in an Access table. The pasted data can include all fields or two or more adjacent fields. I do not know how to make this work with either non-adjacent fields or a single field.

• Access:
  
  • Determine which fields will be affected.
  • Ensure that the fields are adjacent. If it’s a large amount of data, it may be worthwhile to temporarily rearrange the the fields in the Access table to facilitate the copy.
  
  
• Source (Word, Excel, website):
  
  • Make sure that the columns of data to be copied match the table fields exactly: same order, same data types, etc.
  • Make sure that there is a column of source data for any Autonum fields that are selected. The data will be ignored, but if it isn’t there, the next column of data will be used (and discarded).
  • Copy the source data to the clipboard (all rows and columns).
  
  
  
  
• Access:
  
  • If the records are to be added, select the corresponding fields in the (New) record.
  • If the data is to overwrite the data in existing records, select the rectangle covering those records and fields.
  • A yellow border will be drawn around the selected records and fields.
  • Paste the data (Ctrl-V).
  • Access will append/overwrite the data and put up a confirmation Message Box.
  • If it’s correct, click Yes. If not, click No.
  
  

A few questions:

1. Access allows me to select the Autonum field and does not complain when I paste into it, but then throws the data away. This seems like very bad UI design. If I make a mistake and select the wrong fields, and especially if the data is such that an error is not obvious, this could result is really bad results.
2. There doesn't seems to be any way to append 50 new records that only contain a single field. If I select just one fields in the (New) record, even if I copy a 50 x 1 column of data, Access pastes it all into that one field.

BTW: This series of tutorials are pretty good. They are very elementary, but they don't take long to go through (about 10-12 minutes each). I've learned a few useful things. When I finish the free ones, I plan to try his paid tutorials. I'll try to remember to post a review here.

[June7]

1. Copy/Paste is not intended to be user interface for data entry. That's what forms are for. Users should not work directly with tables and queries, only forms and reports.

2. Still have to select the entire new record row, and the single field must be the first one.

[Jennifer Murphy]

1. Copy/Paste is not intended to be user interface for data entry. That's what forms are for. Users should not work directly with tables and queries, only forms and reports.

I didn't say anything about users entering data. Regardless, this is something that Access has, so it ought to work right. It doesn't, in my humble opinion.

2. Still have to select the entire new record row, and the single field must be the first one.

That's not what I'm seeing.

Suppose I have this table in Access (2007):

Field Name	Data Type
ID	Autonumber
F1Text	Text
F2Memo	Memo
F3Curr	Currency
F4Num	Number

containing this data:

ID	F1Text	F2Memo	F3Curr	F4Num
1	1-1	2-2	$3.01	41
2	2-1	2-2	$3.02	42
3	3-1	2-2	$3.03	43
(New)

And suppose I have this table in Word:

ID	F1Text	F2Memo	F3Curr	F4Num
100	1/1	2/1	$31.00	401
200	2/1	2/2	$32.00	402
300	3/1	2/3	$33.00	403

From Word I copy the red cells to the clipboard:

ID	F1Text	F2Memo	F3Curr	F4Num
100	1/1	2/1	$31.00	401
200	2/1	2/2	$32.00	402
300	3/1	2/3	$33.00	403

then I select just the F2Memo & F3Curr fields in the (New) row (not the entire new record) and paste (Ctrl+v), I will get:

ID	F1Text	F2Memo	F3Curr	F4Num
1	1-1	2-2	$3.01	41
2	2-1	2-2	$3.02	42
3	3-1	2-2	$3.03	43
4		2/1	$31.00
5		2/2	$32.00
6		2/3	$33.00
(New)

But if I select 3 rows and just 1 column:

ID	F1Text	F2Memo	F3Curr	F4Num
100	1/1	2/1	$31.00	401
200	2/1	2/2	$32.00	402
300	3/1	2/3	$33.00	403

and try to paste it into the F2Memo field of the (New) record, I get:

ID	F1Text	F2Memo	F3Curr	F4Num
1	1-1	2-2	$3.01	41
2	2-1	2-2	$3.02	42
3	3-1	2-2	$3.03	43
4		2/1 2/2 2/3
(New)

All of the data is pasted into the one cell. This is either a design bug or an implementation bug. And there are other anomalies.

[June7]

I don't have much reason to do copy/paste but I get you're meaning. Regardless, tables cannot be expected to behave exactly like spreadsheet.

In a spreadsheet, a single cell can have focus without actually being in the cell. Double click or just start typing and this activates the edit window. Not so with an Access table, single field on single record has focus, you are in the field.