Solved

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

Posted on 2016-09-12
25
42 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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 19

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
 

Author Comment

by:Eduardo Fuerte
ID: 41796341
0
 

Author Comment

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

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 19
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 19
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 19
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 19
ID: 41798191
thank you and you're welcome, Eduardo ~ happy to help
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

825 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