How to update table using VBA

[sra2786]

I want to create an Update Query that I want to execute via a Command Button on my form.

Here is the SQL to update the table but it is not working. I am not sure what is wrong but I am very new to this. Also, can someone share the VBA code I need to execute this query.

UPDATE tblPartMain
SET MfrPartNumber, PartDescription
FROM (SELECT MfrPartNumber, PartDescription
FROM Catalogs
UNION
SELECT MfrPartNumber1 AS MfrPartNumber,
PartDescription1 AS PartDescription


FROM Catalogs);


Thanks.

[isladogs]

I want to create an Update Query that I want to execute via a Command Button on my form.

Here is the SQL to update the table but it is not working. I am not sure what is wrong but I am very new to this. Also, can someone share the VBA code I need to execute this query.

UPDATE tblPartMain
SET MfrPartNumber, PartDescription
FROM (SELECT MfrPartNumber, PartDescription
FROM Catalogs
UNION
SELECT MfrPartNumber1 AS MfrPartNumber,
PartDescription1 AS PartDescription
FROM Catalogs);

There appear to be several issues with your update query
1. You aren't setting the 2 fields MfrPartNumber, PartDescription to anything - what do you want them to be updated to?
2. You are trying to use a UNION query as your record source which I doubt will work
Instead run the UNION query first & save it
Check this has the correct data
Then create an UPDATE query based on the SAVED UNION query - you can use the query designer window for this part

Once you have done this, you would use either
DoCmd.RunSQL "....." or CurrentDB.Execute "....."

[ssanfu]

Something that might help you learn how to create update queries.

In a test dB, create a SELECT query that has 1 field, using the Query design grid. Execute it to see if records are returned. If the query executes and returns records, great. If not, fix the query.

So now you have a working SELECT query (1 field). In design view, go to the menu at the top "Query Tools", and change the query to an "Update Query".
Add a value to the row in the grid that is "Update To".
Switch to "SQL" view to see what the SQL for the update query would look like.
Switch back to design view and add another field. Add a value to the row in the grid that is "Update To". Now you have 2 field that would be updated.
Switch back to "SQL" view.

This query would update ALL records because there is not any criteria to limit records.
You can switch to design view and add criteria in the "Criteria" row. Switch to SQL view to see what the SQL now looks like.


I will typically create a SELECT query (with criteria), execute it to see if the correct records are returned, then change the query to an update query and add values to update the field(s) to and add criteria. I make any changes i need, then I can copy the SQL and paste it into VBA to be able to use the update query in code.

If you know SQL very well, you can just type in the SQL. For me, it is quicker to use the create query wizard to get the "bones" of the query, then modify it.

[sra2786]

Steve,
I believe I followed the steps you outlined and got this to work. Is this the correct VBA or is there a better way to update the table with the SELECTed records

Dim strSQL As String
Dim db As DAO.Database
Dim sMfrPartNumber As String
Dim sPartDescription As String
Dim rstSource As DAO.Recordset
Dim rstTarget As DAO.Recordset


Set db = CurrentDb

On Error GoTo EH

strSQL = "DELETE * from tblPartMain"
CurrentDb.Execute strSQL, dbFailOnError

strSQL = "SELECT MfrPartNumber, PartDescription"
strSQL = strSQL & " FROM (SELECT Material AS MfrPartNumber, [Material Description] AS PartDescription"
strSQL = strSQL & " FROM Master_SAP_MATERIAL"
strSQL = strSQL & " UNION SELECT Component AS MfrPartNumber,"
strSQL = strSQL & " [Component Description] AS PartDescription"
strSQL = strSQL & " FROM Master_SAP_MATERIAL) AS C;"


Set rsParts = db.OpenRecordset(strSQL)


strTable = "tblPartMain"
Set rstSource = db.OpenRecordset(strSQL)
Set rstTarget = db.OpenRecordset("tblPartMain", dbOpenDynaset)
Do While Not rstSource.EOF

sMfrPartNumber = rstSource!MfrPartNumber
sPartDescription = rstSource!PartDescription
rstTarget.AddNew
rstTarget![MfrPartNumber] = sMfrPartNumber
rstTarget![PartDescription] = sPartDescription
rstTarget.Update
rstSource.MoveNext

Loop

rstTarget.Close
db.Close

[isladogs]

You don't need to loop through a recordset to run an update.
Use an update query instead which will be a lot faster.

