Results 1 to 14 of 14
  1. #1
    Join Date
    Nov 2003
    Posts
    53

    Unanswered: Importing Access table

    hi guys,
    i have a problem here. i want to import a table from microsoft access, but i encountered an error. it says something like cannot convert into sqlserver format. I notice that the column in the access table that causes the error is actually an auto-number column with long integer. What i have to do to be able to import the table in?
    thanx

  2. #2
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618

    Re: Importing Access table

    Assuming you want it to continue to be an autonumber/identity it has to be a a numeric field of some sort (so int, big int, numeric, not sure what else is allowed to be an identity).

    You are most likely having a problem because the column is set to int and the number in your access db is more then a standard sql int. try changing it to big int and see what happens.

  3. #3
    Join Date
    Nov 2003
    Posts
    53
    u mean big int in access or sqlserver? i checked just now, access in the size field only has long integer or replication ID. What if i change it to replication ID? i'm new to sqlserver, i just know access.please help.
    thanx

  4. #4
    Join Date
    Feb 2002
    Posts
    2,232
    Are you upsizing or importing ?

  5. #5
    Join Date
    Nov 2003
    Posts
    53
    importing...from an access table. coz i am migrating to sqlserver from access, my application can run using access.

  6. #6
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    big int in sql server.

  7. #7
    Join Date
    Nov 2003
    Posts
    53
    ok then i'll try it later, thanx guys...

  8. #8
    Join Date
    Feb 2002
    Posts
    2,232
    Long integer in access is the same as int in sql. Both are 4 bytes. What are the other field definitions in that table ? Also, what is the definition of the sql server table ?

  9. #9
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    you sure on that?? I understood that that used to be the case but it changed with sql server 2k.

  10. #10
    Join Date
    Nov 2003
    Posts
    53
    i'm not sure about the bytes of the data type, but in my table there are text data type in my access table. and after convert they will change to nvarchar in sqlserver. i know this because i successfully converted a different table.

  11. #11
    Join Date
    Nov 2003
    Posts
    53
    yeah i did not declare anything for the sqlserver table because i use the import wizard and just find the path of my database. then it run into this autonumber problem.

  12. #12
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    okie, just had a look through the doco and an int (in sql) should be fine for a long (in access).

  13. #13
    Join Date
    Feb 2002
    Posts
    2,232
    Can you post the mdb with the same schema and a few records ?

  14. #14
    Join Date
    Nov 2003
    Posts
    53
    sorry, i can only post it on fri as it's christmas, so i'll let u guys know later ok!
    thanx guys and have a merry christmas!

Posting Permissions

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