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.