Foreign characters in SQL statement

[Jacob Havkrog]

Hi.

I've come to beleive that MS Access supports the use of national characters
like Danish letters ÆØÅ, ie letters outside the A-Z range.

If you just enclose tabel and field names in square bracket, things are
fine.

OK, until you need to create a constraint, it seems. I try this:

ALTER TABLE [Kørsler]
ADD CONSTRAINT RunIDRef
FOREIGN KEY ([KørselsID])
REFERENCES Posteringer ([KørselsID])
ON DELETE CASCADE ON UPDATE CASCADE

and get the error message, that "KørselsID" is an illegal name for a
constraint definition.

It's a little late for Access to tell me this, because this database is
already in use many places....

What can I do to create this constraint? Thanks for any help!

Jacob

 

[Jacob Havkrog]

Hi.

I need to add, that I use ADO to get to the database from my application.

Could I use ADOX to do the job? Can you point me to some code samples ?

Thanks!!!

Jacob

 

[Brendan Reynolds]

Your SQL statement worked for me, successfully creating the relationship,
when executed via ADO in Access 2003. Here's the code that worked for me ...

Public Sub CreateConstraint()

Dim strSQL As String
strSQL = "ALTER TABLE [Kørsler] " & _
"ADD CONSTRAINT RunIDRef " & _
"Foreign Key([KørselsID]) " & _
"References Posteringer([KørselsID]) " & _
"ON DELETE CASCADE ON UPDATE CASCADE"
CurrentProject.Connection.Execute strSQL

End Sub

 

[Jacob Havkrog]

Hi !

It now works. There were 2 problems that prevented me from seeing the light.

1) I had primary and secondary table mixed up. I needed to add the
constraint to the secondary table. I hadn't realised that, but I guess it's
logical when you think about it.

2) The Access IDE gives me a syntax error when I try to issue the correct
SQL statement. Using my own little helper application to issue the same SQL
statement via ADO works !!

I'm a bit surprised about this. Its Access 2003 SP2, I keep it up-to-date.

Thanks for your help! It's a big relief.

Jacob Havkrog

 

[Brendan Reynolds]

I haven't tested this, but you might be able to execute the SQL statement
via the Access UI with the ANSI 92 option enabled. (From the Tools menu,
select Options. In the Options dialog, select the Tables/Queries tab. The
'SQL Server Compatible Syntax (ANSI 92)' option is down near the bottom
right-hand corner.)

There are some issues with this option, though. If you want to try it, my
advice is ...

a) Read the KB article at the following URL:
http://support.microsoft.com/kb/824189/en-us

b) Make a copy of the MDB before enabling the option.

c) Carefully read and consider the warning that Access displays when you
enable the option.

 

[Jacob Havkrog]

Thanks -

I don't use forms, so that might be an idea. For the time being, I'm happy
the the current solution, as I only use Access as a convenient way to
inspect my data. The application I'm developing does not rely on Access,
only on ADO.

Best regards