I outlined what to do in my previous reply:

Run the UNION query first & save it
Check this has the correct data
Then create an UPDATE query based on the SAVED UNION query - you can use the query designer window for this part

[ssanfu]

I agree with ridders52, an UPDATE query would be faster, but I don't know what you are trying to do now..... Would need to have your dB to try and understand......



About your code, there are several syntax errors and a couple of other errors. Here is what I see:

Option Compare Database  '<--EVERY module should have this line at the top
Option Explicit          '<--EVERY module should have this line at the top

Sub test()  '<< the name I used for this routine
    Dim strSQL As String
    Dim db As DAO.Database
    Dim sMfrPartNumber As String
    Dim sPartDescription As String
    Dim rstSource As DAO.Recordset
    Dim rstTarget As DAO.Recordset


    Set db = CurrentDb

    On Error GoTo EH   ' 1

    strSQL = "DELETE * from tblPartMain"
    CurrentDb.Execute strSQL, dbFailOnError

    strSQL = "SELECT MfrPartNumber, PartDescription"
    strSQL = strSQL & " FROM (SELECT Material AS MfrPartNumber, [Material Description] AS PartDescription"
    strSQL = strSQL & " FROM Master_SAP_MATERIAL"
    strSQL = strSQL & " UNION SELECT Component AS MfrPartNumber,"
    strSQL = strSQL & " [Component Description] AS PartDescription"
    strSQL = strSQL & " FROM Master_SAP_MATERIAL) AS C;"


    Set rsParts = db.OpenRecordset(strSQL)  ' 2


    strTable = "tblPartMain"   ' 3
    Set rstSource = db.OpenRecordset(strSQL)
    Set rstTarget = db.OpenRecordset("tblPartMain", dbOpenDynaset)
    Do While Not rstSource.EOF  ' 4

        sMfrPartNumber = rstSource!MfrPartNumber
        sPartDescription = rstSource!PartDescription
        rstTarget.AddNew
        rstTarget![MfrPartNumber] = sMfrPartNumber
        rstTarget![PartDescription] = sPartDescription
        rstTarget.Update
        rstSource.MoveNext

    Loop

    rstTarget.Close   ' 5
    db.Close
End Sub

1) Compile error - the Label "EH" is not defined. Missing the error handling code

2) Compile error - Variable not defined. You are trying to open a recordset named "rsParts", but "rsParts" was never declared (no Dim statement). Should delete this line.

3) Compile error - Variable not defined. You are trying to set the variable "strTable" to a value, but "strTable" was never declared (no Dim statement)/ Also, "strTable" is never used in the code, so why is it there? Should delete this line.

4) Good programming practice is to test for records BEFORE trying to use the record set. If there are no records in the record set, there will be an error.
Something like

.
.
.
If Not rstSource.BOF and Not rstSource.EOF Then
    rstSource.MoveLast
    rstSource.MoveFirst

    Do While Not rstSource.EOF
        .
        .
        .
    Loop

End if

.
.

5) You DON'T close the dB. The rule is "If you create it, destroy it, if you open it, close it". You opened 2 record sets, but only closed one. You also created 2 record sets, but didn't destroy either. And you didn't destroy the "db" object.
The code would look like

    rstTarget.Close
    rstSource..Close
    Set rstTarget = Nothing
    Set rstSource = Nothing
    Set db = Nothing
End Sub

[sra2786]

Thank you Steve and ridders52 for your help. I think I have it figured out now. I created an APPEND query from my SELECT query and that seems to work. I can use APPEND because I am deleting all of the data in the table tblPartMain before I execute the APPEND query. Here is my of my VBA code

Dim db As DAO.Database


Set db = CurrentDb

On Error GoTo EH

strSQL = "DELETE * from tblPartMain"
db.Execute strSQL, dbFailOnError

DoCmd.OpenQuery "qryAppend_tblPartMain"
DoCmd.Close acQuery, "qryAppend_tblPartMain"

Set db = Nothing

EH:
MsgBox "Error " & Err.Number & ": " & Err.Description



Here is the SQL in the query qryAppend_tblPartMain
INSERT INTO tblPartMain ( MfrPartNumber, PartDescription )
SELECT DISTINCT C.MfrPartNumber, C.PartDescription
FROM (SELECT Material AS MfrPartNumber,
[Material Description] AS PartDescription
FROM Master_MATERIAL
UNION
SELECT Component AS MfrPartNumber,
[Component Description] AS PartDescription
FROM Master_MATERIAL) AS C;

