Solved

Cannot find table or query error with SQL connection string in query and ODBC table

Posted on 2008-10-03
12
294 Views
Last Modified: 2012-06-27
I an getting the error cannot find the input table or query ".." when trying to run my query. The query uses  ODBC linked tables and local tables. I put the connection string in the query. The name is correct. The ODBC connection is not a trusted connection. Everything works fine in my test environment with a standard ODBC setup. These are read-only tables from another server. The tables open just fine and display data. These queries are used in reports and display fields on forms. Without the connection string, the user is asked for the login a zillion times. I attached a screen shot of the error and the SQL in the query. What am I missing? Is there another setting I have to do on either side? I don't control the server. Thanks!
Doc1-3.doc
0
Comment
Question by:gaynes
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
12 Comments
 
LVL 42

Expert Comment

by:dqmq
ID: 22636597
You seem to be missing end-quotes to terminate the connection string before the WHERE clause.
0
 
LVL 42

Expert Comment

by:dqmq
ID: 22636677
Putting connection string in the query is highly unusual; I'm not clear why you don't simply use linked tables or passthru queries.    
0
 

Author Comment

by:gaynes
ID: 22636715
The SQL is Access written. I do use linked tables, but since it isn't a trusted connection I have to use the connection string. I don't think a passthru query would act any differently.
0
The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

 
LVL 42

Expert Comment

by:dqmq
ID: 22636787
>I do use linked tables, but since it isn't a trusted connection I have to use the connection string.  

Actually, the connection string is assembled from the DSN and the query.  You can put the userid, password, and database in the DSN, then you don't need any of it in the query.  Furthermore, then you can also link to the tables without prompting for that information.

 
0
 

Author Comment

by:gaynes
ID: 22638313
That is what I did. It is in the query properties.It still gives the error message. If you look at my example you can see that.
0
 
LVL 42

Expert Comment

by:dqmq
ID: 22639815
When I look at the example, I see a userid, password and database originating in the query.   I'm suggesting that you specify those things in the DSN, instead. That way you can link to the tables via the DSN without being prompted for userid/password.  The query can just reference the tablename without the IN clause and the connection string overrides embedded in it.

 
0
 

Author Comment

by:gaynes
ID: 22640942
 The person who set it up (IT person at the client) tells me he did. Since it is not a trusted connection, it will not retain the password so has to be put here as well. The tables can be opened with no problem outside the query. They are linked via ODBC.
0
 
LVL 42

Expert Comment

by:dqmq
ID: 22641623
I apologize for haranging you so much about the DSN since it doesn't directly cause your problem.   Have you tried adding the end-quote as suggested earlier?

As for the getting the connection string out of the query: it can be done, but it's a little more complicated than I led you to believe, since the password you enter on the ODBC control panel is not saved. Let me know if you want to pursue





0
 

Author Comment

by:gaynes
ID: 22641729
The end quote is not an issue as the query was created in Access and works. The password works. The issue is that now it says it doesn't see a table that is there, is spelled correctly, and does open.
0
 
LVL 42

Expert Comment

by:dqmq
ID: 22641768
On the user's machine Open the linked table in design view and click past all the warnings that you cannot change it.  Click on the title bar an then View properties.  What appears in the Description property?




0
 

Author Comment

by:gaynes
ID: 22642360
I have to get to the client's machine. I won't be able to do that until Monday.
0
 

Accepted Solution

by:
gaynes earned 0 total points
ID: 22652481
Figured it out - the error message was valid - it was referring to the name of the table on the SQL database referred to in the connection string. The query was using the linked table name in the database, dbo_+table name. Once I corrected that, it worked. Also, in 2007, connecting them and putting in the pw even though not a trusted connection, seems to store the pw. Thanks for trying.
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question