ODBC Call Failed (3146) - but only on some PC's!

[SteveC24]

Hello all,

I have a most peculiar question.

I have been working on a database for months now, on and off, and it works absolutely fine on my PC. Access frontend, linked to SQL backend.

I have now tried to share this with collegues now, and am getting an odbc error in just one query. I attach a screenshot of the error - this occurs on the openform event of a command button in a form.

I have a second PC, and it also works fine on that one - but I can't make it work on several other PC's. They are all domain administrators, and have full and unrestricted access to the SQL server, and the databases on it.

Even stranger - my boss (one of the people I am trying to share it with!) has opened up the frontend, and looked at the query that is the recordset for the form in question. He cannot run it.
The query for this form is quite simple, just consists of 2 other queries, with a join between them, to show all the records from one, which don't have a matching record in the other query.
If he removes that link, or changes it to a 1-1 join, the query runs without a problem, but put it back in, and the error pops back up.

He also tried recreating the query in a new query, and it all works until he puts in the one-sided join.

VERY weird!


Any ideas anyone?


The query (with said join in place) is as follows:

SELECT qryCallOpenings.IncidentID, qryCallOpenings.PC, qryCallOpenings.User, qryCallOpenings.DateTime AS Reported, DateDiff("d",[Reported],Now()) AS TimeSinceReport, qryCallClosings.IncidentID AS ClosedID
FROM qryCallOpenings LEFT JOIN qryCallClosings ON qryCallOpenings.IncidentID = qryCallClosings.IncidentID
WHERE (((qryCallClosings.IncidentID) Is Null));

 

[namliam]

What is the SQL for the queries beeing called?

 

[SteveC24]

Sorry - here they are:

qryCallClosings:

SELECT tblIncident.IncidentID, tblIncident.PC, tblEvent.EventType, tblEvent.DateTime, tblIncident.User, tblEvent.Notes
FROM tblIncident INNER JOIN tblEvent ON tblIncident.IncidentID = tblEvent.IncidentID
WHERE (((tblEvent.EventType)=2));

qryCallOpenings:

SELECT tblIncident.IncidentID, tblIncident.PC, tblEvent.EventType, tblEvent.DateTime, tblIncident.User, tblEvent.EnteredBy, tblEvent.Notes
FROM tblIncident INNER JOIN tblEvent ON tblIncident.IncidentID = tblEvent.IncidentID
WHERE (((tblEvent.EventType)=1));

Thank you!

 

[namliam]

Sorry, I cannot see anything that might be a problem

But you had the same problem ...

Only thing I can think of.... How are you authenticating on the SQL server side? Are you using windows user or are you using a database user?

 

[SteveC24]

Authenticating with windows username.

 

[namliam]

Then you have your own environment in the SQL database, which could be the cause of your problem. Make sure that your own and the other users on the database have the same priviliges and the same settings in i.e. synonyms and (own) tables and queries.

Perhaps if you are a sysdba and they are not, that can make a big difference.

Have you tried adding dbo. [or the actual database name] infront of your table names?

 

[SteveC24]

I thought it might be something to do with permissions as you do - so I have just tried it on another PC.

This is a PC which was exhibiting the problem when I was logged in as someone else. So - I logged onto the PC as both the domain administrator, and myself, and the same problem occurs.

This seems to imply that the problem is related to the setup on the PC, rather than permissions on SQL server or the network.

The fact it mysteriously works on my 2 PC's, also rather implies it isn't something in the query makeup I would have thought?


I shall continue to investigate, but suggestions are very welcome!

 

[namliam]

Sounds to me like maybe the DSN is not setup properly??
Not connecting into the proper (default) database or something?

(grasping at straws here)

What I usually do in my databases when they have a connection like this, is to create it from code each time my database starts, that way I 1) Ensure the link is there 2) It is properly setup.
I mostly work with Oracle though and have never done this for SQL server.