"Null" value in Queries

Question

I'm trying to run code that will copy fields into a new table, moving them from a _New table to the original table. The VBA code that does this works as such:

SQLStatement = CStr("INSERT INTO " & TName & " SELECT * FROM " & TName & "_New")
Log.WriteLine "Running query with string """ & SQLStatement & """ "
QueryTimer = Timer
DoCmd.RunSQL SQLStatement
Log.WriteLine "Query time: " & (Timer - QueryTimer)

The log is just a handful of procedures in a class module I threw together. Its output on the error is

@142921:  Running query with string "INSERT INTO Records SELECT * FROM Records_New" 
@142941:  Error Date/Time: 7/21/2009 2:29:40 PM
@142941:  Error # & Description: 3162, You tried to assign the Null value to a variable that is not a Variant data type.

I can confirm that TName and SQLStatement are both valid strings at the time the SQL operation is run, and that the sources (Records and Records_New) are both valid. Option Explicit is set elsewhere in the file to avoid any confusion from typos. The error is thrown on the DoCmd line.

Why would this have a Null value, even though DoCmd.RunSQL doesn't return a value?

Answer 1

Maybe Timer needs parens?

QueryTimer = Timer()

Answer 2

Can you post the table descriptions for Records and Records_New tables? I would wager that you are trying to insert a NULL value into one of the columns of the "Records" table (and the column description is NOT NULL).

Hope this helps.

Answer 3

I think it will help if you also change the insert statement to be more specific about which columns it is inserting/selecting. You are asking for bugs by being so non-specific.

This may seem like it is non-responsive to your answer, but I suspect that the columns in the select table and destination table are either not lined up, or there is a field in the destination table that disallows null.

Try this:

In a new Query (in SQL view) paste your query "INSERT INTO Records SELECT * FROM Records_New" in and try to run it manually. I bet you get a more specific error and can troubleshoot the query there before running it with the added complexity of the code around it.

Answer 4

INSERT INTO Statement (Microsoft Access SQL)

Your SQL INSERT statement is incorrect - it should be:

INSERT INTO Records SELECT * FROM [Records_New];

Here's what you need to use:

CStr("INSERT INTO " & TName & " SELECT * FROM [" & TName & "_New)"];")