X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Tool To Extract Table Data Definition Statements From Access Databases, Any Version    
post Sep 28 2019, 08:27 AM

UtterAccess Moderator
Posts: 12,620
Joined: 6-December 03
From: Telegraph Hill

Hi UA,

When posters are looking for help with their databases on UtterAccess they are often asked by those responding to post their table structure. Access gives no easy way to extract this info, and often newer users aren't really sure what's being asked of them anyway!

The solution is often to post their whole database, but not everyone responding is willing to download and run random db's from the internet (even from such esteemed sites such as UA!) or they don't have a new enough version to open the download.

In the world of SQLServer/MySQL/Oracle etc people are more used to sharing the details of their database via Data Defintion statements (DDL) which can be used to recreate the database tables and relationships.

Inspired by nvogel's post in this thread and other cries of frustration from MadPiet and others, I have hacked together a quick db which you can download and which you can choose and Access db, then select for which tables you want info (and optionally data), and it will output the DDL.

The DDL can then be pasted in to a post. Whomever is responding can then copy the DDL from a post and use the database to create the tables in a new or existing database on their machine.

I have only spent a few hours on the tool, so it's pretty basic at the moment. I'm sure it will barf when having to deal with dbs containing Attachment or Lookup fields!

I'm posting it here for testing and if folk find it useful, and when more features are added, I will post it to the Code Archive.

Todo list:
- Check whether a target db already contains the tables to be created - I haven't tested properly, but I'm sure an error will occur if this is the case! blush.gif
- Perhaps optionally add DROP statements to the DDL
- Add the ability to output resutlsets nicely formatted for pasting in to posts
- Prevent flashing when opening the database for extraction
- Limit the number of records extracted
- Allow creation of tables in an SQLServer or MySQL instance
- ...

Try it out and let me know what breaks/can be improved.


d thumbup.gif
Attached File(s)
Attached File  DDL_Extractor_v0.2.accdb.zip ( 137.13K )Number of downloads: 35



David Marten
Go to the top of the page
post Sep 29 2019, 10:51 PM

UA Admin
Posts: 36,975
Joined: 20-June 02
From: Newcastle, WA

I haven’t had a lot of time to try it out. However one thing I’d like to see is a built—in way to save the SQL statements. Either in a table or as a text file.

My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    2nd April 2020 - 08:19 AM