Solved

Is it possible to obtain the creation script of an Access database?

Posted on 2016-09-12
25
38 Views
Last Modified: 2016-09-14
Hi Experts

Is it possible to obtain the creation script of an Access database?

If so, how to do that?

Thanks in advance.
0
Comment
Question by:Eduardo Fuerte
  • 14
  • 6
  • 3
  • +2
25 Comments
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 41795042
What is a "creation script"?

/gustav
0
 

Author Comment

by:Eduardo Fuerte
ID: 41795099
Hi

I meant by creation script all the commands needed to create tables and relations between them PK, FK, index, constraints etc. giving an existing database.
0
 
LVL 22

Expert Comment

by:Ferruccio Accalai
ID: 41795156
There's a tool called DBWScript that does this. The full trial works for a month, then the cost is $29 http://dbweigher.com/dbwscript.php
0
 
LVL 45

Expert Comment

by:aikimark
ID: 41795837
Crystal Long (strive4peace) has this free tool:
https://analyzer.codeplex.com/

You could possibly take the output from that and construct new tables.

Alternatively, you could import the tables' structures using the import wizard.
0
 

Author Comment

by:Eduardo Fuerte
ID: 41795860
Hi and thank you for the replies

@Ferruccio
The tool you suggested generates an script that seens have  to be all rewrited to run.... I don't know it's caused by Access 2007 I'm using, directives like  COMPRESSION  and fields into brackets [ ]
aren't accepted.

CREATE TABLE [tblAdjustments] (
	[BookNumber] LONG,
	[AdjustBy] TEXT(50) WITH COMPRESSION,
	[DateAdjust] DATETIME
);

Open in new window


@aikimark
I saw the video, it seens a free and very good tool, my interest is in database itself (I'm using C#  - not access forms). It's not clear to me if it simply generates the DDL code for database recreation.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 41795889
That "with compression" keyword makes me think the utility is designed for SQL Server output.
0
 

Author Comment

by:Eduardo Fuerte
ID: 41795941
I had defined it to create as Access Script
(It doesn't runs as SQLServer script too... )
0
 

Author Comment

by:Eduardo Fuerte
ID: 41795967
@aikimark

Unfortunatelly the available Analyser isn't compatible with Access 2007 - the reports are blank pages. I couldn't find one compatible for download.

img003
0
 
LVL 45

Expert Comment

by:aikimark
ID: 41795986
I pinged the author of the utility.  Hopefully, Crystal will shed some light on the utility.
0
 

Author Comment

by:Eduardo Fuerte
ID: 41795992
Ok. Thank you by now.
0
 
LVL 18

Assisted Solution

by:crystal (strive4peace) - Microsoft MVP, Access
crystal (strive4peace) - Microsoft MVP, Access earned 500 total points
ID: 41796111
thanks for the message, Mark

Eduardo, if your database has a password, you would have to temporarily create a copy with no security to run the Analyzer on.  Does the Table Summary Report get your list of tables? Does it get any of them? It will list them until there was an error -- usually because the link to a back-end table is not right. Try running the Analyzer first on your back-end if the back-end is Access. If there are linked tables to SQL Server, again, the username and password should  be in the connection string -- this can be done temporarily, just long enough to run the Analyzer.  

An error log file should be created in the directory with the Analyzer. Do you have one? What does it say?

The 5 Basic reports should work if all you did was analyze a database with tables (Relationships report will be blank, of course, if there aren't any -- that is another common problem).  Naturally, if the database did not have forms, there was nothing to analyze so form reports would be blank.

This is the earliest version of the Analyzer that is posted on CodePlex (right after Wayne Phillips and Graham Mandeno added in vbWatchdog):
https://analyzer.codeplex.com/releases/view/126028
If this version doesn't work, please let me know and I will contact Wayne .

it should work with 2007 but it hasn't been tested on that version for awhile.

Are you running 32-bit or 64-bit Access (/Microsoft Office)?

btw, adding a feature to create scripts to make tables is something that has on my back-burner -- the Analyzer gets all the information needed and it is available for you to see
0
 

Author Comment

by:Eduardo Fuerte
ID: 41796334
Hi @crystal

No password in database.

When opening  the database the tables appeared.

The sequence of analysis is:
img004
img005
img006
The report is blank
img007
Could you possibly check?

The database is attached.
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

Author Comment

by:Eduardo Fuerte
ID: 41796341
0
 

Author Comment

by:Eduardo Fuerte
ID: 41796356
..and  no log errors appears
img008
0
 
LVL 18

Accepted Solution

by:
crystal (strive4peace) - Microsoft MVP, Access earned 500 total points
ID: 41796394
ran the Analyzer on your file -- here it is with the analysis:
http://www.msAccessGurus.com/temp/Analyzer_160913_Eduardo_v160904.zip

use the Find box at the top to pull it up and look at the reports. The information you want will be in a_Tbls and a_Flds, linked on TID. Index info is in a_Idx and a_IdxFlds. Relationship information is in a_Rels and a_RelFlds.

Once you download it, I will remove the file from the server.

I notice you are using an international version  of Access -- probably is an issue because the properties have different names.
0
 

Author Comment

by:Eduardo Fuerte
ID: 41796470
@crystal

I downloaded and ran.   The table reports are still blank.
A log error erased (attached).
ErrorLog_Analyzer.txt
0
 

Author Comment

by:Eduardo Fuerte
ID: 41796492
Backing to DBWScript/ DBWConsole

The tool 1st generated its own "metadata" by using DBWScript. Something useful only by DBWConsole. So in truth it doesn't create an Access DDL

Then is needed to use DBWConsole, create a .mdb by using this tool, open the metadata script and then run it.

The new database will be a clone of the previous database. I'm wondering in what it is advantageous to use it...making a backup copy of database could be more util.
0
 
LVL 18
ID: 41796507
can you switch the language to English? That is what the code is written in.  If not, open the tables directly -- you can also create another database and import all the tables.  That will give you the results of the analysis
0
 

Author Comment

by:Eduardo Fuerte
ID: 41796629
@crystal

Your suggestion is directly to read the tables Analyzer tables?

If so, I did that, hard to interpretate its contents.
0
 
LVL 18
ID: 41796651
Yes. There is a query called "qFields" you can look at -- but if you modify it, give it a different name.  I often open that query and put its information into Excel then delete what I don't want.  The fields are named logically (at least I think so).  Once you see what they are called and the information they have, I think you will get the logic.
0
 

Author Comment

by:Eduardo Fuerte
ID: 41796883
Ok. Good to know this resources, certainly it will be useful in the future.

Backing the original subject of the question it's amazing how difficult is to find by now a feature that could obtain a DDL script for an existing Access database. As I read you are planning to do that...
0
 
LVL 18
ID: 41796899
yes I am ... and now perhaps sooner --- desire from others drives me ;)
0
 

Author Comment

by:Eduardo Fuerte
ID: 41796911
I guess you teached the lesson in youtube also, isn't it?

Thanks for your kindness and dedication.
0
 

Author Closing Comment

by:Eduardo Fuerte
ID: 41798018
Congratulations for the product you are constantly improving.

I hope the next version could cover DDL generation for Access database,

Thanks for the guidance!
0
 
LVL 18
ID: 41798191
thank you and you're welcome, Eduardo ~ happy to help
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now