Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

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

SQLTeam Poet Laureate

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  
 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.02 seconds. Powered By: Snitz Forums 2000