Using the same access application for multiple database environments

[Terrell Miller]

-----Original Message-----
I have 4 different Oracle database environments(test,

production, ect..) that have the same table structure. I
need to write an access application that will work for all
environments. If a database change is made which is
applied to all 4 Oracle instances, I want to be able to go
into my access application and make the application change
needed, one time, and not have to make the change for all
environments. If anyone can shed some light on this, I'd
appreciate it.

Connie,

If the tables will be identical, then what you can do is:

* set up separate DSNs for each Oracle database.

* In Access, link to the tables of one Oracle database

* Build your application around that data source, with
whatever queries, forms and reports you need.

* When you need to change to another database, you just
need to go into Tools/Database Applications/Linked Table
Manager. Click the "Select All" button and make sure
the "Always prompt for new location" checkbox is checked,
then click OK. The next dialog box will ask you for the
DSN you want to connect with. Choose whichever one you
need. (Sounds like you may wind up with separate Access
front-ends one for each of your databases, in which case
you'd choose the DSN for each one).

That will change the table links to the new database, and
the rest of your application will never know the
difference. It's still pulling data from the "same" table
as far as Access is concerned.

You'd also use this process when teh design of a
particular table changes, except that you'd only need to
select the appropriate table(s) instead of Select All, and
you wouldn't check the "always prompt for new location"
box.

HTH,

 

[Joe Fallon]

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

--
Joe Fallon
Access MVP

Thank you for the information. It works great on my small tables, but

when I go to switch the database in our mega size tables I believe it must
be selecting all the data from the tables, because my machine just hung on
this. I waited a good 10 minutes and it only had one mark on the progress
bar. This is unfortunate, because I was pretty excited about the solution
you provided me. Thanks again.