Append 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 (PK) is auto generated. Now, I would like to import the payments that is in excel to an existing access table. The column heading in excel (attached) spreadsheet exactly matches the field headings in access table. Col_ID in ms access is a primary key usually auto populated by the DB when someone enters a new payment. Currently, the Col_ID field in my excel is blank. Do I include the Col_ID field in my excel during the append process? How can I make sure that the Col_ID field in access table is populated? TIA Regards,

[madpiet]

I'd just create a linked table that points at the Excel file. Then you can just treat it like a normal table (well, sort of). Just create an append query and run it. Why not make a backup copy of the database and play with it? Seriously, TRY IT. The only thing an autonumber column does that's special is that it creates a mostly sequential set of numbers as it adds unique ID's to records.

Instead of importing the contents of a blank column, why not just create a unique ID in the table you're importing into?

[June7]

As advised, append records. Don't include the blank Excel column. Access will still generate unique ID in the autonumber field.

[Welshgasman]

Get rid of the Excel ID column, then just append as already mentioned.
I would link to the Excel file from Access, then just keep overwriting the Excel workbook, so you do not add the records more than once, else you will need to check if they have already been added, if you are just adding rows to the worksheet?

[madpiet]

Good point about not importing records already in the table. In that case, you could use the Find Unmatched Query wizard to return only the records that are in the new file but not in the database table. Then append only those. (Would make more sense if you try it with some dummy data, though, if you've never done it before - and to make sure you have it right).

[June7]

Review https://stackoverflow.com/questions/...crosoft-access

[ArviLaanemets]

As others here adviced, have an Excel table with payments to be imported linked to Access database. But use the linked table only for updating the payments table you create in Access database. I.e. you create an event (p.e. OnOpen, or OnClick for button, or whatever), which runs an append query to add the new payments from linked Excel table into Access Payments table. Optionally you can this script also to clear data from linked table after it is successfully imported to Access Payments table.

On Excel side, my advice is you add into Excel workbook, with Excel Payments table linked to Access database, a button which will run a VBA script, which ask for client Excel file to be read in, and adds the payment info for this client into linked Excel table.

The client sends the payment info in file of certain format > Some employee imports payment info from various payments files into linked Excel table > The Access VBA Procedure updates Access Payments table with info from Excel Payments table > The Access VBA procedure clears the info presently imported into Access Payments table from linked Excel Paymets table.