Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2007
    Posts
    15

    Unanswered: Insert Access into SQL? Data Type Issue?

    I have data like: 000000, 000002, 000017, etc... in an Access column.
    I need to run an Append Query to send the data into SQL 2000.
    Everytime I try it, no matter what I do.. the data always ends up trimmed down in SQL... like 0, 2, 17, etc...

    I've tried changing my SQL datatype to varchar, and nchar (same problem).
    My table in Access is "text" format.

    I am sending the data to SQL via a Query View (but i doubt that's the problem).


    Any Ideas?
    Thanks!!

  2. #2
    Join Date
    May 2008
    Location
    Raleigh, NC
    Posts
    151
    I'm assuming, and yes I know what assume means, that the column in the SQL table is set to varchar or nchar. Try passing the Access value inside of a single quote such as '0000' or '00002'. I think you can define this in query with something like Expr1 = "'" & FieldName & "'".

    Hope this helps
    --If its free, take it for what its worth!

  3. #3
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    I tried to duplicate that, and it worked fine for me.
    Can you post the SQL that your Query Designer is generating?
    Inspiration Through Fermentation

  4. #4
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    Is the Access database an ADP or and MDB? Are the tables linked via ODBC in an MDB? If so I have noticed that whenever you make field data type changes in SQL Server you have to relink to the tables in SQL Server, otherwise Access is not updated. So if you began with a Int data type then changed it to a *char data type without relinking it would still convert it to a number before inserting the data (I believe).

  5. #5
    Join Date
    Apr 2007
    Posts
    15
    heck yeah.
    That is great.

    Deleting, and re-creating the linked table (via Access) did the trick!!
    Thanks sooooo much!!
    I've been working on this like all day. Sheesh!!

  6. #6
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    You can refresh the link without deleting the the table.
    In the database window, just right click over a table, and go to Refresh Linked Tables (I think that's the text?), then select all the tables you want to refresh and click ok. It's a little easier.
    Inspiration Through Fermentation

Posting Permissions

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