ODBC Problem

[maxmangion]

Hi,

I normally use Access to link tables via ODBC to a database which resides on the server. I have create some databases to perform certain reports and all of these work fine, however, some of the databases work fine on the pc which were created on but i get an ODBC - connection to 'sipr' failed (clicking on the help button appears to be error 3151) when trying to run them on other computers (which all have same version of OS/Office) and all have an ODBC link.

Can anyone suggest me what might be the problem that some databases are running only on the pc which were created on?

Thanks in advance.

 

[Banana]

Will need a bit more information.

1) What kind of backend are we dealing with?
2) Are we using a DSN or not?
3) How are we connecting to the backend? Via Office LAN, I assume?
4) Please jog my memory- what is the description for 3151? (If you don't know, run this in immediate windows:

?Accesserror(3151)

and relay the return.

 

[maxmangion]

Hi Banana and thanks for the reply.

1) The system is built with Uniface & SQL Server

2) When we setup the ODBC in the control panel we setup a System DNS (SQL Server Driver)

3)Yes we connect via office LAN

4)Immediate window produced ODBC--connection to '|' failed.

Thank You

 

[Banana]

Ok, if my memory serves me, I believe what you want is a file DSN which needs to be then distributed to other PCs so they can then connect to the same server. Give it a whirl and see if it helps.

 

[oumahexi]

If you try to configure the ODBC connection can you get it to run in Test?

 

[Banana]

Ooo, forgot about that one. Good idea, oumahexi!

 

[maxmangion]

Actually all PCs in question are connected to the same server. In fact all users on pc access the database and perform their routine work.

As regards the file DSN, what is basically the difference between it and the system dns?

The reason i am asking this is because way back when the database suppliers had set up the system i was given a step-by-step guide how to create the odbc connections and the file dsn was never mentioned. In fact all PCs which have ODBC connections they all work fine, but just one particular pc have only some of the databases (not all) which they do not run on others ... could this relate to a driver version issue?

 

[Banana]

Disclaimer: This is an educated guess and as I don't normally deal with DSNs, I could be totally wrong!

System DSN is available to all users on a single PC but not to others.
User DSN is available to this particular user on the PC; other users on same PC cannot access that.
File DSN is available to anyone who has the file present on the computer. It's also easier to distribute whereas the first two require that you re-create the DSN (although I would imagine you can easily automate that)

I just want to be sure I 100% understand your situation.

You have a PC that has one or more databases that aren't normally available to others PC and you wanted to deploy those using the same DSN but it fails on other PC.

If that is indeed the case, do what oumahexi suggested- do a test connection to verify that driver is OK, then make sure that database itself is included as well; depending on the driver, you may have to explicitly select a server then a database.

 

[maxmangion]

Hi,

I think i found the problem, but before i proceed further i would like to ask for suggestion:

I went to the odbc system dsn window and it seems that the pc in question have 2 system dsn created one called sipr and another called sits both having SQL Server as driver. All the other pcs have only one system dsn the named sits

Therefore do you think that i should create another system dsn and call it sipr (as i assume it would solve the problem) but on the other hand i don't really understand why there would be the need for two system dsns

an ODBC - connection to 'sipr' failed

that's what i had in my original thread so i am assuming that the database is causing the odbc error because it is not finding a system dsn which is called sipr

 

[oumahexi]

I suggest you delete the connection called sipr, it sounds like it's causing problems. Check the sits one first and make sure you can get a successful test connection with it. Also, as a fail safe, I'd write down all the details of sipr before I delete it, just in case.

 

[Banana]

As I pointed out before, if you have two different databases, even if managed by same server, and one DSN specify the server and a database "sits", then it's good only for this database "sits"; the database "sipr" is simply not visible.

I am not sure, however, if it is possible to create a DSN and not specify a database to allow you access to more than one database managed by same server. An interesting question for me to find out.

Edit: A quick search does indeed confirm that DSN, by design, lives and dies with one database so to have multiple databases connection, you need as many DSN.

 

[maxmangion]

Hi Banana/Oumahexi,

I have created another system dsn and called it sipr and voila the databases are running fine now. Therefore all of the databases are running on all pcs without problems ... the only thing is that all pcs have 2 system dsn now instead of 1.

As regards the suggestion to remove the sipr, i was afraid doing so because since it was the only pc which was running those databases, i didn't want to remove it and i might end up with some databases not running at all.

Basically what i am assuming was happening is that since the original pc has two system dns, when the original user linked the tables from the machine data source screen, sometimes she chose sipr and other times sits, so since the other pcs did not have the sipr one all those databases were failing the connection.

so for those databases using the sipr, do you think i can remove the tables, relink them and this time i choose sits and eventually i can end up with one system dsn ?

Thanks

 

[Banana]

when the original user linked the tables from the machine data source screen, sometimes she chose sipr and other times sits,

The user gets to choose DSN? Via ODBC Administrator? This is an end user, not a developer? If so, this definitely should not be the case! It should be the application that takes care of the DSN and perform the connection transparently to the user.

so for those databases using the sipr, do you think i can remove the tables, relink them and this time i choose sits and eventually i can end up with one system dsn ?

Thanks

If you mean to move all tables in sipr database to sits database, then yes that will go back to one dsn.

 

[oumahexi]

Please make sure you back everything up first.

 

[maxmangion]

The user gets to choose DSN? Via ODBC Administrator? This is an end user, not a developer?

The person in question wasn't an end user. Basically some "power users" often create access databases and link them via odbc so that we can create our own queries/reports etc. So what was happening is that the power user in question went to File->Get External Data->Link Tables ->ODBC ... on the machine data source window sometimes she chose sipr and other times chose sits (so basically on her pc she had two similar dns yet with different names and it appears that she used them interchangeable)

Thank you very much for your guidance and finally since i managed to solve the issue, i don't have to worry that if that pc fails, i cannot run those databases elsewhere

 

[Banana]

Gotcha.

I was afraid that all users in the company was actually tinkering with DSNs. A scary thought for me, indeed, which is probably why I settled on DSN-less connection.

Glad we got it all sorted out.

 

[maxmangion]

Once again a big thank you for you and oumahexi as well

reputation time