Exporting jet table metadata as text?
Kevin Chambers
In an attempt to commit an Access MDB to a versioning system (subversion),
I'm trying to figure out how to convert a jet table's metadata to text, a
la SaveAsText. The end goal is to be able to build an MDB completely from
the svn repository text files.
Has anybody dealt with this?
Thanks in advance,
Kevin
polite person
This is a metareply I'm afraid.
You can go some way using SQL create table statements including indexes and relationships but this
won't handle all the properties and for your purposes would ultimately be a waste of time.
I would use code which enumerates all properties in DAO (others might use ADOX if that can handle
all the properties). There are a lot of properties which in practice are set by default but it would
be simpler to store them all. Don't forget that some properties cannot be read (trap this error and
ignore them) and that to handle custom properties (which include field descriptions) you will
require the fully extended syntax ...xxx.properties("yyy"). To rebuild you would need to do things
in the right order. (ie tables before foreign keys).
Maybe someone has done this - XML would be a good output notation.
A much easier way would be to have the tables in SQL server or some other db where you can generate
them from a script!
Kevin Chambers
Thanks for the reply, pp
Yes, that's all pretty much what I had in mind. I thought about creating
DDL statements like you say, but wanted to be able to capture all the odd
little jet properties. As I write, though, I realize that there may
substantial advantage in avoiding all the Jet-specific stuff anyways.
I've already written code to synchronize schemas in DAO, so I have an idea
of what's involved on that end. I was wondering if anyone has written the
code to do the text export and then parsing of the text for import.
XML is OK, but also annoyingly verbose for my tastes. I suppose I could
mimic the format of the SaveAsText format, then I'd have something I could
reuse if I ever needed to rebuild a form/report/etc programmatically.
polite person
Export table design to text (XML) example is at
http://groups.google.co.uk/group/microsoft.public.access.gettingstarted/browse_frm/thread/b3bc010f3e2e248b/8ad5ffafa5abdd30?tvc=1&q=save+table+design+text#8ad5ffafa5abdd30
More googling (or putting in some work!) might find import, and similar code for relations
I agree the DDL route seems better but it depends on the purpose of your backup. The only really
useful things not in (IMHO) are field/table description properties as most of the others are
formatting etc including the lunatic caption property.
Some time ago I wrote something one-off to convert JET to db2 and what follows is modified from
that, without much testing I'm afraid. Include the module in your DB and call the sub
CreateTableQueries. Output DDL is written to a file TEST.TXT and can be pasted into a module in an
empty database and run to recreate the tables of the original database (design only).
One property not captured is if your autonumbers are random.
Option Compare Database
Option Explicit
Dim outfile&
Public Sub CreateTableQueries()
Dim mydb As DATABASE
Dim mytdef As TableDef
Set mydb = CurrentDb
openoutfile
For Each mytdef In mydb.TableDefs
If Left(mytdef.name, 4) <> "MSys" Then
CreateOneTable mytdef
CreateIndexes mytdef
End If
Next
DoRelations mydb
closeoutfile
End Sub
Public Sub CreateOneTable(mytdef As TableDef)
Dim myfld As Field, fld2 As Field
Dim myindex As Index
Dim mysql$, Tablename$, sepcode$, a$
Tablename = mytdef.name
mysql = "CREATE TABLE " & sanitary(Tablename) & " ("
sepcode = ""
For Each myfld In mytdef.Fields
mysql = mysql & sepcode & sanitary(myfld.name) & " "
Select Case myfld.Type
Case dbBoolean 'Boolean
a = "YESNO"
Case dbByte 'Boolean
a = "BYTE"
Case dbCurrency 'Currency
a = "CURRENCY"
Case dbDate 'Date / Time
a = "DATE"
Case dbDouble 'Double
a = "DOUBLE"
Case dbInteger 'Integer
a = "INTEGER"
Case dbLong 'Long
'test if counter, doesn't detect random property if set
If (myfld.Attributes And dbAutoIncrField) Then
a = "COUNTER"
Else
a = "LONG"
End If
Case dbMemo 'Memo
a = "MEMO"
Case dbSingle 'Single
a = "SINGLE"
Case dbText 'Text
a = "TEXT(" & myfld.Size & ")"
Case dbGUID 'Text
a = "GUID"
Case Else
MsgBox "Field " & Tablename & "." & myfld.name & " of type " & myfld.Type & " has
been ignored!!!"
End Select
mysql = mysql & a
If myfld.Required Then
mysql = mysql & " NOT NULL "
End If
sepcode = ", "
Next myfld
plant mysql & ")"
End Sub
Public Sub CreateIndexes(mytdef As TableDef)
Dim myfld As Field
Dim myindex As Index
Dim mysql$, a$, sepcode$, Tablename$
Tablename = mytdef.name
For Each myindex In mytdef.Indexes
If Left(myindex.name, 1) = "{" Then
'ignore, GUID-type indexes - bugger them
ElseIf myindex.Foreign Then
'do from relationships
Else
sepcode = ""
mysql = "CREATE "
If myindex.Unique Then
mysql = mysql & "UNIQUE "
End If
mysql = mysql & "INDEX " & myindex.name & " ON " & sanitary(mytdef.name) & "("
For Each myfld In myindex.Fields
mysql = mysql & sepcode & sanitary(myfld.name)
sepcode = ", "
Next myfld
mysql = mysql & ")"
If myindex.Primary Or myindex.IgnoreNulls Or myindex.Required Then
mysql = mysql & " WITH "
If myindex.Primary Then mysql = mysql & "PRIMARY "
If myindex.IgnoreNulls Then mysql = mysql & "IGNORE NULL "
If myindex.Required Then mysql = mysql & "DISALLOW NULL "
End If
plant mysql
End If
Next myindex
End Sub
Public Sub DoRelations(mydb As DATABASE)
Dim mytdef As TableDef
Dim myrel As Relation
Dim myfld As Field
Dim myindex As Index
Dim mysql$, a$, sepcode$, constraintname$
'ALTER TABLE table
'CONSTRAINT name
'FOREIGN KEY (ref1[, ref2 [, ...]]) REFERENCES foreigntable [(foreignfield1 [, foreignfield2 [,
...]])]
Set mydb = CurrentDb
For Each myrel In mydb.Relations
With myrel
constraintname = sanitary(.name) 'may be a guid string
mysql = "ALTER TABLE " & .ForeignTable & " ADD CONSTRAINT " & constraintname & " FOREIGN
KEY ("
sepcode = ""
For Each myfld In .Fields 'ie fields of the relation
mysql = mysql & sepcode & sanitary(myfld.ForeignName)
sepcode = ","
Next
mysql = mysql & ") REFERENCES " & .Table & "("
sepcode = ""
For Each myfld In .Fields
mysql = mysql & sepcode & sanitary(myfld.name)
sepcode = ","
Next
mysql = mysql & ")"
End With
plant mysql
Next
End Sub
Public Function sanitary(NameWithOddChars$) As String
'this is here because original prog converted to non-JET dbs
'so some character substitutions were made in names
sanitary = "[" & NameWithOddChars & "]"
End Function
Public Function str1tostr2(f$, a$, b$)
'in string F, replaces A by B wherever it occurs
'needed in Access 97 as no built-in function
Dim i&
i = InStr(f, a) - 1
If i >= 0 Then
str1tostr2 = Left(f, i) & b & str1tostr2(Right(f, Len(f) - i - Len(a)), a, b)
Else
str1tostr2 = f
End If
End Function
Public Sub plant(s$)
If Len(s) > 0 Then
Print #outfile, "CurrentDb.Execute """ & s & """"
End If
End Sub
Public Sub openoutfile()
outfile = FreeFile
Open "TEST.TXT" For Output As #outfile
Print #outfile, "Sub RunMe()"
End Sub
Public Sub closeoutfile()
Print #outfile, "End Sub"
Close #outfile
End Sub
Kevin Chambers
Hey, excellent, this looks like a great start. I'll make it work for Jet
DDL and post it when I'm done. My current thinking is that this is a good
chance to avoid Jet-specific properties, since it's my intention to make
the app as portable as possible anyways.
Thanks again!
David W. Fenton
news:du2uj...@enews3.newsguy.com:
> Hey, excellent, this looks like a great start. I'll make it work
> for Jet DDL and post it when I'm done. My current thinking is
> that this is a good chance to avoid Jet-specific properties, since
> it's my intention to make the app as portable as possible anyways.
Depending on what metadata you want, keep in mind that certain
Access table properties are going to be completely unknown to DDL,
even Jet's own DDL. So there may be table properties that you can't
get without using DAO. This only matters if your Jet tables were
created with Access and if you're using any of the Access-specific
properties.
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Kevin Chambers
> Kevin Chambers <MyIni...@MyName.DontSpamMe.UncleSam> wrote in
> news:du2uj...@enews3.newsguy.com:
>
>> Hey, excellent, this looks like a great start. I'll make it work
>> for Jet DDL and post it when I'm done. My current thinking is
>> that this is a good chance to avoid Jet-specific properties, since
>> it's my intention to make the app as portable as possible anyways.
>
> Depending on what metadata you want, keep in mind that certain
> Access table properties are going to be completely unknown to DDL,
> even Jet's own DDL. So there may be table properties that you can't
> get without using DAO. This only matters if your Jet tables were
> created with Access and if you're using any of the Access-specific
> properties.
Thanks for the reminder, David.
For anyone who wants it, here's some code based on pp's previous posting,
refactored to meet my needs.
As DF pointed out, all sorts of properties won't be included in the
generated ddl, default values and validation being the most glaring to me.
Public Function GetTableDdl(mytdef As TableDef) As String
Dim myfld As Field, sql As String
Dim Seperator As String, a As String
sql = "CREATE TABLE " & QuoteObjectName(mytdef.Name) & " ("
Seperator = vbCrLf
For Each myfld In mytdef.Fields
sql = sql & Seperator & " " & QuoteObjectName(myfld.Name) & " "
Select Case myfld.Type
Case dbBoolean 'Boolean
a = "BIT"
Case dbByte 'Byte
a = "BYTE"
Case dbCurrency 'Currency
a = "MONEY"
Case dbDate 'Date / Time
a = "DATETIME"
Case dbDouble 'Double
a = "DOUBLE"
Case dbInteger 'Integer
a = "INTEGER"
Case dbLong 'Long
'test if counter, doesn't detect random property if set
If (myfld.Attributes And dbAutoIncrField) Then
a = "COUNTER"
Else
a = "LONG"
End If
Case dbMemo 'Memo
a = "MEMO"
Case dbSingle 'Single
a = "SINGLE"
Case dbText 'Text
a = "VARCHAR(" & myfld.Size & ")"
Case dbGUID 'Text
a = "GUID"
Case Else
MsgBox "Field " & mytdef.Name & "." & myfld.Name & _
" of type " & myfld.Type & " has been ignored!!!"
End Select
sql = sql & a
If myfld.Required Then
sql = sql & " NOT NULL "
End If
Seperator = ", " & vbCrLf
Next myfld
sql = sql & vbCrLf & ");"
GetTableDdl = sql
End Function
Public Function GetIndexDdl(mytdef As TableDef, myindex As index) As String
Dim sql As String, Seperator As String, myfld As Field
If Left(myindex.Name, 1) = "{" Then
'ignore, GUID-type indexes - bugger them
ElseIf myindex.Foreign Then
'this index was created by a relation. recreating the
'relation will create this for us, so no need to do it here
Else
Seperator = vbCrLf
sql = "CREATE "
If myindex.Unique Then
sql = sql & "UNIQUE "
End If
sql = sql & "INDEX " & QuoteObjectName(myindex.Name) & " ON " & _
QuoteObjectName(mytdef.Name) & "("
For Each myfld In myindex.Fields
sql = sql & Seperator & QuoteObjectName(myfld.Name)
Seperator = ", " & vbCrLf
Next myfld
sql = sql & vbCrLf & ")"
If myindex.primary Then
sql = sql & vbCrLf & " WITH PRIMARY"
ElseIf myindex.IgnoreNulls Then
sql = sql & vbCrLf & " WITH IGNORE NULL"
ElseIf myindex.Required Then
sql = sql & vbCrLf & " WITH DISALLOW NULL"
End If
sql = sql & ";"
End If
GetIndexDdl = sql
End Function
' Returns the SQL DDL to add a relation between two tables.
' Oddly, DAO will not accept the ON DELETE or ON UPDATE
' clauses, so the resulting sql must be executed through ADO
Public Function GetRelationDdl(myrel As Relation) As String
Dim mytdef As TableDef
Dim myfld As Field
Dim sql As String, Seperator As String
With myrel
sql = "ALTER TABLE " & QuoteObjectName(.ForeignTable) & _
" ADD CONSTRAINT " & QuoteObjectName(.Name) & " FOREIGN KEY ("
Seperator = vbCrLf
For Each myfld In .Fields 'ie fields of the relation
sql = sql & Seperator & " " & QuoteObjectName(myfld.ForeignName)
Seperator = "," & vbCrLf
Next
sql = sql & ")" & vbCrLf & "REFERENCES " & _
QuoteObjectName(.Table) & "("
Seperator = vbCrLf
For Each myfld In .Fields
sql = sql & Seperator & " " & QuoteObjectName(myfld.Name)
Seperator = "," & vbCrLf
Next
sql = sql & ")"
If (myrel.Attributes And dbRelationUpdateCascade) Then _
sql = sql & vbCrLf & "ON UPDATE CASCADE"
If (myrel.Attributes And dbRelationDeleteCascade) Then _
sql = sql & vbCrLf & "ON DELETE CASCADE"
sql = sql & ";"
End With
GetRelationDdl = sql
End Function
Private Function QuoteObjectName(Str As String) As String
' Handle metadata object names with spaces, reserved words,
' or other odd stuff.
' Other flavors of sql use quotes for this
QuoteObjectName = "[" & Str & "]"
End Function