Import DDL database structure to Access database

[Minty]

It's because a single field isn't unique in a composite index. Plain and Simple.
You can't set a constraint on a non-unique field to a non-unique field.

 

[jaryszek]

i have composite keys as primary keys in both tables. What i know is that primary key is unique always from design. And if i am using composite primary key they have already index created.

i do not undetsrand what you mean Minty

 

[Minty]

So you can create the constraint correctly yes?

ALTER TABLE YourTableName
ADD CONSTRAINT
ConstraintName UNIQUE NONCLUSTERED
(
    Column01,
    Column02,
    Column03
)

This page gives you a complete one to one example https://www.tech-recipes.com/rx/56738/one-to-one-one-to-many-table-relationships-in-sql-server/

 

[jaryszek]

Thanks:

the error i am getting is like you see.
Not working still ech.

sqlS = "ALTER TABLE tblCustomers " & _
"ADD CONSTRAINT " & _
"CustomerCon UNIQUE NONCLUSTERED" & _
"(" & _
    "LastName," & _
    "FirstName" & _
")"

but it worked with out NONCLUSTERED - maybe this is SQL Server only?

And what next ?

why primary keys can not be used as joined fields?

Jacek

 

[The_Doc_Man]

Can't address the question about CONSTRAINT syntax. My "big boy" database experience is with ORACLE and a non-Access FE. Have to leave that to others more familiar with SQL Server and its variants.

However, the fact that you have the same two fields participating in two indexes might confuse Access more than just a little bit. It would probably matter considerably as to which index name you used for the relationships. Access really doesn't like it when you give it mixed signals - like, "Which index to use?" when there are two apparently identical options.

I would bet that if you switched from InvoicesCon to PrimaryKey in that little "Indexes" display, the only thing that would change would be that "Primary" would be YES for that one.

It is your database and of course you are more familiar with it - but people's names are usually very poor choices as PKs. Given you have only First name and Last name, my dad and I couldn't be in your database together. Because we have the same names except he was "Senior" and I was "Junior."

 

[Minty]

I couldn't enforce referential integrity in the UI for a join of that type, which means you probably can't code it either.

 

[jaryszek]

Minty,

you mean cant code by VBA?

Jacek

 

[Minty]

I couldn't make it work, but it is not something I have ever tried to do in Access.

I wonder if the amount of effort you are expending on this far outweighs any potential benefit.

 

[jaryszek]

We will see i hope it will be useful.
Maybe i will try to do this via VBA...

Jacek

 

[CodeByJim]

You sent an example of your data in an excel spreadsheet. Why don't you simply export the data to an Access table. Edit your tables and then set up Access relationships.

 

[CodeByJim]

If you have CSV data in a text file, you can read each string into VBA and use the Split function to create a one dimension array which can be saved into a pre-designed table. I use CSV strings a lot when handling data movement into and out of my tables.