Import DDL database structure to Access database (1 Viewer)

jaryszek

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

I’m here to help
Staff member
Local time
Today, 15:53
Joined
Oct 29, 2018
Messages
21,358
First impression is "why not?" But, could you post an example just to be sure?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 18:53
Joined
Feb 19, 2002
Messages
42,976
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

Nifty Access Guy
Staff member
Local time
Today, 22:53
Joined
Jul 9, 2003
Messages
16,245
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

Nifty Access Guy
Staff member
Local time
Today, 22:53
Joined
Jul 9, 2003
Messages
16,245
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.

 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 22:53
Joined
Jul 9, 2003
Messages
16,245
My other recollection was that I imported everything into a memo field and this seemed to solve the problem of the 2000 character limit.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 18:53
Joined
Feb 19, 2002
Messages
42,976
SQL strings don't lend themselves to csv format since they include commas.
 

jaryszek

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

Super Moderator
Staff member
Local time
Today, 22:53
Joined
Feb 19, 2013
Messages
16,553
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

Registered User.
Local time
Today, 15:53
Joined
Aug 25, 2016
Messages
756
Hello,

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

Jacek
 

Attachments

  • Rels.zip
    7.8 KB · Views: 235

CJ_London

Super Moderator
Staff member
Local time
Today, 22:53
Joined
Feb 19, 2013
Messages
16,553
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

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

Super Moderator
Staff member
Local time
Today, 22:53
Joined
Feb 19, 2013
Messages
16,553
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

Super Moderator
Staff member
Local time
Today, 18:53
Joined
Feb 19, 2002
Messages
42,976
This is what DDL looks like:

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

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

Super Moderator
Staff member
Local time
Today, 18:53
Joined
Feb 19, 2002
Messages
42,976
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

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

Super Moderator
Staff member
Local time
Today, 22:53
Joined
Feb 19, 2013
Messages
16,553
But cna Access via SQL create relationships?
of course - see this link for an example.


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
 

Users who are viewing this thread

Top Bottom