Problems with DoCmd.RunSQL in Access 2003 and SQL 2008 database

compuzak1
compuzak1 used Ask the Experts™
on
I have a simple SQL statement that updates records in a table from records in the same table.  The SQL works fine in SQL 2008 Server Management Tool as a stored procedure.  When I try to run it via the DoCmd.RunSQL it gives me a runtime error 3075 with a message of a syntax error (missing operator).  I copy the exact string Access is creating and paste it into a SQL2008 script and it save it as a stored procedure and it executes perfectly.  

Obviously there is some limitations or differences between Access DoCmd.RunSQL and running a stored procedure directly in SQL 2008.  I am able to run other SQL commands but so far nothing with an INNER JOIN.

Here is the Code:
    DoCmd.SetWarnings False
    strSQL = "UPDATE  t1 " & _
             " SET     LaneIdx = t2.LaneIdx, " & _
             "         StyleIdx = t2.StyleIdx, " & _
             "         SizeIdx = t2.SizeIdx, " & _
             "         GradeIdx = t2.GradeIdx , " & _
             "         LabelIdx = t2.LabelIdx " & _
             " FROM    dbo_TblLaneCfgDet t1 " & _
             " INNER JOIN dbo_TblLaneCfgDet t2 on t2.LaneIdx = t1.LaneIdx " & _
             " AND t2.LaneCfgHdrIdx = " & OrigLaneCfgHdrIdx & _
             " WHERE   t1.LaneCfgHdrIdx = " & NewLaneCfgHdrIdx
    DoCmd.RunSQL strSQL

If someone can tell me how to fix the syntax so that it will work for Access that would work.  

If not, can someone show me the Access 2003 syntax to call a stored procedure from SQL 2008 including the connection logic:
proc:  usp_CopyLaneConfig
parm1: INT
parm2: INT

The stored procedure method is preferred, but I am having problems with the exact syntax from Acess 2003.  I call stored procedures in Visual 2008 C# without any problems, very straightforward syntax, but I am having problems in Access.  The help in Access 2003 is limited at best (at least for me).


Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016

Commented:
try this


    strSQL = "UPDATE  dbo_TblLaneCfgDet t1"
    strSQL = strSQL & " INNER JOIN dbo_TblLaneCfgDet t2 on t2.LaneIdx = t1.LaneIdx"
    strSQL = strSQL & " AND t2.LaneCfgHdrIdx = " & OrigLaneCfgHdrIdx
    strSQL = strSQL & " SET LaneIdx = t2.LaneIdx,"
    strSQL = strSQL & " StyleIdx = t2.StyleIdx,"
    strSQL = strSQL & " SizeIdx = t2.SizeIdx,"
    strSQL = strSQL & " GradeIdx = t2.GradeIdx ,"
    strSQL = strSQL & " LabelIdx = t2.LabelIdx"
    strSQL = strSQL & " WHERE t1.LaneCfgHdrIdx = " & NewLaneCfgHdrIdx

currentdb.execute strSQL, dbseechanges


Author

Commented:
I tested this and it gives:

Run-time error '3296';

Join expression not supported

How about a stored procedure?  I am having trouble connecting to the database.  I can connect via Visual 2008 C#, so the database is up.  I have SQL Server Native Client 10 driver on the ODBC connection.  I have linked to the SQL2008 data tables and can access them with no problem.  I just can't connect.  It fails on cn.open below.

 gblConnString = "Provider=SQLNCLI10.1;Data Source=myServer;User ID=MyUser;Password=myPassword;Initial Catalog=myDatabase;"
   
    ' call stored procedure to copy the configuration
    Set cn = New ADODB.Connection
    cn.Open gblConnString
    Set cmd = New ADODB.Command
    With cmd
        .ActiveConnection = cn
        .CommandType = adCmdStoredProc
        .CommandText = "usp_CopyLaneConfig"
        .Parameters.Append .CreateParameter("@origLaneCfgHdrIdx", adInteger, adParamInput, 4, OrigLaneCfgHdrIdx)
        .Parameters.Append .CreateParameter("@newLaneCfgHdrIdx", adInteger, adParamInput, 4, NewLaneCfgHdrIdx)
        .Execute
    End With
    cn.Close
    Set cmd = Null
    Set cn = Nothing

Commented:
Try putting parentheses around the JOIN clause:

strSQL = "UPDATE  t1 " & _
             " SET     LaneIdx = t2.LaneIdx, " & _
             "         StyleIdx = t2.StyleIdx, " & _
             "         SizeIdx = t2.SizeIdx, " & _
             "         GradeIdx = t2.GradeIdx , " & _
             "         LabelIdx = t2.LabelIdx " & _
             " FROM  (dbo_TblLaneCfgDet t1 " & _
             " INNER JOIN dbo_TblLaneCfgDet t2 ON t2.LaneIdx = t1.LaneIdx " & _
             " AND t2.LaneCfgHdrIdx = " & OrigLaneCfgHdrIdx & _
             ") WHERE   t1.LaneCfgHdrIdx = " & NewLaneCfgHdrIdx

Or try using comma notation:

strSQL = "UPDATE  t1 " & _
             " SET     LaneIdx = t2.LaneIdx, " & _
             "         StyleIdx = t2.StyleIdx, " & _
             "         SizeIdx = t2.SizeIdx, " & _
             "         GradeIdx = t2.GradeIdx , " & _
             "         LabelIdx = t2.LabelIdx " & _
             " FROM    dbo_TblLaneCfgDet t1, dbo_TblLaneCfgDet t2 " & _
             "WHERE  t2.LaneIdx = t1.LaneIdx " & _
             " AND t2.LaneCfgHdrIdx = " & OrigLaneCfgHdrIdx & _
             " AND  t1.LaneCfgHdrIdx = " & NewLaneCfgHdrIdx
Commented:
Hoist " AND t2.LaneCfgHdrIdx = " & OrigLaneCfgHdrIdx outside of the JOIN clause as there is no join-dependency in that predicate.

Author

Commented:
I figured out the problem with my connection string.  In Visual C# I needed to backslashes in the server name path (to allow the backslash to be interpreted correctly) and in Access it does not like that syntax.  I changed it to one backslash and it is now working.  

I will try your solutions at a later time, since I am very short of time.  I like the stored procedure solution better, I was just never able to connect.  Now I can connect.  I am sure your solution will work as well so I will accept your last post.  Thanks for your help.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial