ADO Mutiltable update question

[AustinL]

Hi guys,

I'm trying to load records into a recordset from a query that joins several tables. I'm getting the following error: Query connot be updated because the from clause is not a single simple query name.

Is there any way around this? I've been able to pass it through access as an update query but I really don't want to do it that way... Here is the code:

------------

UpdateGL = "SELECT billtemp_transact.adminfee, ref_codes.gl_excep_flag, billtemp_transact.glaccount, billtemp_header.vendorid, billtemp_header.ref_type_code, billtemp_header.client, billtemp_transact.ref_code, billtemp_header.posting_flag" _
& " FROM (billtemp_transact INNER JOIN billtemp_header ON (billtemp_transact.file = billtemp_header.file) AND billtemp_transact.billidentity = billtemp_header.billidentity)" _
& " INNER JOIN ref_codes ON billtemp_transact.ref_code = ref_codes.ref_code" _
& " WHERE (((billtemp_transact.adminfee = 0) AND (Ref_codes.gl_excep_flag = '0') AND (billtemp_header.posting_flag = '1') AND (billtemp_header.queueflag = '0')) OR ((billtemp_transact.adminfee = 0) AND (Ref_codes.gl_excep_flag = '0') AND (billtemp_header.posting_flag = '1') AND (billtemp_header.queueflag = '6')));"

Set rs = New ADODB.Recordset

rs.Open sUpdateGL, dbconn, adOpenKeyset, adLockOptimistic

Do While rs.EOF = False
rs!glaccount = Mid(rs("ref_type_code&quot, 1, 5) & IIf(rs("vendorid&quot = "At-CA", "30", "10&quot & IIf(Mid(rs("ref_type_code&quot, 1, 5) = "30000" _
Or Mid(rs("ref_type_code&quot, 1, 5) = "31000" Or Mid(rs("ref_type_code&quot, 1, 5) = "31010", "02", "01&quot & rs("client&quot _
& IIf(Mid(rs("ref_code&quot, 2, 1) = "2", Mid(rs("ref_Code&quot, 1, 2) & "00", Mid(rs("ref_Code&quot, 1, 4))

rs("posting_flag&quot = "C"
rs.Update
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
sUpdateGL = ""



-------------
Any help would be greatly appreciated!

- Austin

 

[AustinL]

Oops, got to cutting and pasting and I forgot to include some stuff. I'm trying to go thru the recordset and update data. I get the error when rs.update is executed.