Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2004
    Location
    UK
    Posts
    82

    Question Unanswered: Use sql in vb code

    is it possible to get the same results as these two queries using vba code,
    if so can anyone help

    Query1 append
    INSERT INTO AccountInvTotal ( [ACCOUNT NAME], TOTAL, ID, [Invoice number] )
    SELECT ACCOUNTINVOICE.[ACCOUNT NAME], ACCOUNTSTOCK.TOTAL, ACCOUNTINVOICE.ID, ACCOUNTINVOICE.[INVOICE NUMBER]
    FROM ACCOUNTINVOICE AS ACCOUNTINVOICE_1, ACCOUNTINVOICE INNER JOIN ACCOUNTSTOCK ON ACCOUNTINVOICE.ID = ACCOUNTSTOCK.ID
    GROUP BY ACCOUNTINVOICE.[ACCOUNT NAME], ACCOUNTSTOCK.TOTAL, ACCOUNTINVOICE.ID, ACCOUNTINVOICE.[INVOICE NUMBER]
    HAVING (((ACCOUNTINVOICE.ID)=[forms]![accountinvoice1]![id]));

    Query2 update
    UPDATE ACCOUNTINVOICE INNER JOIN AccountInvTotal ON ACCOUNTINVOICE.ID = AccountInvTotal.ID SET ACCOUNTINVOICE.[TOTAL DUE] = [total], ACCOUNTINVOICE.OUTSTANDING = [total];

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    yes
    you can use queries in VB code, you can refer to predefined queries (ie those created int he query designer), you can alos write queries on the fly (ie decide which parameters to include

    have a look at the recordset in the help system

    decide if you want to use DAO or ADO.... similar but different libraries of accessing data
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Mar 2004
    Location
    UK
    Posts
    82
    Thanks

    don't suppose you could be a bit more specific though,

    for example

    update [table1]![field2] to "newvalue"
    where [table1]![field1] = "criteria"

    how can i do that in vb?

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    the easiest is to do a set operation... ie use a single SQL statement to perform the action this is eactly analogous to running it as a query

    eg
    dim strSQL as string
    dim rsMyTable as ADODB.Recordset

    'do some stuff to open an ADODB recordset... forget the exact syntax

    strSQL = "UPDATE <mytable> set <Mycolumun1> = <myValue1>,<Mycolumun2> = <myValue2>,.....<Mycolumunn> = <myValuen> where <MyColumnx> = ??????

    then open your recordset, again forget the deatils but its something like
    set rsmyTable.open(strSQL.........)

    afterward rmember to clean up, closing any open recodsets & freeing any objects in memory
    rsmytable.close
    set rsmytable=nothing

    you can do it in a loop, but this take a heck of a long time
    strSQL = "select <Mycolumun1>, <Mycolumun2>, .....<Mycolumunn>from <mytable>
    where <MyColumnx> = ??????;"

    open your recordset
    then find the record you want to change, note you could use a clone of the forms recordset, or even edit the current record
    with rsmytable.recordset
    .edit
    !<Mycolumun1> = <myValue1>
    !<Mycolumun2> = <myValue2>
    .....
    !<Mycolumunn> = <myValuen>
    .update
    rsmytable.movenext
    wend

    Pressing the F1 key needn't be a forbidden pastime when developing software in MS Access.
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    first -you shouldn't want to do this:
    strSQL = "UPDATE ACCOUNTINVOICE SET [TOTAL DUE] = [total], OUTSTANDING = [total] WHERE....
    why would you want the two fields to be the same?

    but if you are determined to do this strange thing,
    currentdb.execute strSQL
    will be dozens of times faster than messing with recordsets

    if you insist on doing strange things as slowly as possible, then DAO-how is:
    dim strSQL as string
    dim recs as dao.recordset
    strSQL = "SELECT [TOTAL DUE], OUTSTANDING FROM ACCOUNTINVOICE WHERE ID=" & forms!accountinvoice1.id & ";"
    set recs = currentdb.openrecordset(strSQL)
    if recs.bof and recs.eof then
    msgbox "zero records"
    else
    with recs
    .edit
    ![total due]= total
    !outstanding = total
    .update
    end with
    endif
    set recs = nothing

    an observation: when you use CAPS and [INCLUDE SPACES] in the names you use, you render the code almost unreadable.

    izy


    Ahhh - i see healdem types faster - seems that we are saying +/- the same thing tho
    Last edited by izyrider; 12-02-06 at 12:53.
    currently using SS 2008R2

  6. #6
    Join Date
    Mar 2004
    Location
    UK
    Posts
    82

    Vb Query

    The reason i need to set two fields to one value is that the total due value will be set and wont change, this is the total value due from a customer for a single invoice record.

    the outstanding value will change when payments are recieved and allocated to each invoice, the total due and outstanding can then be queried to give a total payments outstanding value for each customer.

    i have this working through queries being called up though vb but i though i might be better to do the whole thing in vb, may be you guys are right and i need to stick with the current method????

    comments please guys???


    p.s. (i now know about the spaces in the db tables and field names but i started the whole thing 2 yeas ago and have learnt more as i have had to add to the app, i know they need changing but the time involved would be enormous)
    Last edited by "L"PLATE; 12-03-06 at 07:57.

  7. #7
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    ok, i see your idea with two-fields-one-value.
    probably somewhere else you have a table with the payments, and you update outstanding each time a payment comes in.

    you are storing a calculated value so you will need to ensure that anything that might edit data in the payments table causes a recalc of outstanding. the absence of triggers in JET makes storing calculated values especially dangerous. safer (but slower) is to query payments and calculate the outstanding each time you need it.

    anyhow, back to your question: a recordset approach will certainly do the INSERT, SELECT, and UPDATE tasks you are discussing but equally certainly it will be very much slower than the SQL approach.

    recordsets can be useful - i'm playing with one right now, looping through it and copying files from recs!serverFileExt to recs!localDrivePathFileExt in a sort-of "installer" application. i wouldn't use recordsets for your task tho.

    izy
    currently using SS 2008R2

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    [QUOTE="L"PLATE]....p.s. (i now know about the spaces in the db tables and field names but i started the whole thing 2 yeas ago and have learnt more as i have had to add to the app, i know they need changing but the time involved would be enormous)[/QUOTE]

    you are going to have to pay that pain at some stage, unless you want to leave it as [URL="http://en.wiktionary.org/wiki/hospital_pass"]a hospital pass[/URL] for the person filling in your shoes when you get promoted, leave or retire.

    if you've recogninsed its aproblem,the the sooner you dio soemthing about it the less the pain will be.. honest
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Mar 2004
    Location
    UK
    Posts
    82
    The promotion part won't happen, i'm in the enviable position of owning the company.

    i use the app to run point of sale and stock control.

    Although i know what you mean, i'll have to bite the bulit one day so sooner the better???

    thanks for you opinions guys, all greatly recieved

Posting Permissions

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