Populate access table from excel

[OCM]

Greetings,

We receive monthly payments that a former employee used to input in the access db manually. In doing so, the Col_ID field is auto generated (primary key). Currently, we receive the monthly payments in excel format and I’m exploring to import the payments into access in bulk. The column heading in excel (attached) spreadsheet exactly matches the field headings in MS access table.

1.A given Case can have multiple payments in a given month e.g., C004200 in attached sample. When importing, do I use report1 or report2. The only difference is that report1 lists the case ID only once for each payment. Report2 repeats the case ID for all payments.

2.Since Col_ID is a primary key and it is required, how can I get access auto populate/assign Col_ID?

TIA

Regards,


OCM

 

[dhookom]

I would definitely use Report2. When you import into MS Access, don't include the Col_ID from Excel. You can create a link from Access to the Report2 file and then use an append query.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[OCM]

Hi Duane, thanks!
I received the following error:

TIA,

Regards,

OCM

 

[dhookom]

I have no idea how the Excel file relates to your question or where to find any error message.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[OCM]

Duane,
Once I accessed the Get External Data, I'll choose the 3rd option 'Link to the data source by creating a linked table' As you suggested, I'll not included the Col_ID field in my excel file, correct?

TIA

Regards,

OCM

 

[dhookom]

I would include all columns from Excel but append only the fields needed to your Access target table.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[OCM]

Dhookom,
Thank you for the suggestions, I was able to import the records successfully.

Regards,

OCM