Link to an Oracle Database

[Guest]

Here is my code...

I want to run a query against an oracle database and make a table in Access.

This is what I came up with and it's not working.

The Error:
Could not find Installable ISAM.

The question:
why would it ask for ISAM if I already installed Oracle drivers?

The code


Sub TSNLessTableLink()

Dim db As Database
Dim tbl As TableDef
Dim tblName As String
Dim srcTblName As String
Dim Conn As String

tblName = "NameOfLocalLink"
srcTblName = "NameOfTableOnServer"

Conn = "Driver={Microsoft ODBC for Oracle}; " & _
"CONNECTSTRING=(DESCRIPTION=" & _
"(ADDRESS=(PROTOCOL=TCP)" & _
"(HOST=something.net)(PORT=XXXX))" & _
"(CONNECT_DATA=(SERVICE_NAME=XXXXXX))); uid=XXXXX;pwd=XXXXX;"

Set db = CurrentDb

Set tbl = db.CreateTableDef(tblName)
tbl.SourceTableName = srcTblName
tbl.Connect = Conn
db.TableDefs.Append tbl
db.TableDefs.Refresh
Dim oCon: Set oCon = WSCript.CreateObject("ADODB.Connection")
Dim oRs: Set oRs = WSCript.CreateObject("ADODB.Recordset")
oCon.Open strCon
Set oRs = oCon.Execute("select * from ATABLE where createdtime between
to_date(‘MM/DD/YY hh:mm:ss’,’MM/DD/YYYY HH24:MI:SS’) and to_date(‘MM/DD/YY
hh:mm:ss’,’MM/DD/YYYY HH24:MI:SS’)")
While Not oRs.EOF
WSCript.Echo oRs.Fields(0).Value
oRs.MoveNext
Wend
oCon.Close
Set oRs = Nothing
Set oCon = Nothing


Set tbl = Nothing
Set db = Nothing

End Sub

 

[Guest]

Try to connect to the Oracle DB using:

Function OpenDB()

Dim Cn As ADODB.Connection
Dim cmd As ADODB.Command
Dim rst As ADODB.Recordset
Dim strConn As String
Dim SQL As String

strConn = "Provider=msdaora;Data Source=YOUR_DB;User
Id=YOURUSERNAME;Password=YOURPASSWORD;"

Set Cn = New ADODB.Connection

With Cn
.ConnectionString = strConn
.CursorLocation = adUseClient
.Open
End With

SQL = "Your SQL"
Set cmd = New ADODB.Command

With cmd
.ActiveConnection = Cn
.CommandText = SQL
.CommandType = adCmdText
End With

Set rst = cmd.Execute

End Function
-Matt
Microsoft Certified Professional