Unanswered: Howto Make or Copy SQLServer Table to Access db via ODBC
I have several apps created in Access 97 that need daily updates from a SQL Server database. The apps have a server-based Access97 backend with a frontend installed locally on users machines (different apps =>different users -- 6-10 users/app). I need to keep the SQL Tables (views) concealed, so I want NOT to have them as linked tables.
I have created pass-thru queries and code-based querydefs, and am successful in returning data from the the SQL Server with SQL Select statments with no problem.
However, when I attempt to run an Insert Into xxxx Select yyy From zzz (append) or Select xxx Into yyy From zzz (make new table) I get either an "Error 3065 Raised by DAO.QueryDef: Can't execute a Select Query" (when returnrecords=true) or an Error 3146 Raised by DAO.QueryDef: ODBC--Call Failed (when returnrecords=false).
I know I could just use the Pass-thru select and then step through the 6000+ records, but that seems silly. For efficiency, I think (know) I need to do a table copy or append, but I have been unable to figure how to acomplish that.
I should be able to do a simple "copy" of the SQL tables. Correct??