Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2005
    Posts
    28

    Unanswered: Writing SQL data from Access

    Sorry, but the certified idiot returns...

    All my data retrieval is currently just done on-the-fly, with listbox.rowsource set to a SQL SELECT statement which pulls the right data from the tables according to the values I specify / click on.

    I now want to have a simple button, and when the user selects a record from the listbox, they can press the button and change one of the values, on the SQL server, which will then update the listbox contents (I assume there is a refresh command or something, or I can just run the retrieval statement again).

    Actually, halfway through writing this, I've just had a thought. When I create a recordset, I specify a Connection as part of the declaration. Does this mean that the recordset is synced? If I change the values in the recordset will that change the values on the server?

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    That depends on the type of recordset it is ...
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Dec 2005
    Posts
    28
    Aha! I did it!

    adodb.command was the key. In case there's anybody as clueless as me here who might benefit from it, I used:

    Code:
    Dim cm1 As New ADODB.Command
    cm1.ActiveConnection = Application.CurrentProject.Connection
    cm1.CommandText = "UPDATE Product SET active = '0' WHERE ProductID = '" & List14.Column(0) & "'"
    cm1.CommandType = adCmdText
    cm1.Execute
    This sets the value of the active column to false for the record(s) where the id is the same as the id of the selected entry in list14.

    Before you all start flaming me for using terrible variable names and so on, I'm still learning, so this is all just temporary. When it goes live I'll use nicer names!

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    No one here will tear you apart for variable names... well maybe, but not today.

    I do have a question about active being set to the literal string '0' though... I'm a little nitpicky that way. If that is a boolean field, set it = 0, instead of = '0'.

    Depending on how you evaluate '0', it might actually be true. 0 however, will always be false.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Dec 2005
    Posts
    28
    Thanks for the advice, will bear it in mind.

    Interestingly, the value displays in the listbox by default as "True" or "False", but if I try to set the value to either of the following:

    false
    'false'
    "false"

    ...it doesn't work! Strange world of Microsoft I guess.

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Not really, that's fairly common. True and false are usually stored at the table level as -1 (or 1, depending on platform) and 0 respectively. That's how the COMPUTER determines true or false. The plain english representation comes later, which is why it displays a bit different than it is stored.

    In some specifications, evaluating a non-boolean value for a boolean result will return true so long as a value is present. Access catches it and won't let you use a non-boolean value in a true/false expression, but it's still good practice to make sure your argument is the right type.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

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