Import DDL database structure to Access database

[jaryszek]

Hi,

i would like to ask you Guys do you have method to import DDL structure from from example flat file like CSV and import it to Access?
It has to be written in VBA? It is possible to set up relations and so on ?
Or not?

Please help,
Jacek

 

[theDBguy]

First impression is "why not?" But, could you post an example just to be sure?

 

[Uncle Gizmo]

I messed around with importing Comma separated value files awhile back. I recall I ran into a problem when the comma separated values file had over 700 Fields. Not 100% sure about this, I've just got that niggly little inkling. Just guessing, but I reckon, as you mention setting up relations, I suspect your CSV file might have too many columns.

 

[Uncle Gizmo]

I've just looked at my notes, don't know where I got the 700 from it was actually 250 where I received an error. On investigation I found this thread which pointed out somebody else had had a similar problem.

Error encountered in Access 2010 - Not reported elsewhere

I have a spread sheet I was importing. It is 8160 rows and A through FV columns. During the load, all appeared to work well, until the end. Then the following occurred: Import Spreadsheet Wizard

answers.microsoft.com

 

[Uncle Gizmo]

My other recollection was that I imported everything into a memo field and this seemed to solve the problem of the 2000 character limit.

 

[Uncle Gizmo]

2017 thread on CSV file import here:-

Joining large tables together

I have a small problem. I keep running into error >>>> Record is too large. (Error 3047)

www.access-programmers.co.uk

 

[jaryszek]

We don't know what the file format would be but you would read the flat file and assemble the sql string. Then save it as a querydef and run it. Or just run it without saving if that is what you want.

Hello,

thanks!
What do you mean?

I will have in flat file specified primary keys and table fields.
How can i create sql string from it and why?

I've just looked at my notes, don't know where I got the 700 from it was actually 250 where I received an error. On investigation I found this thread which pointed out somebody else had had a similar problem.

Thanks,
i have small tables up to 50 fields max.
It should work i hope.

Jacek

 

[CJ_London]

suggest provide an example of your file as your descriptions don't help us to help you. It may be you just need to use the transfertext function

 

[jaryszek]

Hello,

Thanks.
Example in Excel attached for tables.
Relationships in CSV attached

Jacek

 

[CJ_London]

sorry, that doesn't help - you implied a single file, but have sent 3 without data and only a couple of fields. Really not prepared to spend time guessing your requirement.

 

[jaryszek]

No problem.
i do not have format for table DDL format - i can use whatever is working for us.

You can use only csv file attached - there is table schema.

Requirement is: create tables and relationships from CSV file. Whataver format you have there.
Best,
Jacek

 

[CJ_London]

the csv file shows relationships, not a schema, it does not have a list of fields or their datatypes which you need to create tables and those used in a relationship have a name only. All with vague names which does not even indicate a datatype.

Good luck with your project, but there is not enough real information to make any meaningful suggestions

 

[jaryszek]

Thank you Pat, thank you Chris,

You mean in Access you can also write something like in SQL Server?

what do you need in this input file?
Generally i can design the flat file so for me is no matter if i will have data fields in separate table or not. or what is the format --> i can have whatever we need.
So in other words --> i can have format what i like but i do not know how can i do this (the result tables and relationships from DDL structure whatavet shape flat file has) so i do not know what format should i have.

We can add string as datatype or integer.

Best,
Jacek

 

[jaryszek]

Thank you Pat

sorry for my english sometimes it causes issue...

We have no idea what you are trying to do. Do you have a DDL file that you are trying to use to build a table or not? Please tell us your business need. You asked if you could import DDL from a file but you don't know what DDL is so let's start with basics.

Let try to explain:
1. I got question: Can Access consume DDL from flat file?
I know from youe help that yes.
Can i get example? "No - you tell me how flat file should look like".

So in my case for me no matter how DDL will look like.
I can imagine that i have 2 tables like in example and have relationships between them and from this file i have to create tables plus create relationships using VBA or whatever.
So question is: if somebody used flat files to do somethin similar and how write SQL to create tables and create relationships from flat file.

In SQL Server, you can have multiple SQL statements in a single execute. In Access, you can have only one. So, if you are going to run the DDL in Access, each input file should contain only a single DDL statement.

This is interesting. You are saying to have only DDL statement in CSV? It would be awesome! Not to open CSV, reaf fields, read keys loop and create relationships but immidietely have SQL to import. But cna Access via SQL create relationships?

In this case i will tell my boss that i want to get DDL statement in SQL which should look like and will provide example from you (if you will provide here

Jacek

 

[CJ_London]

But cna Access via SQL create relationships?

of course - see this link for an example.

W3Schools.com

W3Schools offers free online tutorials, references and exercises in all the major languages of the web. Covering popular subjects like HTML, CSS, JavaScript, Python, SQL, Java, and many, many more.

www.w3schools.com

as Pat says, the sql will vary depending on the ultimate destination (access/sql server/mysql/etc)

you might find that website helpful for pretty much all the things you can do with sql

 

[jaryszek]

Thank you.

I tried this link:

Create and delete tables and indexes using Access SQL

Office VBA reference topic

docs.microsoft.com

And worked!
So with SQL i can create tables and set up primary keys.

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

Jacek

 

[CJ_London]

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

see post #19

 

[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

 

[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

 

[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