Append Query from Access to SQL Server Backend

I am trying to run an append query from my access db to a linked BE table that is in SQL server. Whenever I try to run the append query I get an error stating "You tried to assign the Null value to a variable that is not a Variant data type". What exactly does this mean? Every field I am trying to append data to has a value so I'm not sure what it means by this error. Can anyone shed some light on this?
Lawrence SalvucciInformation Technology ManagerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
Sure about your source data? Cause normally it means exactly that: some row contains a NULL in one of its columns.

Other possibilities: You're inserting into a view, where not all columns are used. Your table has a trigger.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
I'll double check the data but I went through it already and every field had a value. There are some fields that I am not appending data to but those fields are NOT in the append query. If those fields are set to NOT "Allow Nulls" in SQL would it not allow the record to be appended? I wondered if that could be the cause too. I will double check the data again and make sure every field has a value in it. I am inserting it into an actual table, not a view. I know that for a fact. If my destination table had a trigger, would there be a way to disable the trigger from access in order to run the append?
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
If your using any VBA expressions (i.e. DateDiff() for example), make sure your passing the right arguments to them

Also, if your doing this in code with VBA to build the SQL string, it means at some point your trying to put a NULL into a variable that is other than the variant type (such as a string).   In VBA, the only data type that can handle a NULL is the variant type.

Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Lawrence SalvucciInformation Technology ManagerAuthor Commented:
I am not doing this via VBA. I am using an append query straight from the queries, not via VBA.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Can you show the SQL of your append query?
Lawrence SalvucciInformation Technology ManagerAuthor Commented:

INSERT INTO dbo_qtmast2 ( fcompany, fquoteno, fackdate, fccurid, fcfactor, fcfname, fcfromno, fcfromtype, fcity, fcountry, fdatedue, fdaterecvd, fdcurdate, fdexpired, fdistno, fdsalespn, fduplicate, festimator, ffax, fjobname, fcsoldto, fcustno, fnextenum, fnextinum, fordpotent, fordtime, fphone, fprint_dt, fprinted, fquotecopy, fquotedate, fquoteto, frequestno, frevno, fsalespn, fstate, fstatus, ftype, fzip, fcusrchr1, fcusrchr2, fcusrchr3, fnusrqty1, fnusrcur1, fdusrdate1, fdisrate, fterm, fpaytype, fdeurodate, feurofctr, fusercode, fcshipto, fltotal, fclosmemo, fmstreet, fmusermemo, fsalumemo, fccontkey, flcontract, fndbrmod, ContractNu, OpportunNum, ModifiedDate, OppCrType, CreatedDate, fbilladdr )
SELECT dbo_qtmast1.fcompany, dbo_qtmast1.fquoteno, dbo_qtmast1.fquotedate, "USD" AS fccurid1, "1.00000" AS fcfactor1, dbo_qtmast1.fcfname, dbo_qtmast1.fcfromno, dbo_qtmast1.fcfromtype, dbo_qtmast1.fcity, dbo_qtmast1.fcountry, dbo_qtmast1.fdatedue, dbo_qtmast1.fquotedate, dbo_qtmast1.fdcurdate, dbo_qtmast1.fdexpired, dbo_qtmast1.fdistno, dbo_qtmast1.fdsalespn, dbo_qtmast1.fduplicate, dbo_qtmast1.festimator, dbo_qtmast1.ffax, dbo_qtmast1.fjobname, dbo_qtmast1.fcsoldto, dbo_qtmast1.fcustno, dbo_qtmast1.fnextenum, dbo_qtmast1.fnextinum, dbo_qtmast1.fordpotent, dbo_qtmast1.fordtime, dbo_qtmast1.fphone, dbo_qtmast1.fprint_dt, dbo_qtmast1.fprinted, dbo_qtmast1.fquotecopy, dbo_qtmast1.fquotedate, dbo_qtmast1.fquoteto, dbo_qtmast1.frequestno, dbo_qtmast1.frevno, dbo_qtmast1.fsalespn, dbo_qtmast1.fstate, dbo_qtmast1.fstatus, dbo_qtmast1.ftype, dbo_qtmast1.fzip, dbo_qtmast1.fcusrchr1, dbo_qtmast1.fcusrchr2, dbo_qtmast1.fcusrchr3, dbo_qtmast1.fnusrqty1, dbo_qtmast1.fnusrcur1, dbo_qtmast1.fdusrdate1, dbo_qtmast1.fdisrate, dbo_qtmast1.fterm, dbo_qtmast1.fpaytype, dbo_qtmast1.fdeurodate, dbo_qtmast1.feurofctr, dbo_qtmast1.fusercode, dbo_qtmast1.fcshipto, dbo_qtmast1.fltotal, dbo_qtmast1.fclosmemo, dbo_qtmast1.fmstreet, dbo_qtmast1.fmusermemo, dbo_qtmast1.fsalumemo, dbo_qtmast1.fccontkey, dbo_qtmast1.flcontract, dbo_qtmast1.fndbrmod, dbo_qtmast1.ContractNu, dbo_qtmast1.OpportunNum, dbo_qtmast1.ModifiedDate, dbo_qtmast1.OppCrType, dbo_qtmast1.CreatedDate, dbo_qtmast1.fbilladdr
FROM dbo_qtmast1
WHERE (((dbo_qtmast1.fquoteno)=[Forms]![frmQuoteMain]![txtRecordID]));

Open in new window

Dale FyeOwner, Developing Solutions LLCCommented:
The table you are inserting into may have fields which are not included in your APPEND query, but which will not accept NULL values.

Check the data structure of the destination table and see if there are any fields which are not contained in the APPEND query which indicate "NOT NULL"
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
Thanks, Dale. That was one of my concerns as to why it won't append to the table. Since I don't have all the fields in my append query I was thinking it wouldn't allow null values for fields that I don't have in my query. I'll review this as well.
To confirm what the others have told you, if a field is marked as required, you must include it (with a value) in your append query or set a default value to be used if you can't add the column to your append query.
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
That's what I am going to test out tomorrow. I am going to add every field that is required and then try again. Will post back with my results.
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
I found the problem. Some of my datatypes in Access were set to Yes/No when I imported the data structure from SQL. Those same datatypes were set to bit in SQL so the datatypes weren't 'clicking' together. So I changed those and made sure every field that was required had data in it and the append query is working now. Thank you to everyone for your help! I greatly appreciate it as always!!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.