Thanks for your reply.
We took a similar approach, but continued using ADO for all queries. This function generates an insert statement that will be executed when the user hits the OK button.
function appendfromquery
parameters dest_table,source_table,where_clause
local wasconnected,newkey
wasconnected=.t.
if vartype(oConnection)<>"O"
oConnection = CreateObject("adodb.connection")
oConnection.ConnectionString="PROVIDER=SQLOLEDB;SERVER="+trim(odbcservername)+";DATABASE="+trim(odbcdatabase)+";UID="+trim(odbcusername)+";PWD="+trim(loginpass)+";"
oConnection.Open
wasconnected=.f.
endif
oCols = createobject("ADODB.Recordset")
oCols.ActiveConnection = oConnection
qcol="select col1.column_name from information_schema.columns as col1 join information_schema.columns as col2 on col1.table_name='"+trim(source_table)+"' and col2.table_name='"+trim(dest_table)+"' and col1.column_name=col2.column_name"
oCols.Open(qcol, , 1, 3)
if .not. oCols.eof
fnlist=""
do while .not. oCols.eof
if ""<>fnlist
fnlist=fnlist+", "
endif
fnlist=fnlist+oCols.Fields("column_name").value
oCols.MoveNext
enddo
retval="INSERT "+dest_table+" ("+fnlist+") SELECT "+fnlist+" FROM "+source_table+" WHERE "+where_clause
else
retval=""
endif
if .not. wasconnected
oConnection.Close
oConnection = ""
release oConnection
endif
return retval
Randy Hersom
President, Habilitation Software Inc.