compuzak1
asked on
Problems with DoCmd.RunSQL in Access 2003 and SQL 2008 database
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).
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).
Last Comment
ASKER
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
' 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("@origLan
.Parameters.Append .CreateParameter("@newLane
.Execute
End With
cn.Close
Set cmd = Null
Set cn = Nothing
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
' 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("@origLan
.Parameters.Append .CreateParameter("@newLane
.Execute
End With
cn.Close
Set cmd = Null
Set cn = Nothing
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
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
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
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.
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.
Microsoft Access
Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.
226K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
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