Solved

creating Access database from MFC DAO

Posted on 1998-02-11
6
480 Views
Last Modified: 2008-02-01
I've got an Access database. I'd need to store its internal structure somehow into my application so it will be able to create the same database in run time (with same tables, columns and relations between them). I develop in MSVC++ 5.0 using MFC and DAO. I need to know how to export the database structure into my source code (or external file?) and how to create the database in run time from that exported information.
0
Comment
Question by:stehlik
  • 3
  • 2
6 Comments
 
LVL 12

Expert Comment

by:Trygve
Comment Utility
Why can't you just provide an empty database ?
0
 

Author Comment

by:stehlik
Comment Utility
Perhaps because it's too big? Anyway, I need to create it in runtime. Is that hard or what?
0
 
LVL 9

Accepted Solution

by:
cymbolic earned 50 total points
Comment Utility
I have done this in DAO in VB, if it helps you any. Basically (that's a pun, to you 'c' guys), you iterate down through your tabledefs collection to get specifics on each table, then within each table iterate down through your fields and indexes collection.  There is also a relations collection under the database object that you can use to collect necessary information on your database.  It would be nice if you could get your hands on oneof those charts that show the DAO object model, then these processes and how they relate would be made more clear than I can describe it here.  MS has these access method architectural models availabel in the MSDN subscription and you might find them online at the MS site.  I've also seen versions put out by Seagate.

Now, you can file the info temporarily in internal arrays and invoke a createdatabase method under your workspace, and similar appropriate methods for your fields and indexes within your new tabledefs collection, and for relations, and in that manner create a mirror image of your old database, sans any data.

You can also use the .execute method on a database and process SQL DDL scripts to build tables, fields, links and indexes.

I find it helpful to extract the necessary control data into an external file, so that I can ship the definition in a concise form between systems, and recreate a database at some future time in some differrent place.  I've also created database entirely from SQL DDL scripts, which become useful when you want to be able to throw away a database and recreate it at will (especially during development)
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:stehlik
Comment Utility
As for the iteration down through all the tabledefs and such - I knew this was a way, but it sounds too complicated. I'd need more info about the DDL.  Is it possible to export that DDL info from Access? I can't see how to do that in my old Access 7.0... Or how to obtain the SQL DDL scripts you talk about? I need to do exactly what you write in your last paragraph...
0
 
LVL 9

Expert Comment

by:cymbolic
Comment Utility
Well, here's a sample of an SQL script to create a table:

create table re (
      mls      int,      
      price      real not null,      
      owner      char(40) not null,      
               etc...lots more like this...
      brkemail char(50))

and here's some VB code from an MS Access VBA Module that iterates through the tables (but doesn't get everything, since in this app I didn't need all the data structure):

 Dim dbs As Database, x$, i As Integer, j As Integer, y$
 Dim tdf As TableDef, idx As Index
 Dim tbl$(), ndx$(), ndxfld$()
 ReDim tbl$(0), ndx$(0), ndxfld$(0)
 x$ = CurDir + "\"
 Set dbs = OpenDatabase(x$ + "DP2CMN.MDB")
 With dbs
  For Each tdf In .TableDefs
   If InStr(tdf.Name, "_ImportErrors") > 0 Then
    'format sql to drop table
    x$ = "Drop Table " & tdf.Name
    dbs.Execute x$
   ElseIf Left$(tdf.Name, 4) <> "MSys" And Right$(UCase$(tdf.Name), 3) <> "XRF" Then
    ReDim Preserve tbl$(UBound(tbl$) + 1)
    'save this table name for later build
    tbl$(UBound(tbl$)) = tdf.Name
    tdf.Indexes.Refresh
    ReDim Preserve ndx$(UBound(ndx$) + 1)
    If tdf.Indexes.Count > 0 Then
     'save index name
     ndx$(UBound(ndx$)) = tdf.Indexes(0).Name
    End If
    ReDim Preserve ndxfld$(UBound(ndxfld$) + 1)
    If tdf.Indexes.Count > 0 Then
     For i = 0 To tdf.Indexes(0).Fields.Count - 1
     'save index field names
     ndxfld$(UBound(ndxfld$)) = ndxfld$(UBound(ndxfld$)) + tdf.Indexes(0).Fields(i).Name + ","
     Next i
     If Len(ndxfld$(UBound(ndxfld$))) > 0 Then 'remove trailing comma
      ndxfld$(UBound(ndxfld$)) = Left$(ndxfld$(UBound(ndxfld$)), Len(ndxfld$(UBound(ndxfld$))) - 1)
     End If
    End If
   End If
  Next tdf
 End With
'and so on.  You only need to add collections/items/properties to the loop to gather all information you need to create the SQL statements to rebuild the database structure.  For instance, saving all the field names, sizes and datatypes for each table, as well as all the index names and their fields used, etc.  It's a little work, but once you do it in a general way, you can use it on any database to save and restore the structure.  

Essentially, using DAO collections, your databases are self documenting, but you have to work to gather all needed data from the object collections by getting and saveing the propeties, then using the methods available to create a database, and also formatting SQL that will build tables, indexes, and relations.  A really god book that describes all that's available is "Hitchhiker's Guide To Visual Basic and SQL Server" from Microsoft Press.  The JET engine/DO object and it's collections, properties, and methods are described in detail, with ample examples.
0
 

Author Comment

by:stehlik
Comment Utility
Thank you.
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

744 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

15 Experts available now in Live!

Get 1:1 Help Now