i trying use sql in vba update data in existing table in excel (same file vba code). being written sql queries , updating intent of moving source data db shortly after rollout, there reasons why not right now. keep getting error "operation must use updateable query." sources show readonly in extended properties , not; both have been tried. statusdata named range in excel file. select statements using named range working fine using following connection string:
dbfullname = thisworkbook.path & "\" & thisworkbook.name
cnct = "provider=microsoft.ace.oledb.12.0;data source='" & dbfullname & "';" & _ "extended properties='excel 12.0;hdr=yes;imex=1';"
changes query connection include using "excel 12.0 macro" extended property , readonly=0, have been incrementally. broken code follows:
cnct = "provider=microsoft.ace.oledb.12.0;data source='" & dbfullname & "';" & _ "extended properties='excel 12.0 macro;readonly=0;hdr=yes;imex=1';" set cn = new adodb.connection cn.open connectionstring:=cnct strsql = "update statusdata set notes='ttt' [program category]='something' , [program name]='something' , [ln]='1' , [sn]='101';" cn.execute strsql, recordsaffected, adexecutenorecords
i had problems using imex in end stopped using purpose, connection string has never given me problems using ado/ace connect workbook (usually sql groupby), might need conditioning on data before making connection (i take entire table array , loop though fields making changes needed, dump sheet), easy enough in same workbook of course:
dim conn object dim spath string spath = "c:\offlinestorage\temp1.xlsx" set conn = createobject("adodb.connection") conn .provider = "microsoft.ace.oledb.12.0" .connectionstring = format("data source=" & spath & ";extended properties='excel 12.0 xml;hdr=yes';") .open end 
Comments
Post a Comment