1st time I am posting on this site. I have visited many times before and by using search, I have been able to find the solutions I needed. This time however, I am stuck. I would really appreciate anyone's time and help.
My work has just started transitioning over to Oracle from Microsoft SQL.
Unfortunatley I have never used Oracle, and we are having issues getting everything set up.
I am trying to create a linked table without using the TSNAMES.ora file.
I was able to get a rather simple query to work:
This query works just fine.
But my real goal is to create linked tables. For this i have tried numerous different ways, but am always ending up with an error.
Most recently,
"Run-time error '3000'
Reserved error (-7778); there is no message for this error."
Here is what I am trying:
It always errors out on db.TableDefs.Append tdf line.
If i add in "ODBC;" & on the tdf.Connect then i get the reserved Error 7778. If i leave off the ODBC then I get cannot find installable ISAM.
Does anyone have any ideas what I could be doing wrong?
It just seems so odd to me that the query works, but I cant create a linked table.
Thanks for your help
My work has just started transitioning over to Oracle from Microsoft SQL.
Unfortunatley I have never used Oracle, and we are having issues getting everything set up.
I am trying to create a linked table without using the TSNAMES.ora file.
I was able to get a rather simple query to work:
Private Sub Form_Load()
Dim strCon
Dim dateValue As Date
strCon = "Driver={Microsoft ODBC for Oracle}; " & _
"CONNECTSTRING=(DESCRIPTION=" & _
"(ADDRESS=(PROTOCOL=TCP)" & _
"(HOST=hostname)(PORT=1521))" & _
"(CONNECT_DATA=(SERVICE_NAME=servicename))); uid=username;pwd=password;"
Dim oCon
Set oCon = CreateObject("ADODB.Connection")
Dim oRs
Set oRs = CreateObject("ADODB.Recordset")
oCon.Open strCon
Dim testSQL As String
testSQL = "SELECT SYSDATE FROM DUAL"
Set oRs = oCon.Execute(testSQL)
If oRs.EOF Then
dateValue = 0
Else
dateValue = oRs!SYSDATE
End If
oCon.Close
Set oRs = Nothing
Set oCon = Nothing
Me.dateTextbox = dateValue
End Sub
Dim strCon
Dim dateValue As Date
strCon = "Driver={Microsoft ODBC for Oracle}; " & _
"CONNECTSTRING=(DESCRIPTION=" & _
"(ADDRESS=(PROTOCOL=TCP)" & _
"(HOST=hostname)(PORT=1521))" & _
"(CONNECT_DATA=(SERVICE_NAME=servicename))); uid=username;pwd=password;"
Dim oCon
Set oCon = CreateObject("ADODB.Connection")
Dim oRs
Set oRs = CreateObject("ADODB.Recordset")
oCon.Open strCon
Dim testSQL As String
testSQL = "SELECT SYSDATE FROM DUAL"
Set oRs = oCon.Execute(testSQL)
If oRs.EOF Then
dateValue = 0
Else
dateValue = oRs!SYSDATE
End If
oCon.Close
Set oRs = Nothing
Set oCon = Nothing
Me.dateTextbox = dateValue
End Sub
This query works just fine.
But my real goal is to create linked tables. For this i have tried numerous different ways, but am always ending up with an error.
Most recently,
"Run-time error '3000'
Reserved error (-7778); there is no message for this error."
Here is what I am trying:
Private Sub linkButton_Click()
Dim strConnect
strConnect = "Driver={Microsoft ODBC for Oracle}; " & _
"CONNECTSTRING=(DESCRIPTION=" & _
"(ADDRESS=(PROTOCOL=TCP)" & _
"(HOST=hostname)(PORT=1521))" & _
"(CONNECT_DATA=(SERVICE_NAME=servicename))); uid=username;pwd=password;"
Set db = CurrentDb()
Set tdf = db.CreateTableDef("local table")
tdf.SourceTableName = "server table"
tdf.Connect = strConnect
db.TableDefs.Append tdf
db.TableDefs.Refresh
Set tdf = Nothing
Set db = Nothing
End Sub
Dim strConnect
strConnect = "Driver={Microsoft ODBC for Oracle}; " & _
"CONNECTSTRING=(DESCRIPTION=" & _
"(ADDRESS=(PROTOCOL=TCP)" & _
"(HOST=hostname)(PORT=1521))" & _
"(CONNECT_DATA=(SERVICE_NAME=servicename))); uid=username;pwd=password;"
Set db = CurrentDb()
Set tdf = db.CreateTableDef("local table")
tdf.SourceTableName = "server table"
tdf.Connect = strConnect
db.TableDefs.Append tdf
db.TableDefs.Refresh
Set tdf = Nothing
Set db = Nothing
End Sub
It always errors out on db.TableDefs.Append tdf line.
If i add in "ODBC;" & on the tdf.Connect then i get the reserved Error 7778. If i leave off the ODBC then I get cannot find installable ISAM.
Does anyone have any ideas what I could be doing wrong?
It just seems so odd to me that the query works, but I cant create a linked table.
Thanks for your help
