SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Other Forums
 MS Access
 ODBC Linked Table INSERT from SELECT
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rrb
SQLTeam Poet Laureate

Australia
1479 Posts

Posted - 01/08/2014 :  19:43:24  Show Profile  Reply with Quote
Hi all

I'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.
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000