Import DDL database structure to Access database

[CJ_London]

Question is how to relate them after creating? It is possble by SQL or only VBA?

see post #19

 

[Pat Hartman]

I did not say to make a csv. The SQL is a string, not a series of fields.

DDL is the Data Definition Language part of SQL. The other parts are the queies we use all the time to retrieve/update data and the third part of the SQL language has to do with security. So, DDL can do anything you can do using the Access GUI. It can even do some things that the GUI cannot do. So, YES, DDL can make relationships also.


You still did not tell us what you are actually trying to do. WHY is someone going to send you DDL. It is difficult for us to help you when we don't know what your problem is.

I have a product that is sold to the public. It is a custom product, not shrink-wrapped so I have some say over the environment but not much. From time to time, we produce updates to the product that require changes to the schema. So, if the client wants the update, I send him the new FE and I also send him one of two files depending on whether he is using Jet/ACE as the BE or SQL Server. The app supports both. If the BE is SQL Server, I send a script file with all the DDL statements in a single .sql file. This is a standard text file. The .sql association is known to SQL Server and so SQL Server will import the file into a query window where you can run the queries it contains or modify them. This process would always be handled by the client's DBA. It would never be handled by the users.

When the BE is Jet/ACE, I send an Access .accdb with code that checks the version to make sure the client is going from 1 to 2 or 2 to 3 or whatever the update is designed to support. Each of these update databases and .sql script files is designed to upgrade from one specific version of the BE to another specific version. The validation is careful since this process is run by the user and it would corrupt the database if run against the wrong version. The app starts by making a backup and then renaming the BE to prevent people from logging in once the process starts. The code then runs a series of DDL statements to add new tables or columns to existing tables. We never remove tables or columns using this process. Sometimes we add indexes or relationships also.

Is this what you are trying to do? It is not for the feint of heart and I don't think your technical skills are up to it.

 

[jaryszek]

Thank you Guys.

I will get Ddl schema (just structure) from my boss in whatever I want format in csv file or files and he wants to import it to access automatically draw relationships and see them on the diagram. That is all there is no hidden purpose. And I can have composite primary keys (no composite indexes which I know that I should use but for only this purpose I will use related composite primary keys. )

So I have to run series of ddls like you wrote Pat in order to delete existing tables and relationships and create new ones each time my boss will give me new data in flat files.

The statement from my link working but I have issues with post #19. It is example when table already exists. What I need is first create these tables with primary keys and after that relate them which I do not know how to do this.

What I suppose should do is:
1) create tables first
2) add primary keys indexes with alter table statement
3) relate tables with primary keys also with alter table Statement?

I do not know if I can merge somehow these steps or write 3 sepereted queries to do it. I need your help please.

I will use VBA to read csv , get data in dictionary or collection (or you know better way) write loop for each table and go with steps above.

I hope I made this more undestanable for you.

Best
Jacek

 

[Pat Hartman]

You are giving us minute details. What is the BIG picture? What you are asking for is simply not something that 99.9% of applications would ever have to do. WHAT is the purpose of building tables and relationships this way? You are clearly out of your depth with this project. I'm sure that someone will eventually code it for you but without knowing the end goal, we're never going to get it right. This is now the 24th entry in this thread. The answer to the initial question was YES, you can do that. You have given us no useful information on the kind of process you need.

 

[jaryszek]

Thank you. What information do you want from me? In my previous post I wrote the purpose - requirements which I have. purpose is to complete task. My boss wants to see tables and rels which he created in his head. That is the all sense or nonsense. I have to implement. What else do you want ? Please tell I can describe but honestly I wrote everything what I know.

I have to know way to at least to create using sql tables and relationships on the fly - it can be small example with composite key. Thats all.

Jacek

 

[Pat Hartman]

There is NO code necessary. You just save the DDL as a querydef and run it. I posted a sample of what DDL looks like. It is well documented.

