i working on access db, have odbc linked sql server table, , have following script run tsql query, can see tried include value access forms in query, fails run. form opened , filled data when execute script. wondering if impossible or there way of doing it? new tsql , sql server, here question. appreicate if can help. lot.
function formtest() dim qryd querydef set qryd = currentdb.createquerydef("") qryd.connect = "odbc;dsn=sqlserver;" qryd.sql = "update dbo.table1 set firstname = [forms]![testform]![datainput]" qryd.returnsrecords = false qryd.execute end function
the sql server doesn't know forms. have send data query. this:
qryd.sql = "update dbo.table1 set firstname = '" & [forms]![testform]![datainput] & "'" one thing have aware of though if there single quotes in datainput invalidate sql. security issue. either test single quotes , raise error, or replace each of them two.
the best way use parameterized query. absolutely prevent issues sql injection , performance in many cases. unfortunately, don't believe can create paramaterized query sql server using dao. have convert ado, best suited sending queries sql engine other jet.
to use ado might have add reference microsoft activex data objects opening vba code window , selecting tools -> references -> , checking box next it. code this:
dim conn1 adodb.connection dim cmd1 adodb.command dim param1 adodb.parameter rem create , open connection object. set conn1 = new adodb.connection conn1.connectionstring = "odbc;dsn=sqlserver;" conn1.open rem create command object. set cmd1 = new adodb.command cmd1.activeconnection = conn1 cmd1.commandtext = "update dbo.table1 set firstname = ?" rem create parameter object. set param1 = cmd1.createparameter(, advarchar, adparaminput, 25) param1.value = [forms]![testform]![datainput] cmd1.parameters.append param1 set param1 = nothing rem open recordset object. call cmd1.execute 
Comments
Post a Comment