stehlik
asked on
creating Access database from MFC DAO
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.
Why can't you just provide an empty database ?
ASKER
Perhaps because it's too big? Anyway, I need to create it in runtime. Is that hard or what?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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...
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.Coun t - 1
'save index field names
ndxfld$(UBound(ndxfld$)) = ndxfld$(UBound(ndxfld$)) + tdf.Indexes(0).Fields(i).N ame + ","
Next i
If Len(ndxfld$(UBound(ndxfld$ ))) > 0 Then 'remove trailing comma
ndxfld$(UBound(ndxfld$)) = Left$(ndxfld$(UBound(ndxfl d$)), 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/properti es 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.
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.Coun
'save index field names
ndxfld$(UBound(ndxfld$)) = ndxfld$(UBound(ndxfld$)) + tdf.Indexes(0).Fields(i).N
Next i
If Len(ndxfld$(UBound(ndxfld$
ndxfld$(UBound(ndxfld$)) = Left$(ndxfld$(UBound(ndxfl
End If
End If
End If
Next tdf
End With
'and so on. You only need to add collections/items/properti
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.
ASKER
Thank you.