Solved

Can I use an existing table in Access to create code to build an identical table

Posted on 2012-03-09
9
396 Views
Last Modified: 2012-03-11
Greetings!

I have an application that uses an access database with several large tables.  I want to write a program that will dynamically build the database for new users instead of having to provide them with a copy of a blank database.

I prefer not to have to manually type up the code to create each of the tables with the correct field names, type, and sizes.  I figure that I could load the table and go through the fields collection and append each field to a build a create table command.

But is there an automated way that would do this for me from an existing table structure.
0
Comment
Question by:lthames
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 34

Expert Comment

by:Norie
ID: 37703481
Are these databases going to be used separately with each user having their own data?
0
 
LVL 58
ID: 37703516
Simple way:  take a look at the TransferDatabase method, which allows you to copy objects from one DB to another.

Keep a empty table in a "master" DB and then use that to create your new DB.

If the tables are dynamic, then either you can go the DAO route as you described (add a tabledef, fields, indexes, etc) or you can use SQL DML (Data Manipulation Language) statements such as CREATE table

Describe a bit more what it is your doing  and we'll get you started.

Jim.
0
 

Author Comment

by:lthames
ID: 37703810
imnorte:   This is to set up a new database for someone who buys the the application.  Right now we have to provide a empty database but I would really like for the install to create the database and then build the tables.  Or at least start with an empty database and have the install build the tables.

Jim,  What I actually want to do is have the file built dynamically.  I know how to create the tables from my programming language with the create command. . . . . and I know I can write a program to go through all of the table defs to get what I need to build the tables.   I was just hoping there was a shortcut that would take existing tables and actually create the "CREATE TABLE . . . . " command to use.
0
Containers & Docker to Create a Powerful Team

Containers are an incredibly powerful technology that can provide you and/or your engineering team with huge productivity gains. Using containers, you can deploy, back up, replicate, and move apps and their dependencies quickly and easily.

 
LVL 42

Expert Comment

by:dqmq
ID: 37703930
Supporting Jim....TransferDatabase is the method of choice.  

Probably, I would make an Access application to create the new db, then do a series of import/export steps. In that application, use TransferDatabase to import the tables you want.  You can use the Structure-Only option so you don't need to maintain an empty table.  The use TransferDatabase to export the table to the New database.  

You can use the same approach for forms, queries, reports and other database objects.
0
 
LVL 48

Assisted Solution

by:Dale Fye
Dale Fye earned 166 total points
ID: 37704805
I'm not sure I understand why you want to go to all this trouble, rather than just distributing a blank database?

Do you intend to just send them an mde of accde file?

If you are not going to use any sort of install program, and you are using Access 2007/2010, then you might want to consider adding a table to your application front-end and storing the blank database as an attachment in that table.

Then during the first run of the application, your code could check to see whether the database exists on the users system and if not, extract the blank database from that table.  This would save you A LOT of effort.

I don't use attachments much, but this is the way I deploy occasionally deploy images and templates for various Word or Excel files that go with my Access applications.
0
 
LVL 58

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 167 total points
ID: 37704957
<<Jim,  What I actually want to do is have the file built dynamically.  I know how to create the tables from my programming language with the create command. . . . . and I know I can write a program to go through all of the table defs to get what I need to build the tables.   I was just hoping there was a shortcut that would take existing tables and actually create the "CREATE TABLE . . . . " command to use.>>

 Not like your thinking no.    Just use TransferDatabase method then or possibly even more simply, do a file copy of a "master template" DB you provide and rename it when you copy it.  Then you have a complete DB ready for them quick and easy.

And since you'd never touch the master copy, you can always create more.

Jim.
0
 
LVL 65

Accepted Solution

by:
rockiroads earned 167 total points
ID: 37706143
fyed mentioned distributing a blank db. Personally I think that is the best approach, one with the least number of headaches, very low maintenance as you just need to update your install with the new table, not the scripts itself. The ready made db has everything you need for the setup. Any lookup tables will exist with data already defined etc (if you have such a thing). If your tables change often you can copy the db then create scripts to truncate the tables (or use what Jim said).

Other option is for you to maintain sql scripts that create tables, setup indexes etc. This you run on your installs. Maintenance is higher here though.
If you do not have them you can use a tool like dbwscript to generate your initial scripts and then maintain them or use the sample code that is here. These should be treated as something to get you started with creating the scripts, not the tool to do everything for you.
Remember once the initial scripts have been done, you have to remember to modify them as and when new tables are created or existing tables are modified.
0
 
LVL 58
ID: 37706988
@rocki,

  Really nice to see you popup...it's been too long!

  Drop me a line at jimdettman"at"earthlink.net.  Would love to hear what you think about the new site.

Jim.
0
 

Author Closing Comment

by:lthames
ID: 37707509
Although none offered the approach I was looking for, each offered insight into a better solution than I was wanting to implement anyway.

Thanks to all of you for your input!
0

Featured Post

ClickHouse in a General Analytical Workload

We have mentioned ClickHouse in some recent posts, where it showed excellent results.

In this article on Experts Exchange, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
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…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

623 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