Query not working after SQL Server 2005 upgrade

Have you tried taking these access queries (which I assume to be passthru) and running it in sql server mgt studio? (thats if you have it installed)
are you calling any Stored procedures? have you tried compiling them again?

If the ODBC initially was created based on a .dsn file - I would recreate a new .dsn file (or you could edit it via Control Panel - Admin Tools - Data Sources (ODBC)) and point it to the new SQL Server location.

I have tried to run the query in Sql Server mgmt studio and there seems to be a problem with a "merged" field.  Before in access I had [dbo_WORK_ORDER].[BASE_ID] & "-" & [dbo_WORK_ORDER].[SUB_ID] & "/" & [dbo_WORK_ORDER].[LOT_ID] but Sql Server does not like the - or /.

I changed my existing ODBC to point to the new location and the tables are working beautifully and most of the queries are working.  The ones I'm having trouble with work sometimes and then when I go back in, they have the ODBC-failed again.  Don't understand.

Sorry had to be sure of the obvious and probably have to ask more obvious questions:

Has the permissions changed for the user within SQL Server from what it was before?
What version of Access (2003? 2007? 2010?)
Is Access in 32-bit or 64-bit?

As regards testing in SQL Server --- you have to change out the & symbol with pluses +
per - http://sqlserver2000.databases.aspfaq.com/what-are-the-main-differences-between-access-and-sql-server.html
see heading - Handling Strings which suggests to be mindful of nulls and maybe using COALESCE

Sorry I see the Tag is Access 2003.

The first link just to iterate recommends putting the port number with the servername as your connection in the DNS file which I will believe solve your problem.

Here's a highly comprehensive take on trying to solve a similar issue in finding the cause which at the end states the fix:
http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server-connect/4850/Access-2007-SQL-Server2005-connection-was-forcibly-closed
I always highly respect Mary Chipman on anything she says.

these queries that use access string concatenation i.e.  & "/"

what table are they querying from?

does it query from another query which might be a Stored Procedure call?

eg
qry1   call mysp....
qry2  select * from qry1

check that

This was a really difficult fix to find.  Eventually I had to rebuild a couple of tables and was finally able to get past the errors.  Thank you for all the helpful information on the differences between Access and SQL Server 2005.  Now I can port my queries between the two to see what the problems are.  It was strange to me that I had problems with these two tables even though I was going from 2005 to 2005.  You just never know....