Page 2 of 2 FirstFirst 12
Results 16 to 24 of 24
  1. #16
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    I can't help you much from here. When you run your procedure (the original question about linking to an oracle db through tns) does it create a linked table in your access database? if so all you need is INSERT INTO <TargetTable> SELECT * FROM <SourceTable> assuming they both have identical structure or at least that <TargetTable> has all the fields that <SourceTable> does.

  2. #17
    nvrwrkn is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Jun 2012
    Posts
    35
    if I use the code as you wrote it I still get the ISAM error and no table is created locally.

    I get the error either way and it appears to be something with the connection string because I'm not using ODBC to connect.

  3. #18
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    You didn't answer my question though, when you run your procedure that creates the link to your oracle database, does it create a table in your access database? can you open that table and view the contents?

  4. #19
    nvrwrkn is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Jun 2012
    Posts
    35
    I can't use your code as entered because it relies on ODBC using the SQL Server driver, it requires me to state a server, which is looking for an SQL server, and a separate database running on that server. If I attempt to run it as written I get ODBC errors as it can't find the SQL server, canceling it brings up the SQL server connect dialog attempting to let me change the MS SQL server information to log in.

    I'm still under the constraint of having to use TNS to find the location of the server, which in oracle's case is the same thing as the database when using TNS. So I had to change the connection string to match what will use TNS to connect me to the remote database. I had to change it to ConnectString = "Provider=msdaora;" & "Data Source =MYDATABSE;" & "User ID=" & user & ";" & "Password=" & pass

    At the moment, that's the only DSNless string I have working to connect to the remote oracle database and log me in. When I replace only that string to make it connect correctly I get the ISAM error. From what I can tell it appears to be because I'm not connecting to a MS based database.


    .... no table is created either way.

  5. #20
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Quote Originally Posted by nvrwrkn View Post
    eureka! I think..... does that last append actually populate the access table or just create a link table? There's waaaaay too much data in the oracle table to suck it all down into an access table.
    I was under the impression after this post and a couple prior you'd gotten the connection working and it was creating a table in your MS access database to work with. Are you saying that you were able to create the ODBC connection through the control panel > system administrator > odbc connections but you can't use that ODBC connection to create a linked table in your database. I'm a bit confused if you could go over what exactly you have done.

  6. #21
    nvrwrkn is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Jun 2012
    Posts
    35
    shouldn't have said eureka I answered that in optimism when I wasn't at work. Here's the function that gets me logged in and checks the password:


    Code:
    Public Function ConnectDB()
    
    Dim Connect As String
    Set con = New ADODB.Connection
    
    On Error GoTo ERRHANDLER
    
        Connect = "Provider=msdaora;" & "Data Source =MYDBASE;" &  "User ID=" & user & ";" & "Password=" & pass
        con.ConnectionString = Connect
        con.Open Connect
        
    Exit Function
    
    ERRHANDLER:
    
    MsgBox "       Invalid Logon/Password" & vbCrLf & "  Please use valid Logon info"
    
    End Function
    That function successfully logs into MYDBASE. I never got to a point where I had any code creating tables. So far I only have successful login and simple password checking.

    I did just find something about msdaora being the possible culprit. I did get a second to try the string


    DRIVER={Microsoft ODBC for Oracle};SERVER=Orcl;UID=test;PWD=test;

    And I'm a bit farther now, not being able to find the table I'm looking for so now I think it's
    more a question of asking correctly for the source table in MYDBASE.

  7. #22
    nvrwrkn is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Jun 2012
    Posts
    35
    Think I gottit now. The following just created a linked table to the database:

    Code:
    Function LinkTables()
        Dim DB As Database, tDef As TableDef
        Set DB = CurrentDb
        Set tDef = DB.CreateTableDef("Test")
        tDef.Connect = "ODBC;Driver={Microsoft ODBC for Oracle};Server=Orcl;UID=user;PWD=pass"
        tDef.SourceTableName = "MV_OWNER.GEO_CODES_MV"
        DB.TableDefs.Append tDef
    End Function
    Table shows what it should now.

    rpeare I thank you for your time and expertise here. This is why this forum is invaluable.

    Thank you again.

  8. #23
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    I'm afraid I didn't display much expertise, just a matter of shooting a lot of stuff at the wall and seeing what sticks.

  9. #24
    nvrwrkn is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Jun 2012
    Posts
    35
    eh, it's always easier in person to just work the problem rather than listening to someone give you bits and pieces.

    Couldn't have done it without ya, thanks.

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 0
    Last Post: 04-25-2012, 07:59 AM
  2. ODBC with Oracle
    By asearle in forum Queries
    Replies: 1
    Last Post: 09-07-2011, 10:22 PM
  3. Oracle to MSACCESS
    By karankukreja in forum Access
    Replies: 1
    Last Post: 03-24-2011, 08:20 AM
  4. Oracle LAG Equivalent
    By OzzyMiner in forum Queries
    Replies: 2
    Last Post: 03-10-2011, 11:41 AM
  5. Replies: 5
    Last Post: 03-29-2009, 07:20 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