PageRenderTime 23ms CodeModel.GetById 16ms RepoModel.GetById 0ms app.codeStats 0ms

/Access-VBA/articles/0d71f4f1-efc1-127e-5edc-263a3a2a30fb.md

https://gitlab.com/mixedup/VBA-content
Markdown | 258 lines | 121 code | 137 blank | 0 comment | 0 complexity | 1fd127db09f4932d2d711772fe563864 MD5 | raw file
  1. # Insert, Update, and Delete Records From a Table Using Access SQL
  2. ## Inserting Records into a Table
  3. There are essentially two methods for adding records to a table. The first is to add one record at a time; the second is to add many records at a time. In both cases, you use the SQL statement ** [INSERT INTO](http://msdn.microsoft.com/library/D3E44258-79F2-CABA-8629-BDE03F898F2D%28Office.15%29.aspx)** to accomplish the task. **INSERT INTO** statements are commonly referred to as append queries.
  4. To add one record to a table, you must use the field list to define which fields to put the data in, and then you must supply the data itself in a value list. To define the value list, use the **VALUES** clause. For example, the following statement will insert the values "1", "Kelly", and "Jill" into the CustomerID, Last Name, and First Name fields, respectively.
  5. ```
  6. INSERT INTO tblCustomers (CustomerID, [Last Name], [First Name])
  7. VALUES (1, 'Kelly', 'Jill')
  8. ```
  9. You can omit the field list, but only if you supply all the values that record can contain.
  10. ```
  11. INSERT INTO tblCustomers
  12. VALUES (1, Kelly, 'Jill', '555-1040', 'someone@microsoft.com')
  13. ```
  14. To add many records to a table at one time, use the **INSERT INTO** statement along with a ** [SELECT](http://msdn.microsoft.com/library/A5C9DA94-5F9E-0FC0-767A-4117F38A5EF3%28Office.15%29.aspx)** statement. When you are inserting records from another table, each value being inserted must be compatible with the type of field that will be receiving the data.
  15. The following **INSERT INTO** statement inserts all the values in the CustomerID, Last Name, and First Name fields from the tblOldCustomers table into the corresponding fields in the tblCustomers table.
  16. ```
  17. INSERT INTO tblCustomers (CustomerID, [Last Name], [First Name])
  18. SELECT CustomerID, [Last Name], [First Name]
  19. FROM tblOldCustomers
  20. ```
  21. If the tables are defined exactly alike, you can leave out the field lists.
  22. ```
  23. INSERT INTO tblCustomers
  24. SELECT * FROM tblOldCustomers
  25. ```
  26. ## Updating Records in a Table
  27. To modify the data that is currently in a table, you use the ** [UPDATE](http://msdn.microsoft.com/library/08F9C3D6-C020-ECF1-5748-43B93A76DFBB%28Office.15%29.aspx)** statement, which is commonly referred to as an update query. The **UPDATE** statement can modify one or more records and generally takes this form.
  28. ```
  29. UPDATE table name
  30. SET field name = some value
  31. ```
  32. To update all the records in a table, specify the table name, and then use the **SET** clause to specify the field or fields to be changed.
  33. ```
  34. UPDATE tblCustomers
  35. SET Phone = 'None'
  36. ```
  37. In most cases, you will want to qualify the **UPDATE** statement with a ** [WHERE](http://msdn.microsoft.com/library/67E4CAED-6512-E8BD-39D0-6DCA18114B18%28Office.15%29.aspx)** clause to limit the number of records changed.
  38. ```
  39. UPDATE tblCustomers
  40. SET Email = 'None'
  41. WHERE [Last Name] = 'Smith'
  42. ```
  43. ## Deleting Records from a Table
  44. To delete the data that is currently in a table, you use the ** [DELETE](http://msdn.microsoft.com/library/64C235BC-5B1A-0A33-714A-9933BA7A81E5%28Office.15%29.aspx)** statement, which is commonly referred to as a delete query. This is also known as truncating a table. The **DELETE** statement can remove one or more records from a table and generally takes this form:
  45. ```
  46. DELETE FROM table list
  47. ```
  48. The **DELETE** statement does not remove the table structureonly the data that is currently being held by the table structure. To remove all the records from a table, use the **DELETE** statement and specify which table or tables from which you want to delete all the records.
  49. ```
  50. DELETE FROM tblInvoices
  51. ```
  52. In most cases, you will want to qualify the **DELETE** statement with a **WHERE** clause to limit the number of records to be removed.
  53. ```
  54. DELETE FROM tblInvoices
  55. WHERE InvoiceID = 3
  56. ```
  57. If you want to remove data only from certain fields in a table, use the **UPDATE** statement and set those fields equal to **NULL**, but only if they are nullable fields.
  58. ```
  59. UPDATE tblCustomers
  60. SET Email = Null
  61. ```
  62. ## Inserting Records into a Table
  63. There are essentially two methods for adding records to a table. The first is to add one record at a time; the second is to add many records at a time. In both cases, you use the SQL statement ** [INSERT INTO](http://msdn.microsoft.com/library/D3E44258-79F2-CABA-8629-BDE03F898F2D%28Office.15%29.aspx)** to accomplish the task. **INSERT INTO** statements are commonly referred to as append queries.
  64. To add one record to a table, you must use the field list to define which fields to put the data in, and then you must supply the data itself in a value list. To define the value list, use the **VALUES** clause. For example, the following statement will insert the values "1", "Kelly", and "Jill" into the CustomerID, Last Name, and First Name fields, respectively.
  65. ```
  66. INSERT INTO tblCustomers (CustomerID, [Last Name], [First Name])
  67. VALUES (1, 'Kelly', 'Jill')
  68. ```
  69. You can omit the field list, but only if you supply all the values that record can contain.
  70. ```
  71. INSERT INTO tblCustomers
  72. VALUES (1, Kelly, 'Jill', '555-1040', 'someone@microsoft.com')
  73. ```
  74. To add many records to a table at one time, use the **INSERT INTO** statement along with a ** [SELECT](http://msdn.microsoft.com/library/A5C9DA94-5F9E-0FC0-767A-4117F38A5EF3%28Office.15%29.aspx)** statement. When you are inserting records from another table, each value being inserted must be compatible with the type of field that will be receiving the data.
  75. The following **INSERT INTO** statement inserts all the values in the CustomerID, Last Name, and First Name fields from the tblOldCustomers table into the corresponding fields in the tblCustomers table.
  76. ```
  77. INSERT INTO tblCustomers (CustomerID, [Last Name], [First Name])
  78. SELECT CustomerID, [Last Name], [First Name]
  79. FROM tblOldCustomers
  80. ```
  81. If the tables are defined exactly alike, you can leave out the field lists.
  82. ```
  83. INSERT INTO tblCustomers
  84. SELECT * FROM tblOldCustomers
  85. ```
  86. ## Updating Records in a Table
  87. To modify the data that is currently in a table, you use the ** [UPDATE](http://msdn.microsoft.com/library/08F9C3D6-C020-ECF1-5748-43B93A76DFBB%28Office.15%29.aspx)** statement, which is commonly referred to as an update query. The **UPDATE** statement can modify one or more records and generally takes this form:
  88. ```
  89. UPDATE table name
  90. SET field name = some value
  91. ```
  92. To update all the records in a table, specify the table name, and then use the **SET** clause to specify the field or fields to be changed.
  93. ```
  94. UPDATE tblCustomers
  95. SET Phone = 'None'
  96. ```
  97. In most cases, you will want to qualify the **UPDATE** statement with a ** [WHERE](http://msdn.microsoft.com/library/67E4CAED-6512-E8BD-39D0-6DCA18114B18%28Office.15%29.aspx)** clause to limit the number of records changed.
  98. ```
  99. UPDATE tblCustomers
  100. SET Email = 'None'
  101. WHERE [Last Name] = 'Smith'
  102. ```
  103. ## Deleting Records from a Table
  104. To delete the data that is currently in a table, you use the ** [DELETE](http://msdn.microsoft.com/library/64C235BC-5B1A-0A33-714A-9933BA7A81E5%28Office.15%29.aspx)** statement, which is commonly referred to as a delete query. This is also known as truncating a table. The **DELETE** statement can remove one or more records from a table and generally takes this form:
  105. ```
  106. DELETE FROM table list
  107. ```
  108. The **DELETE** statement does not remove the table structureonly the data that is currently being held by the table structure. To remove all the records from a table, use the **DELETE** statement and specify which table or tables from which you want to delete all the records.
  109. ```
  110. DELETE FROM tblInvoices
  111. ```
  112. In most cases, you will want to qualify the **DELETE** statement with a **WHERE** clause to limit the number of records to be removed.
  113. ```
  114. DELETE FROM tblInvoices
  115. WHERE InvoiceID = 3
  116. ```
  117. If you want to remove data only from certain fields in a table, use the **UPDATE** statement and set those fields equal to **NULL**, but only if they are nullable fields.
  118. ```
  119. UPDATE tblCustomers
  120. SET Email = Null
  121. ```