Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Warrington, Cheshire
    Posts
    355
    Thanks
    3
    Thanked 0 Times in 0 Posts

    ODBC Invalid character (97 sr XP Pro)

    A new database has been installed locally.
    ODBC compliant
    I can connect and use MS Queries, but when I am trying the same in access I'm getting:
    "The database engine can't find 'dbo_ZID00001'. Make sure it is a valid parameter or alias name, that it doesn't include invalid characters or punctuation, and that the name isn't too long"

    The table name I'm trying to connect to is dbo.ZID00001 and I'm guessing that it may be the full stop that's stopping it, but it's OK with other tables.

    Suggestions from anyone?
    TIA
    Alan
    Cheshire

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

    Re: ODBC Invalid character (97 sr XP Pro)

    How are you trying to connect to it? If you are using the Menu File/Get External Data/Link Tables and selecting ODBC data sources, then the name should be displayed as one of the tables you can link to in the ODBC database. If you are trying to use a connect string (a DNS-less connection) then the problem may be one of invalid characters. Is the ODBC database SQL Server by chance? It appends the dbo_ string to all table names.
    Wendell

  3. #3
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Warrington, Cheshire
    Posts
    355
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: ODBC Invalid character (97 sr XP Pro)

    Thanks Wendell,
    Trying to link to table using FileGet External DataLink Tables...
    Select the ODBC data source, name is selectable (real word??)
    Yep, ODBC is on SQL Server.
    Yep, dbo_ is appended to the couple of tables which I was able to link to.

    TIA
    Alan

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

    Re: ODBC Invalid character (97 sr XP Pro)

    You're seeing something I've not experienced. Can you look at the table using the Enterprise Manager for SQL Server and see what it thinks the table name is? If the name in SQL Server is "dbo.ZID00001" thats rather unusual. Is it perhaps a table that begins with the full-stop character? It may also be that you need special permissions to attach to that table - SQL Server can do that through it's security feature.
    Wendell

  5. #5
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Warrington, Cheshire
    Posts
    355
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: ODBC Invalid character (97 sr XP Pro)

    The name (through enterprise manager) for the table is ZID00001, the owner is dbo, and the properties for the table are set to public access to all options.
    Strangely, I can return the data via MSQuery, but the access connection just doesn't want to know.
    With the number of rows of data steadily increasing, I'm having to filter the msquery to return batches of data rather than all at once.
    TIA
    Alan

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

    Re: ODBC Invalid character (97 sr XP Pro)

    If enterprise manager shows the table name as ZID00001, but you see it as dbo.ZID00001 in MS Query then something is definitely strange. Is this a commercial application, or is it something your IT department has deployed? There are stored procedures that will let you display information about the table in a text format, and will also allow you to rename a table, but in your situation you definitely don't want to do the latter. One thing you might be able to do is to create a view on the table and connect to the view via ODBC.
    Wendell

  7. #7
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Warrington, Cheshire
    Posts
    355
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: ODBC Invalid character (97 sr XP Pro)

    Commercial app. Off the shelf scanning application.
    Agree with not wanting to rename the table.
    Create view on table? Didn't realise I could do that, but,.....just tried it and it's a go, go, go!
    Thanks for the support Wendell. Sometimes just need to bounce comments/ideas and it pays off.
    Bit strange about seeing in Excel but not in Access though.

    Lounge 1; IT Dept 0

    Alan
    cheshire (sunny, but impending storms)

Posting Permissions

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