0

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?

Improve this question
3
  • 1
    What line is it throwing the error on? Can you try adding "option explicit" to the top of your code and see if it still compiles? Commented Jul 21, 2009 at 18:42
  • Added information to answer your questions. Commented Jul 21, 2009 at 18:46
  • Did you try running the query directly (outside of your code). It might shed some light on the problem. Commented Jul 21, 2009 at 18:50

4 Answers 4

Reset to default
3

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

This turned out to be the issue. The source (Records_New) was a stored Access Query that contained all the records in a table that did not exist in the Records table (based on Records having a Null ID auto number). The INSERT tried to apply a Null auto number, which isn't allowed. Hiding the result of the ID number check fixed the issue.
1

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.

Improve this answer

2 Comments

Pasting in the SQL query gives an error dialog with the same message that was trapped in the code. The help on this error doesn't provide any explanation, since I can't figure out what's been set to Null in the first place.
In that case, I suggest rewriting the the query to be more explicit about the exact fields in the source and destination table. Also double/triple check that none of the fields in the destination table disallow nulls, and that the field types are the same in both tables.
0

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)"];")
Improve this answer

7 Comments

What are the ns for? I've never seen that in SQL syntax before?
It's a table alias, so you know you're getting * from the RECORDS_NEW table without concern that it might take from RECORDS instead or as well.
Your syntax gives a new error: @151343: Error # & Description: 3075, Syntax error in query expression 'SELECT n.* FROM Records_New n'.
Try without the table alias - it's a force of habit, sorry.
Using VALUES here won't help, since its only for a single-record INSERT. VALUES expects a list of values, one for each field, not a list of records like the SELECT would return.
|
-1

Maybe Timer needs parens?

QueryTimer = Timer()
Improve this answer

2 Comments

Timer is a VBA builtin that returns fractions of a second since 1/1/1970. Its used in other places without issue. The iffy line is the DoCmd.RunSQL line.
Timer Function: "Returns a Single representing the number of seconds elapsed since midnight...In Microsoft Windows the Timer function returns fractional portions of a second" (office.microsoft.com/en-us/access/HA012289231033.aspx) Note it can be used as a expression in a Access database engine query.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.