Access-Oracle connectivity

  • Thread starter Thread starter Sanjay
  • Start date Start date
S

Sanjay

Hi,

I am starting on a small project which involves developing
front end in Access 2002 and on the back end it would be
Oracle connected via odbc. I want to find out what are the
important things that i need to be aware. what would be
different as compared to developing a application in
access 2002 only (front end and back end). any help in
this regard will be appreciated. or if some one can point
to some documents or refernece that would be great.

thanks
 
There may be some data types that are a problem.
Look in MSKB if you run into one.
For regular data it works fine.

Here is how I re-link ODBC tables:

I use this procedure to re-create links to Oracle.
There is a local Access table (tblODBCTables) that contains the table names
and primary key fields I want to link to on the Server.
Note: the source table name needs the Schema User prefix which is in the
code. The linked table name usually omits this.

Public Function LinkOracleTables(strDSN As String, strSchema As Variant,
strSchemaPwd As Variant) As Boolean
On Error GoTo Err_LinkOracleTables

Dim db As Database, rs As Recordset, tdfAccess As TableDef, qdf As
QueryDef
Dim dbODBC As Database, strConnect As String, strSQL As String

If strDSN = "" Then
MsgBox "You must supply a DSN in order to link tables."
Exit Sub
Else
strConnect = "ODBC;DSN=" & strDSN & ";UID=xyz" & ";PWD=abc;"
End If

SysCmd acSysCmdSetStatus, "Connecting to Oracle..."

Call DeleteODBCTableNames

Set db = CurrentDb
Set rs = db.OpenRecordset("tblODBCTables")
Set dbODBC = OpenDatabase("", False, False, strConnect)
DoCmd.SetWarnings False

Do While Not rs.EOF
Set tdfAccess = db.CreateTableDef(rs![LinkTablename], dbAttachSavePWD)
tdfAccess.Connect = dbODBC.Connect
tdfAccess.SourceTableName = strSchema & "." & rs![LinkTablename]
db.TableDefs.Append tdfAccess
'run pseudo index queries here. If the table does not exist then this
gets skipped.
strSQL = "CREATE INDEX " & rs![LinkTablename] & "Idx ON " &
rs![LinkTablename] & " (" & rs![IndexFields] & ");"
DoCmd.RunSQL strSQL
TableNotInCollection:
rs.MoveNext
Loop

LinkOracleTables = True

Exit_LinkOracleTables:
On Error Resume Next
DoCmd.SetWarnings True
rs.Close
Set rs = Nothing
Set dbODBC = Nothing
Set db = Nothing
SysCmd acSysCmdClearStatus
Exit Function

Err_LinkOracleTables:
Select Case Err.Number
Case 3151
MsgBox ("There is an ODBC datasource problem." & vbCrLf & "Please
verify the DSN and database are spelled correctly." & vbCrLf & "Note: They
can be case sensitive.")
Case 3265, 3011, 7874 'item not in collection - table does not exist, or
can't find object
Resume TableNotInCollection
Case Else
MsgBox "Error # " & Err.Number & " was generated by " & Err.Source &
vbCrLf & Err.Description, , "LogOnCode - LinkOracleTables"
End Select
LinkOracleTables = False
Resume Exit_LinkOracleTables

End Function

'This procedure deletes all linked ODBC table names in an mdb.
Public Sub DeleteODBCTableNames()
On Error GoTo Err_DeleteODBCTableNames

Dim dbs As Database, tdf As TableDef, I As Integer
Set dbs = CurrentDb
For I = dbs.TableDefs.Count - 1 To 0 Step -1
Set tdf = dbs.TableDefs(I)
If (tdf.Attributes And dbAttachedODBC) Then
dbs.TableDefs.Delete (tdf.Name)
End If
Next I

dbs.Close
Set dbs = Nothing

Exit_DeleteODBCTableNames:
Exit Sub

Err_DeleteODBCTableNames:
MsgBox ("Error # " & Str(Err.Number) & " was generated by " & Err.Source
& Chr(13) & Err.Description)
Resume Exit_DeleteODBCTableNames

End Sub
 
Thank you very much for the reply, i really appreciate
your help. One thing i was wondering is where do i put
this code. should i create a module and run it at startup
of access application or i just put it in startup event of
the my main front end screen.

please adivse.

once again thank you very much.
 
Either is fine.
I have it in a module.

Remember that Module names should never duplicate any method names.
 
Thanx for the response.
I was going through the code and you have mentionied to
use it for relinking the ODBC tables. Please correct my
understanding.

My understanding is i would use this code as startup (as
discussed earlier) and every time access is opened (odbc
tables are opened this codes need to be executed). i dont
understand why would we relink. it should be a link
instead. please advise. what will be the difference in
link and relink, and at what times we need relinking the
odbc tables.

Please advise.

Thanx.
 
Access caches the link so at startup you provide the user with 2 options:
1. Keep Existing Link
2. Re-Link (to some other database)

The very first time a user uses the app, they should do a re-link so the
current link is cached.
Then every time after that they never have to link again (unless they want
to switch databases. Like between a Test and a Production environment.)
 
I am able to link the table from access 2002 to Oracle via
odbc connectivity. (im using oracle 9206 driver version).
this is a very small database and it has only one table
with 275 records, but the access time is very slow and to
move from one record to another takes about 7-8 seconds in
tabular view.

what can be the possible places to lookin to fix the
access speed.

thank you very much for all the help.
 
Never open the linked table and try to navigate it.
It is excruciatingly slow on As400.
Oracle should be better but network connectivity may be an issue.
Write queries and return rows.
 
Back
Top