Pasting complete row from Excel to Access Form (1 Viewer)

AnilBagga

Member
Local time
Today, 17:38
Joined
Apr 9, 2020
Messages
137
I have a form frmContainerbooking in the attached DB. It has 5 different sections which represent 5 tranches of information coming from different sources who are not in the domain of access users

Section 1 which generates the BookingRefNo etc will be created in Access. The information comes in Excel and is manually copy pasted cell by cell which is very time consuming and prone to errors. What is the best way to do this?

We can build 3 different forms for each activity if it helps.

We cannot allow Access to the Table for the users as the database is split and the user are working on their fe versions with suitable forms and reports
 

Attachments

  • MIS_fe - Copy.zip
    165 KB · Views: 15

arnelgp

error reading drive A:
Local time
Today, 20:08
Joined
May 7, 2009
Messages
11,146
create a Link table from excel file and use query to extract the info.
 

AnilBagga

Member
Local time
Today, 17:38
Joined
Apr 9, 2020
Messages
137
create a Link table from excel file and use query to extract the info.
Thanks
1. Can I create an Append query with the linked Excel file as a data source?

2. We will appending data of these 3 queries to records which are already created. Will these 3 append queries work and append those specific fields of the file/query leaving all other data of other fields of the same record untouched?
 

arnelgp

error reading drive A:
Local time
Today, 20:08
Joined
May 7, 2009
Messages
11,146
1. You can append it to a table not to linked excel file.
2. Use Update query. Use Right join to Append and at the same time update existing record.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:08
Joined
Feb 19, 2002
Messages
30,615
Append queries add new rows to tables.
Update queries update existing rows.

Relying on Excel to be the "master" is problematic. The master should always be a database. Spreadsheets are excellent for analyzing data. They are a poor choice as a database.

If only new data comes in from the spreadsheet, use append queries. If you are trying to update existing data, you need a key on which to join the spreadsheet to your table. Do you have such a common unique identifier?
 

AnilBagga

Member
Local time
Today, 17:38
Joined
Apr 9, 2020
Messages
137
1. You can append it to a table not to linked excel file.
2. Use Update query. Use Right join to Append and at the same time update existing record.
I did that but the append query update
Append queries add new rows to tables.
Update queries update existing rows.

Relying on Excel to be the "master" is problematic. The master should always be a database. Spreadsheets are excellent for analyzing data. They are a poor choice as a database.

If only new data comes in from the spreadsheet, use append queries. If you are trying to update existing data, you need a key on which to join the spreadsheet to your table. Do you have such a common unique identifier?
Thanks

Yes i have a key and have constructed the update query

In an append query if the autonumber I'd is not in the fields, will Acees generate the new I'd for appended records?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 06:08
Joined
Feb 28, 2001
Messages
18,735
To answer the direct question at the end of post #6, Access will generate a new autonumber ID field when you append the other fields.
 

Users who are viewing this thread

Top Bottom