How Do I Insert a Null Value in an Access Database?

When you need to insert a null value into a column in an Access database, you could use "" to indicate a zero-length string. However, a zero-length string is not the same as Null. To insert a Null value, simple pass the keyword Null for the column, as shown in the code example.
<% Option Explicit %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
  <title>Inserting Null in a Microsoft Access Database</title>
  Dim MyConnection
  Dim MyConnectionString
  Dim MySQLStatement
MyConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=C:\Databases\WebSite.mdb;User Id=admin;Password=" MySQLStatement = "INSERT INTO MyTable " & _ "(Column1, Column2) Values('A Value', null)" Set MyConnection = Server.CreateObject("ADODB.Connection") MyConnection.Open MyConnectionString MyConnection.Execute(MySQLStatement) MyConnection.Close() Set MyConnection = Nothing Response.Write("Record inserted successfully")

Where to Next?

Wonder where to go next? You can read existing comments below or you can post a comment yourself on this article .

Consider making a donation
Please consider making a donation using PayPal. Your donation helps me to pay the bills so I can keep running Imar.Spaanjaars.Com, providing fresh content as often as possible.

Feedback by Other Visitors of Imar.Spaanjaars.Com

On Wednesday, May 21, 2008 10:42:59 AM pavani said:
how to enter null values of textbox into ms access database?
On Wednesday, May 21, 2008 5:06:53 PM Imar Spaanjaars said:
Heuh? Isn't this what the article is about?

On Wednesday, January 6, 2010 5:30:37 PM Mitali Chaudhuri said:
I have a linked table in Access, which resides in Oracle. The field in question is a NUMBER(19,4) column in the Oracle table.

I wanted to insert a null value into this field using Access VBA code using this sql string:

strInsSQL = insert into OracleTable (PROP_APPR_LOWR_VAL)
VALUES ( Nz(Me.NET_DOM_LWR.OldValue, null))

Using "null" in the string above didn't work.

How can I insert a null value for this field from Access into the underlying linked table?

Thanks in advance.

On Wednesday, January 6, 2010 7:41:42 PM Imar Spaanjaars said:
Hi Mitali,

Difficult to say as this string doesn't look valid to me at all (no quotes) which makes it impossible to tell where you're using the null value.



Talk Back! Comment on Imar.Spaanjaars.Com

I am interested in what you have to say about this article. Feel free to post any comments, remarks or questions you may have about this article. The Talk Back feature is not meant for technical questions that are not directly related to this article. So, a post like "Hey, can you tell me how I can upload files to a MySQL database in PHP?" is likely to be removed. Also spam and unrealistic job offers will be deleted immediately.

When you post a comment, you have to provide your name and the comment. Your e-mail address is optional and you only need to provide it if you want me to contact you. It will not be displayed along with your comment. I got sick and tired of the comment spam I was receiving, so I have protected this page with a simple calculation exercise. This means that if you want to leave a comment, you'll need to complete the calculation before you hit the Post Comment button.

If you want to object to a comment made by another visitor, be sure to contact me and I'll look into it ASAP. Don't forget to mention the page link, or the QuickDocId of the document.

For more information about the Talk Back feature, check out this news item.