I have an access front end, connected to SQL server 2008 express as back end. Migration was done using SSMA. Once migrated, the client machine copies of Access application, are connected using ado/pass through queries/ODBC,
and did not had any SSMA local tables.No issues on this, as the data is being retrieved and displayed on front end.

However, on the Access front end, we have an import utility, that loads four text files, and does some query processing and finally loads into the main table(Table A), which is currently residing on Access.Same table (Table A) exists on SQL Server also.
Every week, the table A gets new rows.

In theory, if a linked table is set up for this table, the newly added rows would be added to SQL server table as well. This is not working.


Eg: Before import, Table A on Access and SQL had 80000 rows.
When the linked table was set up, but it also got 80000 rows. The linked table used ODBC to connect to SQL server. Linked table was set up from "External data" tab/ Import section/ more--> ODBC data sources". This created a linked table, with rows imported from SQL server.
Once the import utility is ran on Access front end, the Table A on access DB got about 90000 rows. But the Linked table nor SQL table still show 80000 rows, and is not synced with access table.
Closed and reopend the access database, assuming it would refresh. No luck.

Tried to setup the linked table from "External data" tab/ export section/ more--> ODBC data sources". When the destination table is specified, it returned an error indicating the table already exists. The intent is to append the new rows to existing table.!!

Tried with two DSNs one with SQL server driver and other
with SQL server native client 10.0 driver as well.

What could be wrong. Any help appreciated.

How are you moving the imported rows from the access table to the linked SQL server table? Did you use an append query, ADO recordset to insert them? OR?

Can you append data to SQL Server using the Link Table? When you migrated the table to SQL, did you set a Primary Key in SQL? This is usually why most new tables cannot be updated. They need a PK.

I have removed the PK on the SQL server table, and recreated liked table on Access. No luck. Tables on SQL server is not refreshed.

The imported rows, that were loaded onto access table through the utility, does not reflect in the linked table, nor on SQL server table.
I am looking for help to get these rows onto SQL server table.

YOU, the developer, have to do something to move the newly imported records from the Access table into the SQL Server table yourself. Moving data from one table to another is YOUR responsibility. Access has no way of knowing that's what you want to have happen.

As Walter suggested, write and execute an append query to copy the new records into SQL Server after your import into the Access tables from the text files. Add that step to the import function.