Learning SQL (1 Viewer)

Drand

Registered User.
Local time
Tomorrow, 09:10
Joined
Jun 8, 2019
Messages
179
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

Nifty Access Guy
Staff member
Local time
Today, 22:10
Joined
Jul 9, 2003
Messages
16,245
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!


 
Last edited:

cheekybuddha

AWF VIP
Local time
Today, 22:10
Joined
Jul 21, 2014
Messages
2,237
Basic UPDATE query syntax:
SQL:
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.
SQL:
UPDATE tblTest
SET
  TextFld = 'Some text',
  NumberFld = 123,
  DateFld = #2020-06-13#
WHERE ID = 999
;

hth,

d
 
Last edited:

cheekybuddha

AWF VIP
Local time
Today, 22:10
Joined
Jul 21, 2014
Messages
2,237
>> Update Field "Active" in Table "tblStock" where "Stocknumber = myVariable "StockNo"". <<

To convert into a VBA string you need to concatenate the pieces together:
Code:
  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:
Code:
           "WHERE Stocknumber = '" & StockNo & "'; "
 
Last edited:

Drand

Registered User.
Local time
Tomorrow, 09:10
Joined
Jun 8, 2019
Messages
179
>> Update Field "Active" in Table "tblStock" where "Stocknumber = myVariable "StockNo"". <<

To convert into a VBA string you need to concatenate the pieces together:
Code:
  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:
Code:
           "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
 

Users who are viewing this thread

Top Bottom