Ciao Martino,
which Version of Upsize Wizard are you using?
Last week my colleague and me tried to do just the same thing. we found out,
that there are problems with the INSERT- and UPDATE - triggers if you are using
a foreign key. It doesn't handle the case that the field is empty. So we looked
at the sourcecode of Update Wizard and changed the part which creates the
trigger.
This is the original version:
'stSQL = stSQL & " (SELECT COUNT(*) FROM " & stMaster & ", _
inserted WHERE (" & stRestr & "))" & vbCrLf
Here is the modification which should work:
stSQL = stSQL & " ((SELECT COUNT(*) FROM " & stMaster & ", _
inserted WHERE (" & stRestr & ")) + "
stSQL = stSQL & "(SELECT COUNT(*) FROM inserted WHERE " & _
stRestrNull & "))" & vbCrLf
stRestrNull is a string which keeps the Condition that the foreign key field
could be empty. It is determined by a function like :
stRestrNull = StBuildRelRestrNull(rel, "inserted", stSeperator:="AND")
Tis changes have to be made in the parts commented with: "SLAVE INSERT TRIGGER"
and "SLAVE UPDATE TRIGGER".
The source code that function could look like:
Function StBuildRelRestrNull(rel As Relation, ByVal stOtherTable As String, _
ByVal stSeperator As String) As String
Dim stSQL As String
Dim fd As Field
Dim stForeignName As String
stSQL = ""
For Each fd In rel.Fields
If stSQL <> "" Then stSQL = stSQL & " " & stSeperator & " "
stForeignName = UT_StRemotizeName(fd.ForeignName)
stSQL = stSQL & stOtherTable & "." & stForeignName & " IS NULL"
Next fd
StBuildRelRestrNull = stSQL
End Function
Probably the problem will be fixed for a later version of Update Wizard. Till
then I hope this helps.
Sebastian
Quote:> I'm trying a porting of an MS Access 97 DB to SQL Server 7.0.
> First of all, I used MS Access Upsizing Tool: it's almost all OK (it created
> tables and triggers) but, since my table names and field names contain some
> blanks, the tool created tables and fields with modified names and Access
> queries allowing Access to work with them.
> Since SQL Server 7.0 allows blanks in table and field names, I tried to
> import my DBin Access 2000.
> It works fine both for tables and relationships.
> In Access 2000 I can then use the Upsize Wizard that I find in the tools
> menu.
> Tables and delete triggers are imported well, but update and insert triggers
> aren't. Why????
> Any suggestions?
> Thanks
> Martino Gusmini
by