[Last Call] Learn how to a build a cloud-first strategyRegister Now


Error when adding Oracle linked table to MS Access

Posted on 2011-10-18
Medium Priority
Last Modified: 2013-11-27
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"
  rs.Open "select * from admuser.pmstbllookups", cn
  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) & "|||"
  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:
    (ADDRESS = (PROTOCOL = TCP)(HOST = server.something.net)(PORT = 1522))
      (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...
Question by:yhwhlivesinme
  • 5
  • 4
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36987019
>>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.

Author Comment

ID: 36987046
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...
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36987072
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.
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.


Author Comment

ID: 36987128
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!
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36987193
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.

Author Comment

ID: 36993639
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.
LVL 78

Accepted Solution

slightwv (䄆 Netminder) earned 2000 total points
ID: 36993676
Cool.  DSNless should work.

Try the Oracle driver to help narrow down the original error.

Then we can try the EZConnect method: Using the Easy Connect Naming Method (aka EZ Connect)


Author Comment

ID: 37011857
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?

Author Closing Comment

ID: 38206790
Didn't really solve my problem, just accepting to close the problem.

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Suggested Courses

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question