The request from your boss is crazy. It would be far easier for him to create the tables using the GUI than to type out the necessary DDL. If what he really wants is to send you a file with fields in columns, that is a whole different process. You will have to link to the csv file and loop through the records to build the DDL yourself. Each row will be a field with a data type. It will need an additional column to mark the PK. The file will be the table and should include the name of the table you need to create. The file format for relationships will be different. If you need indexes, that will be a third type of input file.

Perhaps someone will build this for you but I don't have time. We don't get paid for our work here so essentially, you are asking us to do work for you for free that YOU will get paid for presenting as your own. Give the process a shot once you have a sample input. We can't even begin to help if you don't have the input files defined. Post back with specific questions. Please don't ask us to just build applications that you are getting paid to build and not even give us an actual spec with inputs defined.

 

[Pat Hartman]

Here is a more complex sample of DDL

Use [DrawingLog]
GO
CREATE TABLE dbo.[tblTransmittal] ([TransmittalID] INT IDENTITY, 
[JobID] INT , 
[StageID] INT , 
[TransmittalSeq] INT , 
[TimberlineTransmittalSeq] INT , 
[ReviewerID] INT , 
[SentDT] DATETIME , 
[SentViaID] INT , 
[AllApprovedInd] SMALLINT , 
[Comments] NTEXT , 
[ContactID] INT , 
[Subject] VARCHAR(255) , 
[AdditionalText] VARCHAR(255) , 
[SendShop] SMALLINT , 
[SendCopy] SMALLINT , 
[SendPrints] SMALLINT , 
[SendChange] SMALLINT , 
[SendPlans] SMALLINT , 
[SendSpecifications] SMALLINT , 
[SendSamples] SMALLINT , 
[SendDrawing] SMALLINT , 
[SendCalculations] SMALLINT , 
[SendDocuments] SMALLINT , 
[SendSketches] SMALLINT , 
[TransmitReasonID] INT , 
[CCBox] VARCHAR(255) , 
[UpdateDT] DATETIME , 
[UpdateBy] VARCHAR(20) , 
[upsize_ts] [timestamp] Null);

CREATE  INDEX [lkpSentViatblTransmittal] On [tblTransmittal] ([SentViaID]);

CREATE  INDEX [lkpTransmitReasontblTransmittal] On [tblTransmittal] ([TransmitReasonID]);

ALTER TABLE [tblTransmittal] ADD CONSTRAINT [PK_tblTransmittal] PRIMARY KEY ([TransmittalID]);

CREATE  INDEX [tblContactstblTransmittal] On [tblTransmittal] ([ContactID]);

CREATE  INDEX [tblJobtblTransmittal] On [tblTransmittal] ([JobID]);

CREATE  INDEX [tblReviewerstblBatch] On [tblTransmittal] ([ReviewerID]);

CREATE  INDEX [tblStagestblSubmital] On [tblTransmittal] ([StageID]);

CREATE  UNIQUE  INDEX [UniqueID] On [tblTransmittal] ([JobID], [TransmittalSeq]);

 

[jaryszek]

Thank you Pat!

The request from your boss is crazy.

Totally agree

It would be far easier for him to create the tables using the GUI than to type out the necessary DDL. If what he really wants is to send you a file with fields in columns, that is a whole different process. You will have to link to the csv file and loop through the records to build the DDL yourself. Each row will be a field with a data type. It will need an additional column to mark the PK. The file will be the table and should include the name of the table you need to create. The file format for relationships will be different. If you need indexes, that will be a third type of input file.

thank you, it is nice way!

Now i see, i can read CSV, use VBA to create Query on the fly and use DDL statement within it!

What i tried is:

1) I created table tblCustomers using this statement:

CREATE TABLE tblCustomers      (CustomerID INTEGER NOT NULL,    [Last Name] TEXT(50) NOT NULL,    [First Name] TEXT(50) NOT NULL,    Phone TEXT(10),    Email TEXT(50))

2) i created tblInvoices creating adequate SQL.

3) alter index to get composite key on First Name and Last Name columns in tblCustomers
4) did the same for tblInvoices.

