Import DDL database structure to Access database (1 Viewer)

jaryszek

Registered User.
Local time
Today, 03:05
Joined
Aug 25, 2016
Messages
756
You mean in code?
I could use VBA and ADO statements but i have to know what to pass :)

I tried link attached but i failed - please see post #33 :

ALTER TABLE tblInvoices
ADD CONSTRAINT FK_Test FOREIGN KEY ([PK]) REFERENCES tblCustomers ([PK])

but this is relating only 1 to many...

Please help somebody how to write this.

Jacek
 

Minty

AWF VIP
Local time
Today, 10:05
Joined
Jul 26, 2013
Messages
10,353
About halfway down that page, it describes setting up a one to one constraint on one field (Shipping table to customer table)

I have no idea if you could include two fields in it. I doubt it.
 

jaryszek

Registered User.
Local time
Today, 03:05
Joined
Aug 25, 2016
Messages
756
Thank you Minty.

i am trying with this all the time but i have only errors returned...

Best Wishes,
Jacek
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 06:05
Joined
Feb 19, 2002
Messages
42,970
The relationship in your picture is WRONG. CustomerID should be the PK of tblCustomers and CustomerID should be the FK in tblInvoices. LastName and FirstName should NOT be in tblInvoices.

Also,
-- it is poor practice to include spaces or other punctuation symbols in your object names
-- naming all your primary keys as PK just obfuscates your schema
-- If a table has an autonumber, the autonumber should be the PK. Otherwise, there is no reason for the autonumber to exist. So if you have CustomerID (an autonumber) and CustomerNum (a candidate key), CustomerID should be the PK and is what all relationships will be based on and CustomerNum (the visible customer number) is just data. It should be indexed, probably with a unique index in the customer table to prevent duplicates and to facilitate searching since searching will almost certainly be on CustomerNum.
 

jaryszek

Registered User.
Local time
Today, 03:05
Joined
Aug 25, 2016
Messages
756
Thank you Pat for good lesson for me.

I know that relationship is wrong.
I only need technical solution how to write Access SQL to create composite key relationship plus 1 to 1 relationship.
And if this is possible?

Best
Jacek
 

Minty

AWF VIP
Local time
Today, 10:05
Joined
Jul 26, 2013
Messages
10,353
Thank you Pat for good lesson for me.

I know that relationship is wrong.
I only need technical solution how to write Access SQL to create composite key relationship plus 1 to 1 relationship.
And if this is possible?

Best
Jacek
The Link I posted in post #40 has examples of both of the above, what have you tried and what is the error?
 

jaryszek

Registered User.
Local time
Today, 03:05
Joined
Aug 25, 2016
Messages
756
Hi Minty,

as i wrote before i tried:

ALTER TABLE tblInvoices
ADD CONSTRAINT FK_Test FOREIGN KEY ([PK]) REFERENCES tblCustomers ([PK]) --> worked

and tried:

ALTER TABLE tblInvoices
ADD CONSTRAINT FK_LastName FOREIGN KEY ([Last Name])
REFERENCES tblCustomers ([Last Name]) -- no unique index found

and tried:

ALTER TABLE tblInvoices
ADD CONSTRAINT FK_LastName PRIMARY KEY ([Last Name]) REFERENCES tblCustomers ([Last Name])
ADD CONSTRAINT FK_FirstName PRIMARY KEY ([First Name]) REFERENCES tblCustomers ([First Name]) -->> syntax error in SQL statement

ALTER TABLE tblCustomers
DROP CONSTRAINT PrimaryKey --> worked.

And some others but it didnt work for me...

Jacek
 

jaryszek

Registered User.
Local time
Today, 03:05
Joined
Aug 25, 2016
Messages
756
Hi,

thank you for the clue. I used VBA and DAO engine.

This worked for me but creating many to one:

Code:
 Sub CreateTableX1()

Dim dbs As Database
 
    ' Modify this line to include the path to Northwind
    ' on your computer.
    Set dbs = CurrentDb
    
    On Error Resume Next
    sqlS = "ALTER TABLE tblCustomers DROP CONSTRAINT NewOne"
    dbs.Execute sqlS
    On Error GoTo 0
    
    sqlS = "ALTER TABLE tblCustomers ADD CONSTRAINT NewOne FOREIGN KEY (LastName) REFERENCES tblInvoices (LastName) "
 
    ' Create a table with two text fields.
    dbs.Execute sqlS
 
End Sub

When want to create 1 to 1 i get error "Syntax error in ALTER statement"
This is what i tried:

Code:
 Sub CreateTableX1()

Dim dbs As Database
 
    ' Modify this line to include the path to Northwind
    ' on your computer.
    Set dbs = CurrentDb
    
    On Error Resume Next
    sqlS = "ALTER TABLE tblCustomers DROP CONSTRAINT NewOne"
    dbs.Execute sqlS
    On Error GoTo 0
    
    sqlS = "ALTER TABLE tblCustomers ADD CONSTRAINT NewOne FOREIGN KEY (LastName) REFERENCES tblInvoices (LastName) "
 
    ' Create a table with two text fields.
    dbs.Execute sqlS
 
End Sub

and for SQL to create composite relationship i am getting stil the same error:

Code:
 sqlS = "ALTER TABLE tblCustomers ADD CONSTRAINT NewOne FOREIGN KEY (FirstName,LastName) REFERENCES tblInvoices (FirstName,LastName) "

"No Unique index found for the referenced field of primary table"

Please help anybody,
Jacek
 

Attachments

  • Database3.accdb
    428 KB · Views: 102

Minty

AWF VIP
Local time
Today, 10:05
Joined
Jul 26, 2013
Messages
10,353
You would have to have created the primary constraint on tblInvoices first, on BOTH fields.
The error code is telling you the issue.

In your code, you are only creating the initial constraint on one field.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 06:05
Joined
Feb 19, 2002
Messages
42,970
Have you tried?

ALTER TABLE tblInvoices
ADD CONSTRAINT FK_LastName PRIMARY KEY ([Last Name], [First Name]) REFERENCES tblCustomers ([Last Name], [First Name])
 

jaryszek

Registered User.
Local time
Today, 03:05
Joined
Aug 25, 2016
Messages
756
Thank you so much for your help,

Still didnt work.
I used your sql Pat:

Code:
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
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 06:05
Joined
Feb 19, 2002
Messages
42,970
I wasn't saying it would work. I asked you to try it. I can't find any example of the correct syntax. I'll keep looking.
 

jaryszek

Registered User.
Local time
Today, 03:05
Joined
Aug 25, 2016
Messages
756
Thank you. I tried. Maybe microsoft didnt implement this feature?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 05:05
Joined
Feb 28, 2001
Messages
26,996
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

Registered User.
Local time
Today, 03:05
Joined
Aug 25, 2016
Messages
756
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

AWF VIP
Local time
Today, 10:05
Joined
Jul 26, 2013
Messages
10,353
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

Registered User.
Local time
Today, 03:05
Joined
Aug 25, 2016
Messages
756
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
 

jaryszek

Registered User.
Local time
Today, 03:05
Joined
Aug 25, 2016
Messages
756
Thanks:

1610106506111.png


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

Code:
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 ?

1610106670983.png


why primary keys can not be used as joined fields?

Jacek
 

Users who are viewing this thread

Top Bottom