Results 1 to 10 of 10
  1. #1
    FTAUSS is offline Novice
    Windows XP Access 2003
    Join Date
    Jan 2012
    Posts
    8

    Linking to Oracle, Data Source Nmae to long ???

    Trying to create reasonable dynamic connection so any authorized person can use the feature without having to do a separate step to confirm thier creds.

    Tables are already linked and vivible in the Navigation Pane

    I want to connect in code though before running a query

    Code is:

    Public Function TestConnectionString() As String
    Dim cnOracle As New ADODB.Connection
    Dim rsOracle As New ADODB.Recordset
    Dim cnString As String
    cnString = OracleConnectionString
    rsOracle.Open cnString, "SELECT * FROM already_liked_table_name"


    End Function

    Private Function OracleConnectionString()
    OracleConnectionString = "Oracle11g2;DSN=dsnname; uid=username; pwd=password"
    End Function

    With or without curly braces {} around Oracle11g2 I get

    Error #-2147467259
    [Microsoft][ODBC Driver Manager] Data source name too long

    Anybody?

  2. #2
    RuralGuy's Avatar
    RuralGuy is online now Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    How about starting with what is in the OracleConnectionString variable or constant?
    (RG for short) aka Allan Bunch Previous MS Access MVP - WinXP Pro, Win7 Pro Win10 Pro - acXP, ac07, ac10, ac13
    How to mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  3. #3
    FTAUSS is offline Novice
    Windows XP Access 2003
    Join Date
    Jan 2012
    Posts
    8
    It's there. 4 lines down - Private Function OracleConnectionString()

  4. #4
    RuralGuy's Avatar
    RuralGuy is online now Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Du'h...I hate it when that happens. <putting on glasses>
    (RG for short) aka Allan Bunch Previous MS Access MVP - WinXP Pro, Win7 Pro Win10 Pro - acXP, ac07, ac10, ac13
    How to mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  5. #5
    RuralGuy's Avatar
    RuralGuy is online now Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Maybe it does not like "already_liked_table_name"
    (RG for short) aka Allan Bunch Previous MS Access MVP - WinXP Pro, Win7 Pro Win10 Pro - acXP, ac07, ac10, ac13
    How to mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  6. #6
    RuralGuy's Avatar
    RuralGuy is online now Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    (RG for short) aka Allan Bunch Previous MS Access MVP - WinXP Pro, Win7 Pro Win10 Pro - acXP, ac07, ac10, ac13
    How to mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  7. #7
    FTAUSS is offline Novice
    Windows XP Access 2003
    Join Date
    Jan 2012
    Posts
    8
    already_liked_table_name is just the name of the table, how else would I identify it?

    And I did a kinda decent web search before I came here. The only thing I saw anywhere was ahint, a hint mind you, that it didn't like the fact that the tables were linked already. I think the last time I did this, 10 months ago, that is how it was setup. My boss is a little frantic about things she can't see even if she doesn't understand them. And there doesn't appear to be a lot she understands.

    I'm just trying to add the last bit of bullet proof to it before I go, couple of different people use it and having to relink the tables before running the process is not something I want to leave behind. I'm gone in about a month, soon as I get an offer, so I feel pressed to get it done.

    Anyway I think I'm gonna try it tommorrow after removing the links.

  8. #8
    RuralGuy's Avatar
    RuralGuy is online now Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Did you look at the link I posted? It talks about *NOT* using semicolons ";"!
    (RG for short) aka Allan Bunch Previous MS Access MVP - WinXP Pro, Win7 Pro Win10 Pro - acXP, ac07, ac10, ac13
    How to mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  9. #9
    FTAUSS is offline Novice
    Windows XP Access 2003
    Join Date
    Jan 2012
    Posts
    8
    Um it talked about not using commas.
    From the page

    The correct syntax is: DSN=vfpsql;USERID=sa;PASSWORD=sa;DATABASE=pubs

    The parms different but my parms are what I need. And my DSN is exactly what is listed in the MS OBDC under Admin

    And I did see that page before in my searching.

  10. #10
    RuralGuy's Avatar
    RuralGuy is online now Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Well, that exhausts my thoughts on the topic. Sorry.
    (RG for short) aka Allan Bunch Previous MS Access MVP - WinXP Pro, Win7 Pro Win10 Pro - acXP, ac07, ac10, ac13
    How to mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

Please reply to this thread with any new information or opinions.

Similar Threads

  1. insert data into oracle linktable from access table
    By newaccess in forum Import/Export Data
    Replies: 1
    Last Post: 01-04-2013, 02:20 PM
  2. Replies: 13
    Last Post: 01-11-2012, 09:44 PM
  3. Load csv data to Oracle through Access
    By acces2oracle in forum Access
    Replies: 1
    Last Post: 10-24-2011, 02:41 PM
  4. Importing data from Oracle to access
    By indira in forum Access
    Replies: 3
    Last Post: 11-18-2010, 02:58 PM
  5. SQL Server/VBA Data Types - Long & BigInt
    By Patrick.Grant01 in forum Programming
    Replies: 0
    Last Post: 06-05-2009, 09:24 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums