Using DDL to Create Constraint - Syntax Error

[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
..

 

[Gary Walter]

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:

 

[John Spencer]

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
..

 

[Gary Walter]

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

 

[Gary Walter]

last line should be (argh!)

oConn.Execute strSQL, , adCmdText + adExecuteNoRecords