Import DDL database structure to Access database

[jaryszek]

Thank you so much for your help,

Still didnt work.
I used your sql Pat:

sqlS = "ALTER TABLE tblInvoices ADD CONSTRAINT FKLastName PRIMARY KEY ([LastName], [FirstName]) REFERENCES tblCustomers ([LastName], [FirstName])"

But i am getting syntax error in ALTER statement...
Maybe it is not working at all or it is crazy SQL

Best,
Jacek

 

[jaryszek]

Thank you. I tried. Maybe microsoft didnt implement this feature?

 

[The_Doc_Man]

This is a delicate issue. There is almost never a time when you would seriously implement a 1-to-1 relationship in any table, manually or through code. With very few exceptions, having such a relationship makes it questionable as to whether the tables involved should be separate tables or one table. Seeking to make a 1/1 relationship makes it almost impossible to enforce relational integrity through any SQL actions that would alter one but not the other of the tables. It would become nearly unusable from a purely procedural viewpoint.

And you should note that there is nothing wrong with having a 1/many relationship between the tables if you put a separate index constraint on the second table so that you keep it unique. If there is a specified order in creating records in the two tables, put the one that is created first as the "ONE" side of the 1/many relationship. Then the other table is the MANY side. You do this because "MANY" includes "NONE" - so you can create data for the ONE side first and the MANY side second. Then just constrain the PK on the MANY side to remain unique. Everything will still work OK

 

[jaryszek]

thank you The_Doc_Man.

I have even issues with creating one to many relationships between composite keys.
I am getting errors.

Best,
Jacek

 

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