Split database oops

  • Thread starter Amy Blankenship
  • Start date
A

Amy Blankenship

Hi, all;

I have split my database to allow me to continue to work on the FE while the
client uses the existing FE to enter production data into their local BE.
The scenario is this: I pass them a new FE that, upon startup, looks in the
same directory as itself for the BE and reconnects. The reason I've done
this is they are 2 states away, so a typical LAN based FE/BE isn't possible.
So far, so good.

But now I discover I need to add a field to one of their tables. Is there a
way to add code to the FE startup code which, upon startup, looks at that BE
table and, if it lacks a certain field, will add it? The code (modified
from Dev Ashish's code) is this:

Function fRefreshLinks() As Boolean
Dim strMsg As String, collTbls As Collection
Dim i As Integer, strDBPath As String, strTbl As String
Dim dbCurr As Database, dbLink As Database
Dim tdfLocal As TableDef
Dim varRet As Variant
Dim strNewPath As String, dbName As String

Const cERR_USERCANCEL = vbObjectError + 1000
Const cERR_NOREMOTETABLE = vbObjectError + 2000

On Local Error GoTo fRefreshLinks_Err
dbName = Application.CodeProject.Name
strNewPath = Application.CodeProject.Path & "\" & Left(dbName,
InStr(dbName, ".") - 1) & "_BE." & Right(dbName, Len(dbName) -
(InStr(dbName, ".")))


'First get all linked tables in a collection
Set collTbls = fGetLinkedTables

'now link all of them
Set dbCurr = CurrentDb

For i = collTbls.Count To 1 Step -1
strDBPath = fParsePath(collTbls(i))
strTbl = fParseTable(collTbls(i))
varRet = SysCmd(acSysCmdSetStatus, "Now linking '" & strTbl &
"'....")
If Left$(strDBPath, 4) = "ODBC" Then
'ODBC Tables
'ODBC Tables handled separately
' Set tdfLocal = dbCurr.TableDefs(strTbl)
' With tdfLocal
' .Connect = pcCONNECT
' .RefreshLink
' collTbls.Remove (strTbl)
' End With
Else
If strNewPath <> vbNullString Then
'Try this first
strDBPath = strNewPath
Else
If Len(Dir(strDBPath)) = 0 Then
'File Doesn't Exist, call GetOpenFileName
'strDBPath = fGetMDBName("'" & strDBPath & "' not
found.")
If strDBPath = vbNullString Then
'user pressed cancel
err.Raise cERR_USERCANCEL
End If
End If
End If

'backend database exists
'putting it here since we could have
'tables from multiple sources
Set dbLink = DBEngine(0).OpenDatabase(strDBPath)

'check to see if the table is present in dbLink
strTbl = fParseTable(collTbls(i))
If fIsRemoteTable(dbLink, strTbl) Then
'everything's ok, reconnect
Set tdfLocal = dbCurr.TableDefs(strTbl)
With tdfLocal
.Connect = ";Database=" & strDBPath
.RefreshLink
collTbls.Remove (.Name)
End With
Else
err.Raise cERR_NOREMOTETABLE
End If
End If
Next
fRefreshLinks = True
varRet = SysCmd(acSysCmdClearStatus)
MsgBox "All Access tables were successfully reconnected.", _
vbInformation + vbOKOnly, _
"Success"

fRefreshLinks_End:
Set collTbls = Nothing
Set tdfLocal = Nothing
Set dbLink = Nothing
Set dbCurr = Nothing
Exit Function
fRefreshLinks_Err:
fRefreshLinks = False
Select Case err
Case 3059:

Case cERR_USERCANCEL:
MsgBox "No Database was specified, couldn't link tables.", _
vbCritical + vbOKOnly, _
"Error in refreshing links."
Resume fRefreshLinks_End
Case cERR_NOREMOTETABLE:
MsgBox "Table '" & strTbl & "' was not found in the database" &
_
vbCrLf & dbLink.Name & ". Couldn't refresh links", _
vbCritical + vbOKOnly, _
"Error in refreshing links."
Resume fRefreshLinks_End
Case Else:
strMsg = "Error Information..." & vbCrLf & vbCrLf
strMsg = strMsg & "Function: fRefreshLinks" & vbCrLf
strMsg = strMsg & "Description: " & err.Description & vbCrLf
strMsg = strMsg & "Error #: " & Format$(err.Number) & vbCrLf
MsgBox strMsg, vbOKOnly + vbCritical, "Error"
Resume fRefreshLinks_End
End Select
End Function

