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?

 

[Pat Hartman]

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.

 

[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

 

[Pat Hartman]

SQL strings don't lend themselves to csv format since they include commas.

 

[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

 

[Pat Hartman]

This is what DDL looks like:

Use [RRSA]
GO
CREATE TABLE dbo.[tblContainers] ([ContainerName] VARCHAR(255) NOT NULL,
[DocName] VARCHAR(255) NOT NULL,
[RecordsetType] INT ,
[upsize_ts] [timestamp] Null);

ALTER TABLE [tblContainers] ADD CONSTRAINT [PK_tblContainers] PRIMARY KEY ([ContainerName], [DocName]);

Therefore, we would expect your input file to contain something that looks like this.

Also, will the DDL be Jet/ACE or SQL Server or something else? This sample is SQL Server. They are all very similar. Just minor differences.

 

[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

 

[Pat Hartman]

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.

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. It doesn't matter if the file is one long record or broken into lines like the one I showed you. The one I showed was actually created by an Access app. I did it a few years ago when Microsoft broke SSMA and had already removed the upsizing wizard from Access so there was no longer any way to get from here to there so I built my own upsizing wizard.

 

[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