cant read a linked table from oracle in access

[adaniele]

hi all, thx 4 your time and hlp in advance.

I linked a table which is in oracle 8. what i am trying to achieve is to read the linked table and populate another table but in access 2000.

the problem is that it is not reading the data coming from the linked table.
this is my code:

   Dim strTable As String
   Dim vcountp, vpage, i, rcount, vtlts As Long
   Dim cnn2 As Recordset
   Dim cuento, n, p, j, l, qq As Long
   Dim vpc, vjc, vyc, vsave As Integer
   Dim cnn As ADODB.Connection
   Set cnn = New ADODB.Connection
 
   Set dbsname = CurrentDb
   cnn.ConnectionString = "OraOLEDB.Oracle;UID=USERID;PWD=PWD;Data Source=XXXXX;"
   cnn.Open
   Set cnn2 = dbsname.OpenRecordset("ssmdata", dbOpenTable)
   
    
    cnn.MoveFirst
    ' count frequency for first record
    cuento = 0
    For n = 1 To 7
        If Val(Mid(cnn.Fields("frequency"), n, 1)) <> 0 Then
            cuento = cuento + 1
        End If
    Next n

when i try to read the frequency field, shows me an error because it cant read that field.

SSMDATA is the access table.
the linked table is shown in access as wr_space_w_q_sssmdata


thx 4 your hlp, max.

 

[Moniker]

You need to make sure you have Oracle ODBC Drivers for Access. Without them, you won't hit the Oracle table directly. By that, I mean that you can go into Oracle and export the table into something simple like a CSV file, and then easily import it into Access. If you're trying to read a "live" Oracle table (one that is perhaps updated daily and exporting to a CSV would be a pain), then you need that specific Oracle ODBC Driver for it to work.

At my work, we hit DB2 all the time through Access, so I know Access will handle giant DBs -- it's just a matter of conencting it properly.

 

[adaniele]

moniker, thx 4 your comment.

this is an example of what i want (important)
oracle table:

departure arrival caterer ac frequency
syd mel syd 777 6
syd mel syd 777 6
mel syd mel 777 7

i would like to copy into ssmdata table in access (no duplicated records) :

departure arrival caterer ac frequency
syd mel syd 777 6
mel syd mel 777 7


As i mentioned above i linked the oracle table but i dont know how to read it.
even if i dont link the oracle table and i like to read it directly from oracle, i dont know how to.
thx, max

 

[Moniker]

You have to link to the table directly in order to read it, which is why you need the Oracle ODBC Driver for Access (also included with the Access install if you have the CDs sitting around). It's not installed as part of the default install unless you selected "Custom" install and selected it. To make sure you have the Oracle driver installed correctly, go to Control Panel, then Administrative Tools, and then Data Sources. You should figure it out from there if the Oracle is not listed.

From there, when you link to it, write a quick SELECT DISTINCT on that table to retrieve only unique records.

 

[adaniele]

You have to link to the table directly in order to read it, which is why you need the Oracle ODBC Driver for Access (also included with the Access install if you have the CDs sitting around). It's not installed as part of the default install unless you selected "Custom" install and selected it. To make sure you have the Oracle driver installed correctly, go to Control Panel, then Administrative Tools, and then Data Sources. You should figure it out from there if the Oracle is not listed.

From there, when you link to it, write a quick SELECT DISTINCT on that table to retrieve only unique records.

moniker, thx for your advise again.
However my problem is not my odbc. i have created another form where i can connect to the db, but i dont know how to read the table and its contents.
thx, max.

 

[adaniele]

Solved

ok guys. this is the way i solved my problem.

   Dim cnn As ADODB.Connection
   Set cnn = New ADODB.Connection
   Dim rs As ADODB.Recordset
   Dim vsql As String
   Dim titi As String

   ' Open a connection using an ODBC.
   cnn.ConnectionString = "OraOLEDB.Oracle;UID=qf_mdaniel;PWD=melastomo;Data Source=space;"
   cnn.Open
      
   Set rs = cnn.Execute("select * from qf_spacep.w_qf_ssm_data where flight_no<=0010 and product_code<>'DUMMY';")
   rs.MoveFirst
   Do
        MsgBox rs.Fields.Item("dep_port_code") & rs.Fields.Item("arr_port_code") & rs.Fields.Item("uplift_port_code") & rs.Fields.Item("frequency")
        rs.MoveNext
   Loop Until rs.EOF

thx again 4 your time and hlp, max.