I have a VB application that (among other things) copies certain records
from a database into an Excel spreadsheet via ADO connection at the end of
an operation.  Originally, the database was MS Access.  Recently we migrated
the data to SQL (Server 2000).  Now my process that INSERTs the records
returns errors.

The code:
    Set conn = New ADODB.Connection
    Set cmd = New ADODB.Command
    conn.ConnectionString = "Provider=MSDASQL.1;Extended
Properties=""DSN=Operations;Description=Operations Database;APP=Visual
Basic;WSID=AValidID;DATABASE=operations;Network=DBMSSOCN;Trusted_Connection=
Yes"""
    conn.CursorLocation = adUseClient
    conn.Open
    strSQL = "INSERT INTO [Shipped$] IN '' [Excel 8.0;Database=" & _
             "X:\AValidPath\shipping.xls] SELECT  ShipDate, PONumber,
ModelNo, Comments, " _
                & "CustName FROM Shipped " _
                & "WHERE status = 'P'"
    conn.Execute strSQL
    conn.Close

The error returned is [Microsoft][ODBC SQL Server Driver][SQL
Server]Incorrect syntax near the keyword 'IN'.

Any thoughts from the wizards?

Hope someone can help with this problem.

I have a VB application that (among other things) copies certain records
from a database into an Excel spreadsheet via ADO connection at the end of
an operation.  Originally, the database was MS Access.  Recently we migrated
the data to SQL (Server 2000).  Now my process that INSERTs the records
returns errors.

The code:
    Set conn = New ADODB.Connection
    Set cmd = New ADODB.Command
    conn.ConnectionString = "Provider=MSDASQL.1;Extended
Properties=""DSN=Operations;Description=Operations Database;APP=Visual
Basic;WSID=AValidID;DATABASE=operations;Network=DBMSSOCN;Trusted_Connection=
Yes"""
    conn.CursorLocation = adUseClient
    conn.Open
    strSQL = "INSERT INTO [Shipped$] IN '' [Excel 8.0;Database=" & _
             "X:\AValidPath\shipping.xls] SELECT  ShipDate, PONumber,
ModelNo, Comments, " _
                & "CustName FROM Shipped " _
                & "WHERE status = 'P'"
    conn.Execute strSQL
    conn.Close

The error returned is [Microsoft][ODBC SQL Server Driver][SQL
Server]Incorrect syntax near the keyword 'IN'.

Any thoughts from the wizards?

Not sure whether this will work with SQL Server, but the correct syntax should be:

     strSQL = "INSERT INTO [Excel 8.0;Database=" & _
              "X:\AValidPath\shipping.xls].[Shipped] SELECT  ShipDate, PONumber,
 ModelNo, Comments, " _
                 & "CustName FROM Shipped " _
                 & "WHERE status = 'P'"

Microsoft MVP (Visual Basic)