ODBC Connection (Access XP)

Topic

New Reply

I have an Oracle 9i database that someone else linked in Access using the Microsoft ODBC for Oracle driver. They can see the data on their computer for all the linked tables but when I copied the database containing the linked tables to my computer I get an error message that the ODBC call failed. However if I login in to the network on their computer I can also see the data. The only difference between our computers is that the other computer is running Access 2000 and I am using Access 2002. From my computer I can logon to the database using SQL Plus. I only have one TNS home. I have downloaded the newest version that I found on the Microsoft site for the ODBC for Oracle driver.

Has anyone else experienced this same problem and if so how was it resolved?

Reply | Quote

Replies

You probably need to create an ODBC data source for that specific database on your PC. The other PC already contains such a data source, so they or you can connect to the database from that PC. Try to find out what the data source is called and how it is configured on that PC.

Open the ODBC Data Sources control panel.
Activate the System DSN tab (I think they created a System DSN, since you can use it on that PC)
Look at the list of DSN’s and try to find out which one is used to connect to the Oracle database. You can click Configure… to find out more about a DSN.
If you cannot find it in the System DSN tab, look in the File DSN tab (it seems unlikely that they created a User DSN, since that is only available to the user who created it)
Write down the settings.

On your own PC, create a new data source (Add… button) and copy the settings you wrote down.

See for illustrations The Windows NT ODBC Data Sources Control Panel. It’s for Windows NT, but it’s not much different for other Windows versions.

Reply | Quote

Thanks for your reply. I have set the ODBC data source connections the same as the other computer that works. I can open the database containing the linked tables. Access knows where the full path to the location of the links because if I let my mouse linger on one of the linked tables it will display “ODBC; DSN=HRINFO; SERVER=hrinfo;;TABLE=AMS_HRMADM.EMPL”
If I try to open the linked table to view the data it will ask for my username, password, and server. Once this info is filled in and I click OK, I get the error that says “ODBC–Connection to HRINFO failed. The weird thing is that I can create a query based on this linked table and I can see all the fields the table contains but it will not let me execute the query and view the data. I am being told it is an Access problem but how can that be the case?

Reply | Quote

Judy,

We’re currently experiencing something rather similar on only a couple of machines in our office, but the backend is SQL Server rather than Oracle and it isn’t for all databases, just this particular one. I don’t have an answer for you but I certainly can relate to the frustration.

Reply | Quote

Someone in my office located a document indicating some registry keys under MTxOCI that needed to be changed. The article related to Oracle 8i rather than 9i but when she changed the registry keys it solved the problem. I can now see my data.

Reply | Quote

Someone in my office located a document indicating some registry keys under MTxOCI that needed to be changed. The article related to Oracle 8i rather than 9i but when she changed the registry keys it solved the problem. I can now see my data.

Reply | Quote

Now that I can see the data within the linked Oracle tables, I have created an Access query that transfers the text from the query to a delimited ASCII file in the location I specify. I would like to be able to set it up to run automatically in the middle of the night by using a scheduled task. Everything works fine except for the fact that the Microsoft ODBC for Oracle Connect dialog box pops up and wants the user name, password, and server info. My question: is there anyway through VBA code to supply this information so that the dialog box does not sit and wait for the information to be filled in manually?

Reply | Quote

Now that I can see the data within the linked Oracle tables, I have created an Access query that transfers the text from the query to a delimited ASCII file in the location I specify. I would like to be able to set it up to run automatically in the middle of the night by using a scheduled task. Everything works fine except for the fact that the Microsoft ODBC for Oracle Connect dialog box pops up and wants the user name, password, and server info. My question: is there anyway through VBA code to supply this information so that the dialog box does not sit and wait for the information to be filled in manually?

Reply | Quote

Judy,

We’re currently experiencing something rather similar on only a couple of machines in our office, but the backend is SQL Server rather than Oracle and it isn’t for all databases, just this particular one. I don’t have an answer for you but I certainly can relate to the frustration.

Reply | Quote

Thanks for your reply. I have set the ODBC data source connections the same as the other computer that works. I can open the database containing the linked tables. Access knows where the full path to the location of the links because if I let my mouse linger on one of the linked tables it will display “ODBC; DSN=HRINFO; SERVER=hrinfo;;TABLE=AMS_HRMADM.EMPL”
If I try to open the linked table to view the data it will ask for my username, password, and server. Once this info is filled in and I click OK, I get the error that says “ODBC–Connection to HRINFO failed. The weird thing is that I can create a query based on this linked table and I can see all the fields the table contains but it will not let me execute the query and view the data. I am being told it is an Access problem but how can that be the case?

Reply | Quote

You probably need to create an ODBC data source for that specific database on your PC. The other PC already contains such a data source, so they or you can connect to the database from that PC. Try to find out what the data source is called and how it is configured on that PC.

Open the ODBC Data Sources control panel.
Activate the System DSN tab (I think they created a System DSN, since you can use it on that PC)
Look at the list of DSN’s and try to find out which one is used to connect to the Oracle database. You can click Configure… to find out more about a DSN.
If you cannot find it in the System DSN tab, look in the File DSN tab (it seems unlikely that they created a User DSN, since that is only available to the user who created it)
Write down the settings.

On your own PC, create a new data source (Add… button) and copy the settings you wrote down.

See for illustrations The Windows NT ODBC Data Sources Control Panel. It’s for Windows NT, but it’s not much different for other Windows versions.

Reply | Quote