Function fIsRemoteTable(dbRemote As Database, strTbl As String) As Boolean
Dim tdf As TableDef
On Error Resume Next
Set tdf = dbRemote.TableDefs(strTbl)
fIsRemoteTable = (err = 0)
Set tdf = Nothing
End Function


Function fGetLinkedTables() As Collection
'Returns all linked tables
Dim collTables As New Collection
Dim tdf As TableDef, db As Database
Set db = CurrentDb
db.TableDefs.Refresh
For Each tdf In db.TableDefs
With tdf
If Len(.Connect) > 0 Then
If Left$(.Connect, 4) = "ODBC" Then
' collTables.Add Item:=.Name & ";" & .Connect, KEY:=.Name
'ODBC Reconnect handled separately
Else
collTables.Add Item:=.Name & .Connect, Key:=.Name
End If
End If
End With
Next
Set fGetLinkedTables = collTables
Set collTables = Nothing
Set tdf = Nothing
Set db = Nothing
End Function

Function fParsePath(strIn As String) As String
If Left$(strIn, 4) <> "ODBC" Then
fParsePath = Right(strIn, Len(strIn) _
- (InStr(1, strIn, "DATABASE=") + 8))
Else
fParsePath = strIn
End If
End Function

Function fParseTable(strIn As String) As String
fParseTable = Left$(strIn, InStr(1, strIn, ";") - 1)
End Function
'***************** Code End ***************

Sub showref()
Dim ref As Reference
For Each ref In References
Debug.Print ref.Name & " " & ref.FullPath
Next ref
End Sub

TIA;

Amy
 
M

Marshall Barton

Amy said:
I have split my database to allow me to continue to work on the FE while the
client uses the existing FE to enter production data into their local BE.
The scenario is this: I pass them a new FE that, upon startup, looks in the
same directory as itself for the BE and reconnects. The reason I've done
this is they are 2 states away, so a typical LAN based FE/BE isn't possible.
So far, so good.

But now I discover I need to add a field to one of their tables. Is there a
way to add code to the FE startup code which, upon startup, looks at that BE
table and, if it lacks a certain field, will add it?
[snip the code]


After you have determined the path to the backend file, you
can use SQL DDL to create the field. E.g.

Dim dbBE as Database
Dim SQL As String

Set dbBE = OpenDatabase(backendpath)
SQL = "ALTER TABLE ADD COLUMN fieldname fieldtype . . . "
dbBE.Execute SQL, dbFailOnError
Set dbBE = Nothing

Be sure to check Access Help - Contents - Jet SQL Reference
- Data Definition Language for details of the ALTER TABLE
statement.
 
A

Amy Blankenship

OK, thanks.

-Amy
Marshall Barton said:
Amy said:
I have split my database to allow me to continue to work on the FE while
the
client uses the existing FE to enter production data into their local BE.
The scenario is this: I pass them a new FE that, upon startup, looks in
the
same directory as itself for the BE and reconnects. The reason I've done
this is they are 2 states away, so a typical LAN based FE/BE isn't
possible.
So far, so good.

But now I discover I need to add a field to one of their tables. Is there
a
way to add code to the FE startup code which, upon startup, looks at that
BE
table and, if it lacks a certain field, will add it?
[snip the code]


After you have determined the path to the backend file, you
can use SQL DDL to create the field. E.g.

Dim dbBE as Database
Dim SQL As String

Set dbBE = OpenDatabase(backendpath)
SQL = "ALTER TABLE ADD COLUMN fieldname fieldtype . . . "
dbBE.Execute SQL, dbFailOnError
Set dbBE = Nothing

Be sure to check Access Help - Contents - Jet SQL Reference
- Data Definition Language for details of the ALTER TABLE
statement.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top