Importing into another database

[Pikeboy]

Looking for help with syntax to do the following:

While in database1 I’d like to delete records from a table in database2 where ‘year’ = X. After this, append the same table with an imported Excel file. Thanks in advance.

 

[isladogs]

There is an easy way & a hard way.
You sound like you're trying to do the hard way.

Instead, if not already done, add the table from database2 as a linked table in database1
Then you can do this from database1 direct without opening database2

 

[Pikeboy]

The table is large so I had put it into another dB to help with size management. If nothing else I’d like to learn how to execute what I’ve described. Thanks for your feedback.

 

[arnelgp]

firstly you need to make a Linked table (from db2) to db1.
second, create a query that will delete the records from this table:

delete * from [linkedTableName] Where Year([DateField]) = 2017;

lastly, import the excel file to this table:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "linkedTableName", "path and filename of excel", True

 

[isladogs]

Splitting the database is a good idea but the normal method is then to link the tables in datbase2 as a backend file.
Arnelgp has given you details of the process I outlined.

However you wanted more details of how to d it the other (harder) way.
Have a look at this thread for more details. https://www.access-programmers.co.uk/forums/showpost.php?p=1578578&postcount=5
You should be able to adapt it but I do not recommend that approach.

One more thing. If the external table is very large, it will be slow to delete all records and append replacement records. It will also cause database bloat. Instead consider updating records that already exist and just append new records. It may not be faster but there will be less bloat.