[isladogs]

Glad you got it working.

Note that using your method - DELETE followed by APPEND means you don't need to save the UNION query
This code will do the same thing more concisely:

On Error GoTo EH

Currentdb.Execute "DELETE * from tblPartMain", dbFailOnError

CurrentDB.Execute "INSERT INTO tblPartMain ( MfrPartNumber, PartDescription )
SELECT DISTINCT C.MfrPartNumber, C.PartDescription
FROM (SELECT Material AS MfrPartNumber, 
[Material Description] AS PartDescription
FROM Master_MATERIAL
UNION
SELECT Component AS MfrPartNumber, 
[Component Description] AS PartDescription
FROM Master_MATERIAL) AS C;", dbFailOnError

EH:
MsgBox "Error " & Err.Number & ": " & Err.Description

However, repeatedly emptying tables & repopulating them will cause database bloat
Using an UPDATE query would prevent that & do the update in one step

Also whichever method you use, your UNION query may be creating duplicate records.
Why do you have a table with both Material/Material Description & Component/Component Description fields?
If a record has entries in both of the field 'pairs' your UNION query will give 2 records

Suggest replace the 4 fields with 3 fields e.g. Item / ItemDescription / ItemType
The ItemType field values would be Material or Component

Doing this would probably eliminate the need for a UNION query completely

[ssanfu]

It looks to me like the table "Master_MATERIAL" has 4 fields that the OP is combining into two fields. If this is true, the UNION query would be necessary..

SELECT DISTINCT C.MfrPartNumber, C.PartDescription FROM

(SELECT Material AS MfrPartNumber,  [Material Description] AS  PartDescription FROM Master_MATERIAL
UNION
SELECT Component AS MfrPartNumber, [Component Description] AS  PartDescription FROM Master_MATERIAL)

 AS C

I would code it as:

    Dim db As DAO.Database

    Set db = CurrentDb

    On Error GoTo EH

    strSql = "DELETE * from tblPartMain"
    db.Execute strSql, dbFailOnError

    'DoCmd.OpenQuery "qryAppend_tblPartMain"
    'DoCmd.Close acQuery, "qryAppend_tblPartMain"
    db.Execute "qryAppend_tblPartMain", dbFailOnError
    
    Set db = Nothing

EH:
    MsgBox "Error " & Err.Number & ": " & Err.Description

However, repeatedly emptying tables & repopulating them will cause database bloat
Using an UPDATE query would prevent that & do the update in one step

Yes, the DELETE/ APPEND will cause some bloat; using a MAKE TABLE query would be thousands of times worse.

Since the OP is deleting all records in the table "tblPartMain", how would an UPDATE query work?
Some records that should be deleted wouldn't be and the UPDATE would fail because there were no records to be updated.




...my $0.02............

[isladogs]

Hi ssanfu

You're correct in saying why the OP uses a UNION query
However, in my last post I wrote:

Why do you have a table with both Material/Material Description & Component/Component Description fields?
If a record has entries in both of the field 'pairs' your UNION query will give 2 records

Suggest replace the 4 fields with 3 fields e.g. Item / ItemDescription / ItemType
The ItemType field values would be Material or Component

Doing this would probably eliminate the need for a UNION query completely

You also wrote:

Yes, the DELETE/ APPEND will cause some bloat; using a MAKE TABLE query would be thousands of times worse.

Since the OP is deleting all records in the table "tblPartMain", how would an UPDATE query work?
Some records that should be deleted wouldn't be and the UPDATE would fail because there were no records to be updated.

Yes I agree - but I don't believe that the OP stated that any records are deleted without replacement nor that new records are added
If that's the case, there is no reason to delete the records in the first place
So its EITHER DELETE then APPEND
Or as I'm suggesting, just UPDATE - 1 step only ... and no bloat

BTW - The other day I read an article by the DBGuy where he compared the effect of DELETING records then APPENDING with doing the same thing using MAKE TABLE.
Both methods cause database bloat but conventional wisdom is that MAKE TABLE is far worse
Surprisingly the bloat was almost identical in each case

The article & a DEMO database can be found here:
http://thedbguy.blogspot.co.uk/2016/...end-query.html

I've seen other similar tests run with huge datasets & done many times over & over => again bloat was similar in each case
However MAKE TABLE is usually MUCH faster