3) and now i want to create relationships on composite primary keys between tables and need help with SQL to do this in Access.
I tried with :

ALTER TABLE tblCustomers
ALTER COLUMN [Last Name] INTEGER REFERENCES tblInvoices ([Last Name])

but it is not working. Plus i do not know how to relare 2 fields as composite ones together...
How can i relate it properly ?
This is the last step to know for me how to build app for my boss

In attachment please find example database.

Thank you,
Have a bless day!
Jacek

 

[Pat Hartman]

See, I knew you could do it Good job.

This is the DDL to Add a relationship to a table. Both tables need to exist before running this so do the relationships last.

ALTER TABLE Sales.TempSalesReason
   ADD CONSTRAINT FK_TempSales_SalesReason FOREIGN KEY (TempID)
      REFERENCES Sales.SalesReason (SalesReasonID)
      ON DELETE CASCADE
      ON UPDATE CASCADE
;

 

[jaryszek]

thank you very much Pat.

ALTER TABLE tblInvoices
   ADD CONSTRAINT FK_LastName FOREIGN KEY ([Last Name])
      REFERENCES tblCustomers ([Last Name])

What i am doing wrongly ?

Database example in attachment

Jacek

 

[CJ_London]

The error message tells you where the problem is. tblCustomers ([Last Name]) is not unique but part of a composite PK. So you cannot create a relationship. Make lastname a unique field and it should then work - but then you can only have one customer with a last name of 'Smith'.

Better to create a unique primary key (autonumber) and link to that

 

[jaryszek]

The error message tells you where the problem is. tblCustomers ([Last Name]) is not unique but part of a composite PK. So you cannot create a relationship. Make lastname a unique field and it should then work - but then you can only have one customer with a last name of 'Smith'.

thanks i checked it using steps:
1) i created primary key manually on [Last Name] in both tables and still have:

2) Better to create a unique primary key (autonumber) and link to that - i know from other topic but i would like to learn to do this with single primary key and composite primary key - just in case becasue my model has composite keys inside

Best,
Jacek

 

[jaryszek]

Ok i found the method to relate FK to PK:

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

It create one to many relationship:

I have question:
1) How to make this 1:1 relationship from SQL level?

2)

and how to make this kind relationship using sql ?

Please help,
Jacek

 

[Minty]

That is a poor example, you should store the CustomerID in table invoices.
In fact, surely the CustomerID should be the PK in tblCustomers?

 

[jaryszek]

Thank you Minty for getting involved!

Sorry for example, it is poor but i do not need design it properly and i cant share fields name here.
Can you please help with only technical solution, i.e. providing SQL which solve the issues from #33 ?

Best Wishes,
Jacek

 

[Minty]

Field Names would not be giving away your trade secrets I'm sure unless you called them BankAcctNum3215798 SortCode 32-65-98

In reality, you would not be able to use such a join as you can never have a null on the Primary key, and during the insertion of a new record, one or other of those joins would be to a null.

You cannot enforce such a one to one join. Think about it - one table wouldn't have an entry or the other one. Impasse.

 

[CJ_London]

agree with Minty - having created your tables, have you tried entering some data to test it?

 

[jdraw]

Agree with minty and CJ. I think you should provide meaningful data when seeking help/advice even if you have to mock up an example. People here are not trying to "steal your data/secrets" - in fact they don't care about your real data. Use test data to confirm/resolve an issue/solution, then apply it to your "real database".

 

[jaryszek]

Hello,

thank you Guys.
Minty awesome explanation.
Ok so no problem with this.
But still would be nice how to create SQL 1:1 - just to know how to solve this technically.

Plus sql for composite key 1 to many relationships plus 1 to 1 composite keys, these relationships are working:

it is curious that 1:1 with only 2 primary keys is not working but for composite key is working...

Thanks for help,
Jacek

 

[Minty]

I am not sure you can create that relationship in code.
Have a read here https://docs.microsoft.com/en-us/of...relationships-between-tables-using-access-sql