assign the Null value to a variable that is not a Variant data type.

Hello Everyone,

I trying to upgrade our Alpha 4v4 Dos Database to MS SQL 2000 with Access XP front end and I have four tables that won't let import my data into them. I keep recieveing a message that says "You tried to assign the Null value to a variable that is not a Variant data type. (Error 3162)"
What can I do to get rid of this stupid error, is it a problem with Access XP or SQL 2000.Sounds like an Access problem to me. Can you bypass Access and load the data directly into the server?|||Originally posted by Paul Young
Sounds like an Access problem to me. Can you bypass Access and load the data directly into the server?

I tried that and I get a MS Jet Database Engine Error when I tried the import function, I know that in Access you can append data in, can you do that in SQL. What I mean is could I just link my Alpha tables into the SQL Database and then try to Append/Insert into my SQL tables. SQL is all new to me and I'm still in the process of learning it and it is alot different then Alphav4 and Access 97 functionality that I'm used to.|||Originally posted by Paul Young
Sounds like an Access problem to me. Can you bypass Access and load the data directly into the server?

Paul,

I can't import, append or insert. But I can copy and paste my old records into the new tables with a few error messages and only 65000 at a time. I guess that something is better than nothing. If anyone has a better way, I'm always open to try something new.|||Yes, you can append data in SQL.

There would be many ways to do this. The very first one that comes to mind would be to use DTS if you are using SQL 7/2k. DTS can connect to a variety of data sources and allows you to transform the data on the fly.

Microsoft's Books Online has some good information on this.|||Originally posted by Paul Young
Yes, you can append data in SQL.

There would be many ways to do this. The very first one that comes to mind would be to use DTS if you are using SQL 7/2k. DTS can connect to a variety of data sources and allows you to transform the data on the fly.

Microsoft's Books Online has some good information on this.
I've been using the DTS function and that is what I first tried to do my import with or even append and I still get an error message. If I import the whole table it will only bring in the structure, no data i get an error on that. THen if I try to append the data I get a different error. I'll look online and see if I can findout what I'm doing wrong. Or I'll just cut and paste since that seems to work.|||What were your errors?

DTS can be used to import a structure and/or data. Often I have found it easyier to created a db, and then use DTS to suck strucutre and all. Once the data is in I can make modifications and move the data to it's ultimate home.|||Originally posted by Paul Young
What were your errors?

DTS can be used to import a structure and/or data. Often I have found it easyier to created a db, and then use DTS to suck strucutre and all. Once the data is in I can make modifications and move the data to it's ultimate home.

If I import my data from Alpha into Access 97 and then into SQL I get an Insert error on 4 of my tables for certain Date fields that says Data Over flow invaild character value for cast specification. If I try to go directly into Alpha 4v4 which are Dbase 5 tables it just won't do it. I get a ms jet vb error and it will import nothing at all. Atleast in access it will import 32 of 36 tables.|||It seems odd that you get a jet error. Are you using the dBase 5 driver or the ODBC driver?|||Originally posted by Paul Young
It seems odd that you get a jet error. Are you using the dBase 5 driver or the ODBC driver?

Ok I changed my data source to dbase III and I was able to import my data directly form Alpha 4v4, plus I'm starting to usnderstand this DTS function. I was wondering if I can use it to just append the tables, not create them everytime. I noticed that everytime I use the wizard it wants to create the table then import the data. I want it to just Append the data now that I have the tables in SQL so that I can refreash the old data with the new until I'm ready to run everything in SQL. Can i physically change the SQL startments for that DTS function and is that possible.|||You should be able to just append data, I don't have dBase 5 to test with but when I load a CSV file into an existing table I can select Transformations and choose Append rows to destination table. That should do it for you.|||Originally posted by Paul Young
You should be able to just append data, I don't have dBase 5 to test with but when I load a CSV file into an existing table I can select Transformations and choose Append rows to destination table. That should do it for you.

Ok I can't find what your talking about "Select transformations and choose append rows?", could you do me a favor and type out the steps that you use to see if I'm going in the right direction. Appending my Bbase file and your CSV file shouldn't be that different as far as the steps go. Thank you for your help.|||ADP is the answer to all your problems.

It only deals with SQL Server-- so its much simpler than what you're talkin about..

in access 2002, you can even create a linked server-- just like how you can link to a different db in a mdb..

of course, i think that you'll need to put drivers on the SQL Server-- but thats not that big of a deal..|||Originally posted by aaron_kempf
ADP is the answer to all your problems.

It only deals with SQL Server-- so its much simpler than what you're talkin about..

in access 2002, you can even create a linked server-- just like how you can link to a different db in a mdb..

of course, i think that you'll need to put drivers on the SQL Server-- but thats not that big of a deal..

ADP, could you tell me more about it, I typed it into the help file and nothing came up.|||1. Fire up DTS
2. Fill in the data source
3. fill in the data destination click "NEXT >"
4. On the "Select Source Tables and Views" panel click on the elips "..." under Transformations.
5. Click on the "Append rows to destination table" radio button and then click on "OK"
6. Back on the "Select Source Tables and Views" panel click on "Next >"
7. On the "Save, schecule , and replicate package" panel click on "Next >".
8. You should be able to take it from here.|||Originally posted by Paul Young
1. Fire up DTS
2. Fill in the data source
3. fill in the data destination click "NEXT >"
4. On the "Select Source Tables and Views" panel click on the elips "..." under Transformations.
5. Click on the "Append rows to destination table" radio button and then click on "OK"
6. Back on the "Select Source Tables and Views" panel click on "Next >"
7. On the "Save, schecule , and replicate package" panel click on "Next >".
8. You should be able to take it from here.

Step four was the one that I wasn't finidng, thank you so much Paul, I did get it to work and now I just need to adjust it so that I won't get any errors when I try to append a date field. Thanks