Using DDL to Create Constraint - Syntax Error

  • Thread starter Thread starter John Spencer
  • Start date Start date
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
..
 
Hi John,

Did you try using

CurrentProject.Connection.Execute strSQL, dbFailOnError

as in maybe one of those ddl's that DAO cannot do?

just a guess,

gary

"John Spencer"wrote:
 
No I did not try that.

I'm not sure how I would do that since I am trying to execute the code
against the backend from the front end.

I decided to just use DAO and VBA code to create a relation in the backend
using CreateRelation method. That worked - although I would really liked to
have done it with DDL.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
good question...I suspect like:

Dim oConn As ADODB.Connection
Dim oCmd As ADODB.Command

Dim strTargetDb As String

'Get path to backend database
strTargetDb = CurrentDb().TableDefs("tblTests").Connect
strTargetDb = Mid(strTargetDb, Len(";Database=") + 1)

' Create and open a new Connection object
Set oConn = New ADODB.Connection
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strTargetDb & ";" & _
"User ID=admin;" & _
"Password=;"

' Create a new Command object
Set oCmd = New ADODB.Command
With oCmd

' Set Command properties
.ActiveConnection = oConn
.CommandType = adCmdText
.CommandText = "ALTER TABLE tblSessionCurriculumModules" & _
" ADD CONSTRAINT fkSessionCurrModules FOREIGN KEY (SessionID)" & _
" REFERENCES tblSessions ( SessionID )" & _
" ON DELETE CASCADE "

' add constraint
.Execute , , adExecuteNoRecords

End With

//////////// OR /////
Dim oConn As ADODB.Connection
Dim strSQL As String

Dim strTargetDb As String


'Get path to backend database
strTargetDb = CurrentDb().TableDefs("tblTests").Connect
strTargetDb = Mid(strTargetDb, Len(";Database=") + 1)



' Open a new connection
Set oConn = New ADODB.Connection
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strTargetDb & ";"

' add constraint
strSQL = "ALTER TABLE tblSessionCurriculumModules" & _
" ADD CONSTRAINT fkSessionCurrModules FOREIGN KEY (SessionID)" & _
" REFERENCES tblSessions ( SessionID )" & _
" ON DELETE CASCADE "
oConn.Execute sSQL, , adCmdText + adExecuteNoRecords
 
Back
Top