Access 2010 ODBC Connection to SQL Server 2005 I'm trying to code an ODBC connection to SQL Server in Access 2010 and hoping someone can help me, as I've lost the plot.

This is my code, it simply accesses one SQL Server database/table and insert into another when a button is clicked:

sql-casecodes is an ODBC table link

DoCmd.RunSQL "insert into [sql-casecodes] (CaseCode,CaseName) values select casecode, name FROM [ODBC;Driver=SQL Server;Server=SQL1;Database=Clients;Trusted_Connection=Yes].clients_case"


When the code is run, Access gives me a run-time error of 


  '3134' syntax error in INSERT INTO statement. 


But the code looks ok?

I don't want to be using system ODBC links as I want to learn how to code it in
The INSERT command comes in two flavors (this is for SQL Server but there's a good chance it'll be the same for Access, too):

(1) either you have all your values available, as literals or SQL Server variables - in that case, you can use the INSERT .. VALUES() approach:

INSERT INTO dbo.YourTable(Col1, Col2, ...., ColN)
VALUES(Value1, Value2, @Variable3, @Variable4, ...., ValueN)


Note: I would recommend to always explicitly specify the list of column to insert data into - that way, you won't have any nasty surprises if suddenly your table has an extra column, or if your tables has an IDENTITY or computed column. Yes - it's a tiny bit more work - once - but then you have your INSERT statement as solid as it can be and you won't have to constantly fiddle around with it if your table changes.

(2) if you don't have all your values as literals and/or variables, but instead you want to rely on another table, multiple tables, or views, to provide the values, then you can use the INSERT ... SELECT ... approach:

INSERT INTO dbo.YourTable(Col1, Col2, ...., ColN)
   SELECT
       SourceColumn1, SourceColumn2, @Variable3, @Variable4, ...., SourceColumnN
   FROM
       dbo.YourProvidingTableOrView


Here, you must define exactly as many items in the SELECT as your INSERT expects - and those can be columns from the table(s) (or view(s)), or those can be literals or variables. Again: explicitly provide the list of columns to insert into - see above.

You can use one or the other - but you cannot mix the two - you cannot use VALUES(...) and then have a SELECT query in the middle of your list of values - pick one of the two - stick with it.

For more details and further in-depth coverage, see the official MSDN SQL Server Books Online documentation on INSERT - a great resource for all questions related to SQL Server!