DNS-Less ODBC connection

[hartwell]

Im looking to connect to a Aspect Call Centre using an ODBC connection. I can currently connect to our own site as it is configured in the tnsnames.ora file however this file is protected and can not be changed!

I have searched high and low and cannot find a solution. From what I can tell i must use a DNS-Less connection or possibly a fileDNS.

Looking to convert this out of the .ora file into a string to connect.

DNS_NAME.world =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(COMMUNITY = tcp.world)
(PROTOCOL = TCP)
(Host = ServerAddress)
(Port = 0000)
)
(ADDRESS =
(COMMUNITY = tcp.world)
(PROTOCOL = TCP)
(Host = ServerAddressBackup)
(Port = 0000)
)
)
(CONNECT_DATA = (SID = ACC)
)
)

 

[PHV]

Have a look here:

http://www.connectionstrings.com/?carrier=oracle

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[hartwell]

great thank you, asuming i need to use the below string how would i call it so a list of tables come up for me to connect to? Have searched the forum somewhat and cant seem to find anything other than a ADOX connection but code given doesnt work, error on the "CurrentProject" part

SERVER=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=SERVERNAME)(PORT=0000))(CONNECT_DATA=(SID=ACC)));uid=XXX;pwd=XXX;


Public Function SQLServerLinkedTable()
Dim oCat As ADOX.Catalog
Dim oTable As ADOX.Table
Dim sConnString As String

' Set SQL Server connection string used in linked table.
sConnString = "ODBC;" & _
"Driver={SQL Server};" & _
"Server={Boxer};" & _
"Database=ReportInventory;" & _
"Trusted_Connection=No;" & _
"Uid=rptinvapps;" & _
"Pwd=mypassword;"

' Create and open an ADOX connection to Access database
Set oCat = New ADOX.Catalog
oCat.ActiveConnection = CurrentProject.Connection

' Create a new Table object
Set oTable = New ADOX.Table

With oTable
.Name = "dbo_tblReportRequestPrelim"
Set .ParentCatalog = oCat
.Properties("Jet OLEDB:Create Link") = True
.Properties("Jet OLEDB:Remote Table Name") = "dbo.tblReportRequestPrelim"
.Properties("Jet OLEDB:Cache Link Name/Password") = True
.Properties("Jet OLEDB:Link Provider String") = sConnString
End With

' Add Table object to database
oCat.Tables.Append oTable
oCat.Tables.Refresh
Set oCat = Nothing

End Function

Public Function SQLServerLinkedTableRefresh()
Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table

Set cat = New ADOX.Catalog
Set tbl = New ADOX.Table

Dim sConnString As String

' Set SQL Server connection string used in linked table.
sConnString = "ODBC;" & _
"Driver={SQL Server};" & _
"Server={Boxer};" & _
"Database=ReportInventory;" & _
"Trusted_Connection=No;" & _
"Uid=rptinvapps;" & _
"Pwd=mypassword;"

' Open the catalog.
cat.ActiveConnection = CurrentProject.Connection
For Each tbl In cat.Tables
If tbl.Type = "LINK" And tbl.Name = "tblReportRequestPrelim" Then
tbl.Properties("Jet OLEDB:Link Datasource") = sConnString
End If
Next
End Function

 

[PHV]

Have you tried to replace this:
oCat.ActiveConnection = CurrentProject.Connection
with this ?
oCat.ActiveConnection = sConnString

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[hartwell]

great, got a little further however get the following error

[ORACLE][ODBC Oracle Driver][Oracle OCI]ORA=03121: No interface driver connected - Function not performed.



Public Function SQLServerLinkedTable()
Dim oCat As ADOX.Catalog
Dim oTable As ADOX.Table
Dim sConnString As String

' Set SQL Server connection string used in linked table.
sConnString = "Driver={Oracle73 Ver 2.5};SERVER=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=birmbrindley1P.aspect.rbsgrp.net)(PORT=1521))(CONNECT_DATA = (SID = ACC)));uid=dta;pwd=tjm;"

' Create and open an ADOX connection to Access database
Set oCat = New ADOX.Catalog
oCat.ActiveConnection = sConnString '###ERROR PRODUCED HERE

' Create a new Table object
Set oTable = New ADOX.Table

With oTable
.Name = "dbo_tblReportRequestPrelim"
Set .ParentCatalog = oCat
.Properties("Jet OLEDB:Create Link") = True
.Properties("Jet OLEDB:Remote Table Name") = "dbo.tblReportRequestPrelim"
.Properties("Jet OLEDB:Cache Link Name/Password") = True
.Properties("Jet OLEDB:Link Provider String") = sConnString
End With

' Add Table object to database
oCat.Tables.Append oTable
oCat.Tables.Refresh
Set oCat = Nothing

End Function

 

[JerryKlmns]

Maybe

Set oCat.ActiveConnection = CurrentProject.Connection

plus

Set oTable = Nothing

The rest as is, trusting that your Oracle connection string is valid...