On Mon, 27 Feb 2006 13:38:25 -0800, Kevin Chambers <My********@MyName.DontSpamMe.UncleSam> wrote:
polite person wrote: On Mon, 27 Feb 2006 01:47:16 -0800, Kevin Chambers <My********@MyName.DontSpamMe.UncleSam> wrote:
Hi all--
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
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!
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.
Export table design to text (XML) example is at
http://groups.google.co.uk/group/mic...d5ffafa5abdd30
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
