Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Now-Luanda, Angola, Originally - Anna Bay, New South Wales, Australia
    Posts
    238
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Losing characters (2003 allSPs/SBS 2003)

    I have an ODBC link to an Accounting package and do significant data manipulation/reporting via Access.
    Because the ODBC is fairly slow, I use Make Table queries to create updated tables of the important data on a regular basis and use these for queries/reports etc.
    Many of the stock items in the stock table have "unusual" Characters eg the small degree symbol ( as in 90 degree bend). When I preview my Make Table results, these are fine. However when I actually make the table, they are converted to ?. I am not changing fonts etc . Running on Small Business Server 2003. Any suggestions
    TIA
    Steve
    Dummy from Down Under <img src=/S/flags/Australia.gif border=0 alt=Australia width=30 height=18>

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Losing characters (2003 allSPs/SBS 2003)

    It sounds like the Accounting package may be using a different character set than your Access default. You might try changing the font setting on the Tools / Options DataSheet to a UniCode font such as Arial Unicode MS and see if that helps the situation. In any event, I don't think the characters are actually being dropped or changed - just the display of the character is altered.

    I'm also curious about the ODBC link and it's behavior. What accounting system are you linking to? Some accounting systems have optional backend choices that will improve performance dramatically.
    Wendell

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Losing characters (2003 allSPs/SBS 2003)

    I find it odd that the preview of the Make Table query would show the symbols correctly, but that they wouldn't be shown properly in the table. It still could be a font issue, you'll have to make sure the fonts for both are the same. If that doesn't solve the problem, you will have to see exactly what is behind that "?" symbol. Find 1 record that has such a character in a specific field. Create a simple select query that returns only that record. Knowing that say the character is in the 3rd position in the field, use this expression to return the character code of that character. If it is <128, then the MakeTable query converted the data!
    ASC( Mid(yourfield,3,1) )
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Now-Luanda, Angola, Originally - Anna Bay, New South Wales, Australia
    Posts
    238
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Losing characters (2003 allSPs/SBS 2003)

    Thanks for the advice - The fonts are not changed (by me) in any way.
    I ran your suggested query and got an Ascii code of 63.
    BTW this gets wierder. Exactly the same MakeTable query run using the same ODBC on the same Accounting File but in XP rather than SBS2003 works fine
    Thanks again
    Steve
    Dummy from Down Under <img src=/S/flags/Australia.gif border=0 alt=Australia width=30 height=18>

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Now-Luanda, Angola, Originally - Anna Bay, New South Wales, Australia
    Posts
    238
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Losing characters (2003 allSPs/SBS 2003)

    Wendell,
    Your suggestion re Unicode worked. Thank you very much - what does that tell me eg if I then produce reports using differnt fonts, will I screw it up again

    BTW this gets wierder (or is that more wierderest). Exactly the same MakeTable query run using the same ODBC on the same Accounting File but in XP rather than SBS2003 works fine

    The accounting package is called MYOB, used to be reasonably popular in the States (I am in Australia) but the American version was sold to Quicken a couple of years ago and I think it is no longer available there. One of the main reasons for buying this particular package was the ODBC link as the clients business is too complex for most other reasonably priced accounting packages (eg Quick Books) to handle. I have managed to design workarounds in conjunction with the ODBC and Access to handle complex consignment stock/foreign currency issues and overcome reporting limitations in the base package

    I would be really interested in speeding up the ODBC as I could then be using "live" data so appreciate any suggestions. The make table process was a dumb Accountants (me) way of overcoming the speed limitations and, while it requires some time investment when updating the file, I do actually get a fairly useable result
    TIA
    Steve
    Dummy from Down Under <img src=/S/flags/Australia.gif border=0 alt=Australia width=30 height=18>

  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Losing characters (2003 allSPs/SBS 2003)

    How do you actually get the special characters into MYOB?
    Or is your MYOB stock list created somewhere else and imported?

    I have done a couple of jobs using the ODBC link to MYOB for similar reasons to yours. I have found the speed a bit slow, but tolerable, so I have not had to resort to your workaround.
    Regards
    John



Posting Permissions

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