J
John Spencer
I am running into a problem with the following code that I am testing. I
get a syntax error on the third DDL SQL statement.
I can't see what I am doing wrong. Perhaps what I am attempting is not
possible using DDL and I will have to use DAO to build the relationship?
-- Access 2003 msb (Access 2000 format);
-- Split FE-BE where I need to update the application at a remote location
The one-table is tblSessions
The many-table is tblSessionCurriculum Modules
Private Function fDUmmy()
Dim strTargetDb As String
Dim dbsTemp As DAO.Database
Dim strSQL As String
On Error GoTo ERROR_fDUmmy
'Get path to backend database
strTargetDb = CurrentDb().TableDefs("tblTests").Connect
strTargetDb = Mid(strTargetDb, Len(";Database=") + 1)
Set dbsTemp = DBEngine.Workspaces(0).OpenDatabase(strTargetDb)
'Works - No error
strSQL = "ALTER TABLE tblSessionCurriculumModules" & _
" ADD CONSTRAINT fkSessionCurrModules FOREIGN KEY (SessionID)" &
_
" REFERENCES tblSessions ( SessionID )"
dbsTemp.Execute strSQL, dbFailOnError
'Works - No Error
strSQL = "ALTER TABLE tblSessionCurriculumModules" & _
" DROP CONSTRAINT fkSessionCurrModules"
dbsTemp.Execute strSQL, dbFailOnError
'Does not work - Syntax error
strSQL = "ALTER TABLE tblSessionCurriculumModules" & _
" ADD CONSTRAINT fkSessionCurrModules FOREIGN KEY (SessionID)" &
_
" REFERENCES tblSessions ( SessionID ) " & _
" ON DELETE CASCADE "
dbsTemp.Execute strSQL, dbFailOnError
EXIT_fDUmmy:
On Error GoTo 0
Exit Function
ERROR_fDUmmy:
MsgBox Err.Number & ": " & Err.Description, , "zzmodChangesToPRS : fDUmmy"
Resume EXIT_fDUmmy
End Function
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
get a syntax error on the third DDL SQL statement.
I can't see what I am doing wrong. Perhaps what I am attempting is not
possible using DDL and I will have to use DAO to build the relationship?
-- Access 2003 msb (Access 2000 format);
-- Split FE-BE where I need to update the application at a remote location
The one-table is tblSessions
The many-table is tblSessionCurriculum Modules
Private Function fDUmmy()
Dim strTargetDb As String
Dim dbsTemp As DAO.Database
Dim strSQL As String
On Error GoTo ERROR_fDUmmy
'Get path to backend database
strTargetDb = CurrentDb().TableDefs("tblTests").Connect
strTargetDb = Mid(strTargetDb, Len(";Database=") + 1)
Set dbsTemp = DBEngine.Workspaces(0).OpenDatabase(strTargetDb)
'Works - No error
strSQL = "ALTER TABLE tblSessionCurriculumModules" & _
" ADD CONSTRAINT fkSessionCurrModules FOREIGN KEY (SessionID)" &
_
" REFERENCES tblSessions ( SessionID )"
dbsTemp.Execute strSQL, dbFailOnError
'Works - No Error
strSQL = "ALTER TABLE tblSessionCurriculumModules" & _
" DROP CONSTRAINT fkSessionCurrModules"
dbsTemp.Execute strSQL, dbFailOnError
'Does not work - Syntax error
strSQL = "ALTER TABLE tblSessionCurriculumModules" & _
" ADD CONSTRAINT fkSessionCurrModules FOREIGN KEY (SessionID)" &
_
" REFERENCES tblSessions ( SessionID ) " & _
" ON DELETE CASCADE "
dbsTemp.Execute strSQL, dbFailOnError
EXIT_fDUmmy:
On Error GoTo 0
Exit Function
ERROR_fDUmmy:
MsgBox Err.Number & ": " & Err.Description, , "zzmodChangesToPRS : fDUmmy"
Resume EXIT_fDUmmy
End Function
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..