Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Mar 2003
    Posts
    22

    Exclamation Unanswered: 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.

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    Sounds like an Access problem to me. Can you bypass Access and load the data directly into the server?
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    Mar 2003
    Posts
    22

    Import Issues

    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.

  4. #4
    Join Date
    Mar 2003
    Posts
    22
    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.

  5. #5
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    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.
    Paul Young
    (Knowledge is power! Get some!)

  6. #6
    Join Date
    Mar 2003
    Posts
    22
    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.

  7. #7
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    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.
    Paul Young
    (Knowledge is power! Get some!)

  8. #8
    Join Date
    Mar 2003
    Posts
    22
    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.
    Attached Thumbnails Attached Thumbnails arerror.bmp  

  9. #9
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    It seems odd that you get a jet error. Are you using the dBase 5 driver or the ODBC driver?
    Paul Young
    (Knowledge is power! Get some!)

  10. #10
    Join Date
    Mar 2003
    Posts
    22

    Import

    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.

  11. #11
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    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.
    Paul Young
    (Knowledge is power! Get some!)

  12. #12
    Join Date
    Mar 2003
    Posts
    22
    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.

  13. #13
    Join Date
    Feb 2003
    Posts
    109

    ADP

    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..
    Access 2002 ADP Rocks my World

    Long live SQL Server and 64bit Windows!!!

  14. #14
    Join Date
    Mar 2003
    Posts
    22

    Re: ADP

    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.

  15. #15
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    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.
    Paul Young
    (Knowledge is power! Get some!)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •