ADO_Connect To Oracle From Microsoft Access 1

[LevelThought]

Trying to connect to Oracle from Access in the testing of ADO to extract data.

Continue to get the error message "Ora 12154: TNS: Could not resolve service name" when I step through the code.


Currently reviewing Data Sources and TNS file to try to resolve.

Any idea as to a resolution?


Sub CreateRecordset2()
'Declare and instantiate the connection
Dim cnn As ADODB.Connection
Set cnn = New ADODB.Connection
'Declare and instantiate the recordset
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
'Open the connection
cnn.Open "Provider=MSDAORA;Data source=VTT;UserId=VF_jacxd;Password='rtJU45C';Persist Security Info=True"

'Use the ActiveConnection property of the recordset object
'to supply the connection information
rst.ActiveConnection = cnn

'Open the recordset and print its contents
rst.Open "Select * from Customer_Detail"
Debug.Print rst.GetString
'Close and destroy the recordset object and the connection object
rst.Close
Set rst = Nothing
cnn.Close
Set cnn = Nothing
End Sub

 

[cmmrfrds]

Try testing your connection with the UDL (universal data link) Wizard.

Here is how you invoke the udl wizard. Do this on your desktop.

1. create a blank notepad file.
2. save the file.
3. rename the file by adding a new extention of .udl
make sure you save as all files not txt or some other
file type.
4. the icon for the file should change from notepad to a
little computer - it has now become the wizard.
5. double click on the file and it will bring up a dialog
box with multipule tabs.
6. If available use the Oracle provider instead of the Micosoft provider that you are using.
7. look under the ALL tab and there will be parameter settings which you can change if necessary. There is a test
button, press to test the connection.
8. close the file.
9. open the file from notepad instead of double clicking the icon. You will see the connection string which you can copy and paste into your program.

Here is a site that has numerous connection strings.

http://www.carlprothman.net/Technology/ConnectionStrings/OLEDBProviders/tabid/87/Default.aspx

 

[LevelThought]

The connections tested successfully at the workstation.

However, when I try the same connections on the laptop computer, from home, I am not able to connect.

My gut feeling is that the tnsnames file on the laptop needs tweaking???

 

[cmmrfrds]

Are you trying to connection from a remote location that is not on the same LAN as the Oracle Server?

 

[LevelThought]

Finally, I was able to connect!

Thanks.