Martin Reid
mwp.reid at qub.ac.uk
Wed Jan 25 12:28:18 CST 2006
Been a while but I think you may need SQL Net in addition to the ODBC drivers for Oracle but not 100% as its been some years form I used Access with Oracle.
Bit of code from net below.
Option Compare Database
Option Explicit
Function AttachTable() As Variant
On Error GoTo AttachTable_Err
Dim db As Database
Dim tdef As TableDef
Dim strConnect As String
Set db = CurrentDb()
strConnect = "ODBC;DSN=oraweb;DBQ=qit-uq-cbiw_oraweb;DATABASE="
' NOTE: DSN is your ODBC Data Source Name; DBQ is your TNSNAMES.ORA entry name
Set tdef = db.CreateTableDef("MY_ACCESS_TABLENAME")
' tdef.Attributes DB_ATTACHEDODBC
tdef.Connect = strConnect
tdef.SourceTableName = "MY_ORACLE_TABLENAME"
db.TableDefs.Append tdef
AttachTable_Exit:
Exit Function
AttachTable_Err:
MsgBox "Error: " & Str(Err) & " - " & Error$ & " occured in global module."
Resume AttachTable_Exit
End Function
Martin WP Reid
Training and Assessment Unit
Riddle Hall
Belfast
tel: 02890 974477
________________________________
From: dba-tech-bounces at databaseadvisors.com on behalf of DJK(John) Robinson
Sent: Wed 25/01/2006 18:23
To: 'Discussion of Hardware and Software issues'
Subject: Re: [dba-Tech] Access/Oracle
You can certainly reach Oracle tables from Access as front end via ODBC. I
haven't done it recently - not since 2005 - so I'm rusty on details, but it
should NOT be a big deal!
(Setting it up initially needs: permissions obviously, DSN, and - uh -
something else Oracle-oriented on your PC I think. Sorry, brain is
elsewhere!)
John
-----Original Message-----
From: dba-tech-bounces at databaseadvisors.com
[mailto:dba-tech-bounces at databaseadvisors.com] On Behalf Of Tesiny, Ed
Sent: 25 January 2006 17:27
To: Discussion of Hardware and Software issues
Subject: [dba-Tech] Access/Oracle
To access our agency's data we have to use this %$#@& Oracle tool called
Discoverer. Could one access Oracle tables using Access as a front end
through an ODBC connection. If so, would it be a big deal to set this up.
The last time I asked, the IT answer was we can't do that. Never got a why
we can't do that??? Anyway, that person is now gone and I'm wondering if I
should give it another try. Any thoughts/advice appreciated. Ed
Edward P. Tesiny
Assistant Director for Evaluation
Bureau of Evaluation and Practice Improvement
New York State OASAS
1450 Western Ave.
Albany, New York 12203-3526
Phone: (518) 485-7189
Fax: (518) 485-5769
Email: EdTesiny at oasas.state.ny.us
_______________________________________________
dba-Tech mailing list
dba-Tech at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-tech
Website: http://www.databaseadvisors.com
_______________________________________________
dba-Tech mailing list
dba-Tech at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-tech
Website: http://www.databaseadvisors.com