Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Nov 2001
    Location
    Texas, USA
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Linked ODBC table (2003)

    I am trying to pull data from a very large table that I link to through ODBC. I can't find any way to force a numeric field to change to a text field. It contains ID numbers, some of which start with leading zeros or alpha characters, and I need to link that field to similar data in other imported tables where the field is a text field. Is there any way I can force that change in a linked table? (There are tens of millions of rows so importing the ODBC table really is not an option.)
    Thanks for any suggestions- you all are an incredible resource!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Linked ODBC table (2003)

    As far as I know, there is no way to change the data type in linked tables.
    I think that Access (or the ODBC driver) looks at the first few records to determine the data type for each field, so a trick I sometimes use is to insert dummy records at the beginning of the external table in which each field has a value of the correct data type. Then, use a query to exclude the dummy records from being used. Of course, I don't know if this is possible in your situation.

  3. #3
    Star Lounger
    Join Date
    Nov 2001
    Location
    Texas, USA
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linked ODBC table (2003)

    Hans,
    Thanks for the reply. There is no way to insert dummy records, unfortunately. I'll try to attack it some other way.
    Judy

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Linked ODBC table (2003)

    Do you wish to do this i queries? If so when you reference the field i questio simply do the following:

    "" & Fieldname as Alias

    The concatenation of "" prior to the fielname causes the concatenated to be a text field.

  5. #5
    Star Lounger
    Join Date
    Nov 2001
    Location
    Texas, USA
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linked ODBC table (2003)

    Pat,
    Yes, I am running a query using the two tables, linked by this unique identifier field. One table has the ID as a numeric field, and the other table has it as a text field. That's where Access says it can't do it- becasue the fields aren't the same.
    When you said to write that statement, what is the best way to do that? Where should that statement (""&[ID] as alias) be written?
    Thanks so much for your help on this.
    Judy

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Linked ODBC table (2003)

    The idea is to create a query based on the linked table. In addition to the fields from the table, create a calculated column (field) in the query:
    <code>
    TextID: "" & [ID]
    </code>
    Save this query. Use this query instead of the table in queries in which you want to link to other tables. Link on the TextID field.

  7. #7
    Star Lounger
    Join Date
    Nov 2001
    Location
    Texas, USA
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linked ODBC table (2003)

    Fantastic! Thank you very much. It worked like a charm.
    Judy

Posting Permissions

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