Link to home
Start Free TrialLog in
Avatar of yhwhlivesinme
yhwhlivesinme

asked on

Error when adding Oracle linked table to MS Access

Using:
Oracle 11g Database (not local install)
MS Access 2007

I currently have a linked table in my database using a DSN, it works fine.  The connection string for this table is: "ODBC;DSN=orcl1;UID=user;PWD=password;"  I thought it would be a good idea to make this a DSNless connection to make installation easier when the app is distributed to clients.  So, after looking through EE and at every website under the sun I came up with this:
Public Function CreateLinkedTable()
  Dim td As TableDef
  
  Set td = CurrentDb.CreateTableDef("tblDSNLessTest")
  td.Connect = "ODBC;Driver={Microsoft ODBC for Oracle};Pwd=admuser;UID=admuser;server=orcl"
  td.SourceTableName = "ADMUSER.PMSTBLLOOKUPS"
  'td.Attributes = ""
  CurrentDb.TableDefs.Append td
  Set td = Nothing
End Function

Open in new window


I get an error "Reserved Error (-7778); there is no message for this error."  So to test out my connection string I wrote this function:
Public Function connectionTest()
On Error GoTo cleanup
  Dim cn As New ADODB.Connection
  Dim rs As New ADODB.Recordset
  
  cn.ConnectionString = "Driver={Microsoft ODBC for Oracle};Pwd=password;UID=user;server=orcl"
  cn.Open
  
  rs.Open "select * from admuser.pmstbllookups", cn
  rs.MoveFirst
  Do While Not (rs.EOF)
    Debug.Print rs.Fields(0) & "|||" & rs.Fields(1) & "|||" & rs.Fields(2) & "|||" & rs.Fields(3) & "|||" & rs.Fields(4) & "|||" & rs.Fields(5) & "|||"
    rs.MoveNext
  Loop
  
cleanup:
  rs.Close
  cn.Close
  Set cn = Nothing
  Set rs = Nothing
End Function

Open in new window

The function executes fine (without going to the error handler) it prints out the correct information from the table.  Also, the linked table that I have currently in the database using DSN shows the correct data, I can edit data through it as well.

I do have TNSNames on my client machine with in the proper location with the following code in it:
ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = server.something.net)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl.world)
    )
  )

Open in new window


So "orcl" is a valid alias to connect to... Ultimately it would even be nice to have a connection string that did not rely on TNSNames.ora, I hear you can embed the TNS information directly into the connection string (I tried this and got the same error).  I do not have the Oracle ODBC Driver on my machine and I'd rather use Microsoft's unless someone here has had bad luck with Microsoft's.

Thanks in advance for the help, I'll be here banging my head against my monitor until I hear back...
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

>>easier when the app is distributed to clients
>>I'd rather use Microsoft's unless someone here

You still need an Oracle client installed.  The MSoft drivers really sit on top of the Oracle client/native drivers.  Microsoft is even desupporting Oracle drivers moving forward (at least in the .Net world).

The Instant Client would probably work for you.

>>I hear you can embed the TNS information directly into the connection string

I've seen it but never used it.  Look around for EZConnect.

Sorry but I really cannot help with the DSNless error.
Avatar of yhwhlivesinme

ASKER

I do have Oracle installed, but for some reason just don't have the ODBC Driver installed... Seems as though since the Microsoft driver is working for both the linked table with DSN and the ADO connection, then it should work in a DNSless connection string...
I figured you had the client installed or it would have worked with the DSN.

I was just commenting on the TNSnames issue since you mention deloyment.
Good point, I figured I'd still have to have oracle installed to talk to SQL*NET on the server, Just figured it'd be easier to not have to configure the TNSnames file and create a DSN, because I probably will not be performing the install, someone from tech support probably will.  Thanks for the info though!
By hard-coding the tns entries in the string the person installing will need to be able to edit the connect info or the database will need to remain static in it's configuration.

If the DB server changes, the app would need to change.
I'm fine with that.  If I had to use TNS I'm not against it, I'm just thinking that it would be better to change it in one place (application) rather than on every user's machine.
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I'm having trouble installing the oracle driver on top of the SQL*plus that I already have installed, everytime I try to install it, it then knocks out my other connection (plus the oracle connection doesnt work) when I uninstall it the original connection then works again.  I traced it down to changing the Environment variable "Path" to including the oracle home directory.  I guess my first installation of oracle was done in a different location and maybe I should install the ODBC engine in that same location?
Didn't really solve my problem, just accepting to close the problem.