Dan Waters
dwaters at usinternet.com
Tue Jul 8 08:31:49 CDT 2008
Mark,
This is part of the code I use to automatically relink to tables. In my
scenario, I move a FE file from my PC to a customer server and back again.
When this happens, the code below will automatically relink to tables when
the FE is opened if needed.
This code is for DAO - I don't know what mods might be needed for Oracle.
Hope this helps!
Dan
'----------------------------------------------------
Public Sub RelinkFrontEndTablesDemo()
'-- This will verify that the table links to the BE are correct. Only
one table is checked.
Dim tdfBE As DAO.TableDef
Dim tdfTT As DAO.TableDef
Dim stgMessage As String
Dim stgSystemFolderPath As String
Dim stgSystemBEName As String
Dim stgBEPath As String
Dim stgTTPath As String
Dim dbs As DAO.Database
'-- Get BE path
stgBEPath = BEFullPath '-- BEFullPath is a function returning a string
path to the BE file
'-- Set tdf variables for FE Database
Set dbs = DBEngine(0)(0)
Set tdfBE = dbs.TableDefs("tblPeopleMain")
Set tdfTT = dbs.TableDefs("tblTTOpenItemsList")
'-- Is relinking needed?
stgTTPath = CodeProject.Path & "\PSITempTables.mdb"
If tdfBE.Connect = ";DATABASE=" & stgBEPath And tdfTT.Connect =
";DATABASE=" & stgTTPath Then
dbs.Close
Exit Sub
End If
dbs.Close
'-- Attempt to relink
If RelinkingTables(stgBEPath, stgTTPath, "Front End") = False Then
stgMessage = "Automatic Front End Table Re-Linking was NOT
Successful." _
& vbNewLine & vbNewLine _
& "Table Name = " & MstgTableName _
& vbNewLine & vbNewLine _
& "Contact your System Owner." _
& vbNewLine & vbNewLine _
& "The system will now shut down.@ @"
MsgBox GstgReminder, stgMessage, vbCritical + vbOKOnly, "Re-Linking
Not Successful"
DoEvents
Application.Quit
End If
End Sub
'----------------------------------------------------
Private Function RelinkingTablesDemo(stgBEPath As String, stgTTPath As
String, stgName As String) As Boolean
Dim tdf As DAO.TableDef
Dim dbs As DAO.Database
Dim intStatus As Integer
Dim varStatus As Variant
Dim stgtest As String
Dim intCustomerLibrary As Integer
Dim stgCurrentPCName As String
'-- Set correct database
If stgName = "Library" Then
Set dbs = CodeDb
Else
Set dbs = DBEngine(0)(0)
End If
'-- Set up progress bar
varStatus = SysCmd(acSysCmdInitMeter, "Refreshing " & stgName & " Table
Links . . .", dbs.TableDefs.Count)
intStatus = 0
'-- Relink the table if it is a link
For Each tdf In dbs.TableDefs
If (tdf.Attributes And dbAttachedTable) Then
MstgTableName = tdf.Name
If Left(tdf.Name, 5) = "tblTT" Then
'-- Temp Table Path
If tdf.Connect <> ";DATABASE=" & stgTTPath Then
tdf.Connect = ";DATABASE=" & stgTTPath
tdf.RefreshLink
End If
Else
'-- BE Path
If tdf.Connect <> ";DATABASE=" & stgBEPath Then
tdf.Connect = ";DATABASE=" & stgBEPath
tdf.RefreshLink
End If
End If
End If
intStatus = intStatus + 1
varStatus = SysCmd(acSysCmdUpdateMeter, intStatus)
DoEvents
Next tdf
varStatus = SysCmd(acSysCmdClearStatus)
RelinkingTablesDemo = True
dbs.Close
End Function
'----------------------------------------------------
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Sheri Hixson
Sent: Monday, July 07, 2008 11:10 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Same tables, Different data source (ODBC)
Sherri
When we were discussing Berrien last week - did you approve moving it to
live? I remember the discussion, but not the outcome!
Let me know,
Thanks,
Sheri
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Joe O'Connell
Sent: Monday, July 07, 2008 11:05 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Same tables, Different data source (ODBC)
Mark,
I have not worked with Oracle databases, but I have used code similar to
this to change table links to different SQL Server databases located on
the same server. If the first characters of the connection string for
Oracle are not "ODBC", then change the value assigned to strSQLPrefix.
You should also add error checking.
Joe O'Connell
Public Sub ChangeLinks(ByVal strDatabase As String)
Dim lngBeg As Long
Dim lngEnd As Long
Dim strConn As String
Dim tdf As TableDef
Dim strSQLPrefix As String
strSQLPrefix = "ODBC"
For Each tdf In CurrentDb.TableDefs
If Len(tdf.Connect) > 0 Then
If Left$(tdf.Connect, Len(strSQLPrefix)) = strSQLPrefix Then
strConn = tdf.Connect
lngBeg = InStr(1, strConn, "DATABASE=") + 8
lngEnd = InStr(lngBeg, tdf.Connect, ";")
strConn = Left(tdf.Connect, lngBeg) & strDatabase _
& Right(tdf.Connect, Len(tdf.Connect) - lngEnd +
1)
tdf.Connect = strConn
tdf.RefreshLink
End If
End If
Next tdf
End Sub
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Mark Porter
Sent: Monday, July 07, 2008 6:50 PM
To: accessd at databaseadvisors.com
Subject: [AccessD] Same tables, Different data source (ODBC)
I have an app that links to tables in an Oracle DB. I'd like to be able
to quickly change that environment from the Dev to the Test or Prod (the
tables linked will stay the same).
Does anyone have any code or techniques to do this quickly?
Thanks in advance,
Mark Porter
Sr. Technologist
Nana Development Corp.
Desk: 907-265-4156
Fax: 907-343-5656
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com