Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
rrb
SQLTeam Poet Laureate
1479 Posts |
 Posted - 2014-01-08 : 19:43:24
|
Hi allI've just upsized some Access MDBs - so now have ODBC Link tables to two different SQL Server databases. The Access application remains coded using DAO with linked tables to the SQL Server databases.I need to copy rows from one table to the other - e.g.insert into [LINKA_TableA] select [col1], [col2] from [LINKB_TableA]Obviously worked fine when JET/ACE was the backend. Now that SQL Server is the backend, exact same code either fails with "ODBC-Call Failed." - either that or it doesn't throw any error and just fails to insert anything (if the source table has only a few rows).I've been searching around but not found anything on the net which says I can't do this, but I'm having no luck. The destination table has a primary non-null key and I can do the same insert in T-SQL (obviously the table names are different in the SQL Server context).I've tried different versions of the SQL Driver (Native etc), different versions of Access etc, same problem. I've also created a simplified version with vanilla .accdb files, SQL tables and can replicate the same problem.Is anyone aware of any limitation of ODBC Link tables which precludes me from insert-into-select-from where the two link tables are pointing at different connection strings?Or do I really have to recode using ADO and do it row by row (really!!?) --I'm not schooled in the science of human factors, but I suspect that surprise is not an element of a robust user interface. |
|
|
|
|
|
ODBC Linked Table INSERT from SELECT