Trying to pass a variable table name thru SQL Upda

Results 1 to 2 of 2

Thread: Trying to pass a variable table name thru SQL Upda

  1. #1
    Join Date
    Dec 1969

    Default Trying to pass a variable table name thru SQL Upda

    Hello.<BR><BR>I have run into the same problem a few times and I am unable to find a solution through my own eforts and endless internet searches.<BR><BR>I have an Access 2000 DB with 50 for each state used by our client.<BR><BR>I want the user to be able to choose which state he/she wants to update.<BR><BR>I am able to pass the table name as a variable on the Open but on the update I get the following:<BR><BR><BR>Microsoft JET Database Engine error &#039;80040e14&#039; <BR><BR>Syntax error in query. Incomplete query clause. <BR><BR>I know my SQL code is correct as I am able to put a table name into the same SQL and it updates.<BR><BR>strTable = Request.Form("Table")<BR>strTable = trim(strTable)<BR><BR>response.write STRtable<BR><BR><BR>Set ObjConn = Server.CreateObject("ADODB.Connection")<BR>Set objCmd = Server.CreateObject("ADODB.Command")<BR>objConn.Op en strConnect<BR>Set objRS = Server.CreateObject ("ADODB.Recordset")<BR><BR><BR><BR><BR><BR>&#039;" """PASS TABLE AS VARIABLE AND USE STRCRITERIA<BR><BR>strOrigin = "AR"<BR>strNewCarrier1 = "TEST"<BR><BR>&#039;The variable name is accepted here<BR><BR> objRS.Open strTable,objConn, , ,adCmdTable<BR> objRS.MoveFirst<BR> strCriteria = "Origin= &#039;" &strOrigin& "&#039;"<BR> objRS.Filter = strCriteria<BR><BR><BR><BR>&#039;Not accepted in update<BR><BR><BR>strSQL = "Update &#039;" &strTable& "&#039; set Carrier1 = &#039;" &strNewCarrier1& "&#039; WHERE Origin = &#039;" &strOrigin& "&#039;"<BR><BR>objCmd.ActiveConnection = strConnect<BR>objCmd.CommandType = adCmdText<BR>objCmd.CommandText = strSQL<BR>objCmd.Execute , ,adCmdText<BR><BR>&#039;end<BR><BR>I tried the &#039; & " in different combinations, with & without spaces.<BR><BR>I checked the Access reserved words also.<BR><BR>If anyone has any knowledge of this problem any help would be greatly appreciated.<BR><BR>Thanks in advance.<BR><BR><BR>

  2. #2
    Join Date
    Dec 1969

    Default two things!

    1) *** 50 tables!!! can we say Really bad design!<BR><BR>2)If you have debugged you would have seen it<BR>strSQL = "Update [hl="red"]&#039;[/hl]" &strTable& "[hl="red"]&#039;[/hl] set Carrier1 = &#039;" &strNewCarrier1& "&#039; WHERE Origin = &#039;" &strOrigin& "&#039;" <BR><BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts