Learning SQL

[Drand]

Hi again

Over the past 18 months or so I have been gradually developing Access/VBA skills, all of which have been self taught or with a lot of help from this forum!

I am now at a stage where I would like to be able to write SQL statements but am struggling a bit. My main issue seems to be with syntax!

I have looked at Google examples and have also looked at the SQL behind queries but to not seem to be able to convert that successfully.

In my application, I have a number of update queries, but it seems to me that writing an SQL statement would be simpler and cleaner so I am keen to embark on learning this side of development.

Rather than create an update query I would like to write a statement that updates a field in a table based on, for example:

Table: "tblStock"
Field to update: "Active" (Yes or No)
Criteria: "StockNumber" in table = "StockNo" (A variable, integer, in my on click event)

So, I am trying to end up with is something like:

Update Field "Active" in Table "tblStock" where "Stocknumber = myVariable "StockNo"".

I would appreciate any assistance how to write this in VBA including Dim statements and DoCmd options to run the SQL to achieve this.

Hope this make sense!

Many thanks

David

 

[Uncle Gizmo]

I would like to be able to write SQL statements but am struggling a bit.

You have reminded me of a recent post I made here:-

I can't write SQL Statements.

Extract:-
I, and I suspect many other MS Access developers struggle to write SQL Statements. The reason? they seldom have to write them, MS Access has a marvellous interface for making it very simple and easy to create an SQL Statement hence as a developer I rarely have to sit down and work out how to build one, in fact, when I do, I often find myself searching for examples with Google.

In this YouTube Video I demonstrate how you can build an SQL Statement without writing any SQL!

 

[cheekybuddha]

Basic UPDATE query syntax:

UPDATE TableName
SET
  Field1 = NewValue[, Field2 = NewValue[, FieldN = NewValue]]
[WHERE Condition]
;

(Square brackets mean content is optional.)

Remember the new values must be appropriately delimited according to their datatypes.

eg.

UPDATE tblTest
SET
  TextFld = 'Some text',
  NumberFld = 123,
  DateFld = #2020-06-13#
WHERE ID = 999
;

hth,

d

 

[cheekybuddha]

>> Update Field "Active" in Table "tblStock" where "Stocknumber = myVariable "StockNo"". <<

To convert into a VBA string you need to concatenate the pieces together:

  Dim strSQL As String

  strSQL = "UPDATE tblStock " & _
           "SET Active = True " & _
           "WHERE Stocknumber = " & StockNo & "; "

If Stocknumber is a text field then the WHERE clause will be:

           "WHERE Stocknumber = '" & StockNo & "'; "

 

[Deleted member 147267]

Drand, lots of computer related learning (html, css, php, sql, etc.) here
https://www.w3schools.com/sql/

 

[Drand]

>> Update Field "Active" in Table "tblStock" where "Stocknumber = myVariable "StockNo"". <<

To convert into a VBA string you need to concatenate the pieces together:

  Dim strSQL As String

  strSQL = "UPDATE tblStock " & _
           "SET Active = True " & _
           "WHERE Stocknumber = " & StockNo & "; "

If Stocknumber is a text field then the WHERE clause will be:

           "WHERE Stocknumber = '" & StockNo & "'; "

Thanks all for the help. This is just what I needed. This is so much simpler than creating update queries all the time!

Looks like I will be able to tidy up quite a few areas of my application by using this.

Cheers

 

[cheekybuddha]

You're welcome! We're all pleased to help.

If you are doing a large amount of building SQL strings in VBA you may wish to investigate this thread by AWF guru @MajP, which shows
how to handle parameters with ease