Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2005
    Posts
    240

    Unanswered: Query not bringing back results after linking tables

    Hi folks,

    Can anyone tell me why one of my queries doesn't work anymore after changing from local tables in Access to linked tables on SQL Server 2000?

    The table names are exactly the same, and so are the fields in the tables.

    Only difference is the tables contain more data.

    Thanks

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Is your query throwing an error (what does it say)
    or is it not returning any data?
    Inspiration Through Fermentation

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    is it possible you have used an invalid character or invalid column name?

    I seem to rememebr that the underscore character is a no no, and SQL server is a lot stricter in how it parses things
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Healdem,

    What do you mean about the underscore being a no-no?
    I use it all the time in table and column names.
    Inspiration Through Fermentation

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by RedNeckGeek
    Healdem,

    What do you mean about the underscore being a no-no?
    I use it all the time in table and column names.

    its my understanding that you cannot use the underscore character in SQL Server queries, The Underscore character should be replaced by a dot. Leastways it was when I took over a project that was being migrated to SQL server in 99..02. Certainly Litwin/Gertz et al make reference to that in 'Access Developer 2000 Enterprise

    eg DT_MyTable.My_ColumnName
    becomes
    DT.MyTable.My.ColumnName

    In all honesty I dont know, because I dont use and underscore in my table or column names.
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Sep 2005
    Posts
    240
    Whoops, I haven't checked this post since I started it.

    I have managed to sort it out.
    For some reason because I linked the tables from SQL Server it didn't like the joins. So it wanted me to join two tables in a query, then use this query as the linked table to the original table, if that makes sense.

  7. #7
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I've used underscores quite a bit (i.e. dbo_Mytablename) and haven't had any problems. I used them because when you link in a SQL Server table, it automatically assigns a dbo_ in front of the table name. I think though that you need to "refresh" the queries. I've seen it where I bring in the same table name but my queries don't work but when I re-add the "new" table name to the query (keeping all the field names the same in the bottom or re-adding the same query fields), the query will work.
    Last edited by pkstormy; 12-19-06 at 17:51.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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