Jak wyodrębnić schemat dostępu (.mdb) bazy danych?

To już starożytne pytanie, ale niestety odwieczne: (

Pomyślałem, że ten kod może być przydatny dla innych szukających rozwiązań. Został zaprojektowany do uruchamiania z wiersza poleceń za pomocą cscript, więc nie ma potrzeby importowania kodu do projektu Access. Podobnie jak (i zainspirowany) kod z Oliver w Jak używać kontroli wersji z access development .

' Usage:
'  CScript //Nologo ddl.vbs <input mdb file> > <output>
'
' Outputs DDL statements for tables, indexes, and relations from Access file 
' (.mdb, .accdb) <input file> to stdout.  
' Requires Microsoft Access.
'
' NOTE: Adapted from code from "polite person" + Kevin Chambers - see:
' http://www.mombu.com/microsoft/comp-databases-ms-access/t-exporting-jet-table-metadata-as-text-119667.html
'
Option Explicit
Dim stdout, fso
Dim strFile
Dim appAccess, db, tbl, idx, rel

Set stdout = WScript.StdOut
Set fso = CreateObject("Scripting.FileSystemObject")

' Parse args
If (WScript.Arguments.Count = 0) then
    MsgBox "Usage: cscript //Nologo ddl.vbs access-file", vbExclamation, "Error"
    Wscript.Quit()
End if
strFile = fso.GetAbsolutePathName(WScript.Arguments(0))

' Open mdb file
Set appAccess = CreateObject("Access.Application")
appAccess.OpenCurrentDatabase strFile
Set db = appAccess.DBEngine(0)(0)

' Iterate over tables
  ' create table statements
For Each tbl In db.TableDefs
  If Not isSystemTable(tbl) And Not isHiddenTable(tbl) Then
    stdout.WriteLine getTableDDL(tbl)
    stdout.WriteBlankLines(1)

    ' Iterate over indexes
      ' create index statements
    For Each idx In tbl.Indexes
      stdout.WriteLine getIndexDDL(tbl, idx)
    Next

    stdout.WriteBlankLines(2)
  End If
Next

' Iterate over relations
  ' alter table add constraint statements
For Each rel In db.Relations
  Set tbl = db.TableDefs(rel.Table)
  If Not isSystemTable(tbl) And Not isHiddenTable(tbl) Then
    stdout.WriteLine getRelationDDL(rel)
    stdout.WriteBlankLines(1)
  End If
Next

Function getTableDDL(tdef)
Const dbBoolean = 1
Const dbByte = 2
Const dbCurrency = 5
Const dbDate = 8
Const dbDouble = 7
Const dbInteger = 3
Const dbLong = 4
Const dbDecimal = 20
Const dbFloat = 17
Const dbMemo = 12
Const dbSingle = 6
Const dbText = 10
Const dbGUID = 15
Const dbAutoIncrField = 16

Dim fld
Dim sql
Dim ln, a

    sql = "CREATE TABLE " & QuoteObjectName(tdef.name) & " ("
    ln = vbCrLf

    For Each fld In tdef.fields
       sql = sql & ln & " " & QuoteObjectName(fld.name) & " "
       Select Case fld.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 (fld.Attributes And dbAutoIncrField) Then
             a = "COUNTER"
          Else
             a = "LONG"
          End If
       Case dbDecimal    'Decimal
          a = "DECIMAL"
       Case dbFloat      'Float
          a = "FLOAT"
       Case dbMemo 'Memo
          a = "MEMO"
       Case dbSingle    'Single
          a = "SINGLE"
       Case dbText 'Text
          a = "VARCHAR(" & fld.Size & ")"
       Case dbGUID 'Text
          a = "GUID"
       Case Else
          '>>> raise error
          MsgBox "Field " & tdef.name & "." & fld.name & _
                " of type " & fld.Type & " has been ignored!!!"
       End Select

       sql = sql & a

       If fld.Required Then _
          sql = sql & " NOT NULL "
       If Len(fld.DefaultValue) > 0 Then _
          sql = sql & " DEFAULT " & fld.DefaultValue

       ln = ", " & vbCrLf
    Next

    sql = sql & vbCrLf & ");"
    getTableDDL = sql

End Function

Function getIndexDDL(tdef, idx)
Dim sql, ln, myfld

    If Left(idx.name, 1) = "{" Then
       'ignore, GUID-type indexes - bugger them
    ElseIf idx.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
       ln = ""
       sql = "CREATE "
       If idx.Unique Then
           sql = sql & "UNIQUE "
       End If
       sql = sql & "INDEX " & QuoteObjectName(idx.name) & " ON " & _
             QuoteObjectName(tdef.name) & "( "
       For Each myfld In idx.fields
          sql = sql & ln & QuoteObjectName(myfld.name)
          ln = ", "
       Next
       sql = sql & " )"
       If idx.Primary Then
          sql = sql & " WITH PRIMARY"
       ElseIf idx.IgnoreNulls Then
          sql = sql & " WITH IGNORE NULL"
       ElseIf idx.Required Then
          sql = sql & " 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
Function getRelationDDL(myrel)
Const dbRelationUpdateCascade = 256
Const dbRelationDeleteCascade = 4096
Dim mytdef
Dim myfld
Dim sql, ln


    With myrel
       sql = "ALTER TABLE " & QuoteObjectName(.ForeignTable) & _
             " ADD CONSTRAINT " & QuoteObjectName(.name) & " FOREIGN KEY ( "
       ln = ""
       For Each myfld In .fields 'ie fields of the relation
          sql = sql & ln & QuoteObjectName(myfld.ForeignName)
          ln = ","
       Next
       sql = sql & " ) " & "REFERENCES " & _
             QuoteObjectName(.table) & "( "
       ln = ""
       For Each myfld In .fields
          sql = sql & ln & QuoteObjectName(myfld.name)
          ln = ","
       Next
       sql = sql & " )"
       If (myrel.Attributes And dbRelationUpdateCascade) Then _
             sql = sql & " ON UPDATE CASCADE"
       If (myrel.Attributes And dbRelationDeleteCascade) Then _
             sql = sql & " ON DELETE CASCADE"
       sql = sql & ";"
    End With
    getRelationDDL = sql
End Function


Function isSystemTable(tbl)
Dim nAttrib
Const dbSystemObject = -2147483646
    isSystemTable = False
    nAttrib = tbl.Attributes
    isSystemTable = (nAttrib <> 0 And ((nAttrib And dbSystemObject) <> 0))
End Function

Function isHiddenTable(tbl)
Dim nAttrib
Const dbHiddenObject = 1
    isHiddenTable = False
    nAttrib = tbl.Attributes
    isHiddenTable = (nAttrib <> 0 And ((nAttrib And dbHiddenObject) <> 0))
End Function

Function QuoteObjectName(str)
    QuoteObjectName = "[" & str & "]"
End Function

Jeśli chcesz również eksportować definicje zapytań, to pytanie powinno pomóc. Jest trochę inaczej, ponieważ zazwyczaj nie tworzysz querydefs z prostą składnią DDL CREATE VIEW foo AS ..., w rzeczywistości nie jestem pewien, czy możesz (?)

Ale oto mały fragment skryptu, który napisałem do tworzenia kopii zapasowych zapytań do oddzielenia .pliki sql (które są częścią większego skryptu do tworzenia kopii zapasowych całego kodu dB, zobacz odpowiedź Olivera na to pytanie ).

Dim oApplication
Set oApplication = CreateObject("Access.Application")
oApplication.OpenCurrentDatabase sMyAccessFilePath
oApplication.Visible = False

For Each myObj In oApplication.DBEngine(0)(0).QueryDefs
    writeToFile sExportpath & "\queries\" & myObj.Name & ".sql", myObj.SQL 
Next

Function writeToFile(path, text)
Dim fso, st
  Set fso = CreateObject("Scripting.FileSystemObject")
  Set st = fso.CreateTextFile(path, True)
  st.Write text
  st.Close
End Function

Poniższy C# przedstawia jak uzyskać schemat zplik mdb.

Uzyskanie połączenia z bazą danych:

String f = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + "database.mdb";
OleDbConnection databaseConnection = new OleDbConnection(f);
databaseConnection.Open();

Pobierz nazwę każdej tabeli:

DataTable dataTable = databaseConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
int numTables = dataTable.Rows.Count;
for (int tableIndex = 0; tableIndex < numTables; ++tableIndex)
{
    String tableName = dataTable.Rows[tableIndex]["TABLE_NAME"].ToString();

Pobierz pola dla każdej tabeli:

    DataTable schemaTable = databaseConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, null, tableName, null });
    foreach (DataRow row in schemaTable.Rows)
    {
        String fieldName = row["COLUMN_NAME"].ToString(); //3
        String fieldType = row["DATA_TYPE"].ToString(); // 11
        String fieldDescription = row["DESCRIPTION"].ToString(); //27
    }
}

Gdzie 3, 11 i 27 pochodzą z? Znalazłem je sprawdzając DataRow.ItemArray za pomocą debuggera, czy ktoś zna "poprawny"sposób?

Jeśli chcesz użyć czegoś innego niż pure Access SQL, możesz zachować kolekcję obiektów ADOX i użyć ich do odtworzenia struktury tabeli.

Przykład (w Pythonie nie odtwarza obecnie relacji i indeksów, ponieważ nie był potrzebny do projektu, nad którym pracowałem):

import os
import sys
import datetime
import comtypes.client as client

class Db:
    def __init__(self, original_con_string = None, file_path = None,
                 new_con_string = None, localise_links = False):
        self.original_con_string = original_con_string
        self.file_path = file_path
        self.new_con_string = new_con_string
        self.localise_links = localise_links

    def output_table_structures(self, verbosity = 0):
        if os.path.exists(self.file_path):
            if not os.path.isdir(self.file_path):
                raise Exception("file_path must be a directory!")
        else:
            os.mkdir(self.file_path)
        cat = client.CreateObject("ADOX.Catalog")
        cat.ActiveConnection = self.original_con_string
        linked_tables = ()
        for table in cat.Tables:
            if table.Type == u"TABLE":
                f = open(self.file_path + os.path.sep +
                         "Tablestruct_" + table.Name + ".txt", "w")
                conn = client.CreateObject("ADODB.Connection")
                conn.ConnectionString = self.original_con_string
                rs = client.CreateObject("ADODB.Recordset")
                conn.Open()
                rs.Open("SELECT TOP 1 * FROM [%s];" % table.Name, conn)
                for field in rs.Fields:
                    col = table.Columns[field.Name]
                    col_details = (col.Name, col.Type, col.DefinedSize,
                                   col.Attributes)
                    property_dict = {}
                    property_dict["Autoincrement"] = (
                        col.Properties["Autoincrement"].Value)
                    col_details += property_dict,
                    f.write(repr(col_details) + "\n")
                rs.Close()
                conn.Close()
                f.close()
            if table.Type == u"LINK":
                table_details = table.Name,
                table_details += table.Properties(
                    "Jet OLEDB:Link DataSource").Value,
                table_details += table.Properties(
                    "Jet OLEDB:Link Provider String").Value,
                table_details += table.Properties(
                    "Jet OLEDB:Remote Table Name").Value,
                linked_tables += table_details,
        if linked_tables != ():
            f = open(self.file_path + os.path.sep +
                     "linked_list.txt", "w")
            for t in linked_tables:
                f.write(repr(t) + "\n")
        cat.ActiveConnection.Close()

Podobna funkcja odwrotna rekonstruuje bazę danych za pomocą łańcucha drugiego połączenia.

Możesz użyć Ace/Jet OLE DB Provider i metody OPENSCHEMA obiektu ADO Connection, aby uzyskać informacje o schemacie jako zestaw rekordów (co jest lepsze niż zbiór, ponieważ może być filtrowane, sortowane itp.).

Podstawową metodologią jest użycie adSchemaTables do uzyskania tabel bazowych (nie widoków), a następnie użycie każdej TABLE_NAME do pobrania adSchemaColumns dla ORDINAL_POSITION,!DATA_TYPE, !IS_NULLABLE, !COLUMN_HASDEFAULT, !COLUMN_DEFAULT, !CHARACTER_MAXIMUM_LENGTH, !NUMERIC_PRECISION,!NUMERIC_SCALE.

AdSchemaPrimaryKeys jest prosty. adSchemaIndexes to miejsce, gdzie znajdziesz unikalne ograniczenia, Nie wiem, czy można je odróżnić od unikalnych indeksów, a także nazwy kluczy obcych do podłączenia do wiersza adschemaforeignkeys np. (pseudo code):

rsFK.Filter = "FK_NAME = '" & !INDEX_NAME & "'") 

-- Uważaj na to, że Jet 3.51 pozwala na FK oparty na bezimiennym PK (!!)

Nazwy zasad walidacji i ograniczeń sprawdzania można znaleźć w adschematableconstraints rowset, używając nazwy tabeli w wywołaniu OpenSchema, następnie użyj nazwy w wywołaniu do adschemacheckconstraint rowset, filter for CONSTRAINT_TYPE = 'CHECK' (a gotcha to constraint o nazwie 'ValidationRule' + Chr$(0), więc najlepiej unikać znaków null z nazwy). Pamiętaj, że reguły walidacji ACE / Jet mogą być na poziomie wiersza lub tabeli( ograniczenia sprawdzania są zawsze na poziomie tabeli), więc może być konieczne użycie nazwy tabeli w filtrze: for adschematableconstraints to [].[].ValidationRule będzie [].ValidationRule in adSchemaCheckConstraints. Innym (podejrzewanym błędem) jest to, że pole ma szerokość 255 znaków, więc każda definicja reguły walidacji/ograniczenia sprawdzania więcej niż 255 znaków będzie miała wartość NULL.

AdSchemaViews, dla obiektów zapytań dostępu opartych na nieparamaterializowanym SELECT SQL DML, jest prosty; możesz użyć nazwy widoku w adSchemaColumns, aby uzyskać szczegóły kolumny.

Procedury są w adSchemaProcedures, czyli wszystkie inne smaki access query objects w tym sparametryzowane SELECT DML; dla tego ostatniego preferuję zastąpienie składni parametrów składnią CREATE PROCEDURE NAZWA_PROCEDURY w PROCEDURE_DEFINITION. Nie szukaj w adSchemaProcedureParameters, nic nie znajdziesz: parametry można wyliczyć używając obiektu Adox Catalog do zwrócenia polecenia ADO np. (pseudo code):

Set Command = Catalog.Procedures(PROCEDURE_NAME).Command

Następnie wylicz Komunikator.Zbiór parametrów dla .Name, .Type for DATA_TYPE, (.Atrybuty i adParamNullable) dla IS_NULLABLE, .Wartość dla COLUMN_HASDEFAULT i COLUMN_DEFAULT, .Rozmiar,Precyzja,NumericScale.

Dla właściwości specyficznych dla ACE/Jet, takich jak kompresja Unicode, musisz użyć innego rodzaju obiektu. Na przykład, Long Integer Autonumber w Access-speak można znaleźć za pomocą obiektu ADO Catalog, np. (pseudo code):

bIsAutoincrement = Catalog.Tables(TABLE_NAME).Columns(COLUMN_NAME).Properties("Autoincrement").Value

Powodzenia:)

Porównaj http://home.gci.net / ~mike-noel/CompareEM-LITE/CompareEM.htm będzie szczęśliwie wygenerować kod VBA potrzebne do odtworzenia MDB. Lub kod do tworzenia różnic między dwoma MDB, dzięki czemu można zrobić aktualizację wersji już istniejącego MDB BE. To trochę dziwne, ale działa. Uwaga nie obsługuje nowych formatów Ace (Access2007) ACCDB itp.

Używam go cały czas.

(jedna trzecia edycji była dobra, a dwie trzecie zła.)

Trudno zrobić Skrypty / zapytania DDL w Access. Można to zrobić, ale lepiej będzie po prostu utworzyć kopię bazy danych-usunąć wszystkie dane i je zagęścić. Następnie użyj tej kopii do odtworzenia bazy danych w innym miejscu.

Zobacz docmd.Polecenie TransferDatabase . Jest to prawdopodobnie najlepsza opcja dla integracji build, która musi replikować strukturę danych

Bardzo pomocny post!

Poprawiłem skrypt, aby wygenerować język definicji danych dla SQL server. Pomyślałam, że może się komuś przydać, więc się tym dzielę. Jedynym problemem, na który wpadłem, jest to, że skrypt VBS wyodrębnia wszystkie pola w tabeli dla indeksów. Nie jestem jeszcze pewien, jak to rozwiązać, więc wyodrębniam tylko pierwsze pole. Będzie to działać dla większości kluczy podstawowych. Wreszcie, nie wszystkie typy danych są sprawdzone, ale myślę, że mam większość z nich.

Option Compare Database


Function exportTableDefs()

Dim db As Database
Dim tdf As TableDef
Dim fld As DAO.Field
Dim ndx As DAO.Index
Dim strSQL As String
Dim strFlds As String

Dim fs, f

    Set db = CurrentDb

    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.CreateTextFile("C:\temp\Schema.txt")

    For Each tdf In db.TableDefs
        If Left(tdf.Name, 4) <> "Msys" And Left(tdf.Name, 1) <> "~" Then
            strSQL = "CREATE TABLE [" & tdf.Name & "] (" & vbCrLf

            strFlds = ""

            For Each fld In tdf.Fields

                strFlds = strFlds & ",[" & fld.Name & "] "

                Select Case fld.Type

                    Case dbText
                        'No look-up fields
                        strFlds = strFlds & "varchar (" & fld.SIZE & ")"

                    Case dbLong
                        If (fld.Attributes And dbAutoIncrField) = 0& Then
                            strFlds = strFlds & "bigint"
                        Else
                            strFlds = strFlds & "int IDENTITY(1,1)"
                        End If

                    Case dbBoolean
                        strFlds = strFlds & "bit"

                    Case dbByte
                        strFlds = strFlds & "tinyint"

                    Case dbInteger
                        strFlds = strFlds & "int"

                    Case dbCurrency
                        strFlds = strFlds & "decimal(10,2)"

                    Case dbSingle
                        strFlds = strFlds & "decimal(10,2)"

                    Case dbDouble
                        strFlds = strFlds & "Float"

                    Case dbDate
                        strFlds = strFlds & "DateTime"

                    Case dbBinary
                        strFlds = strFlds & "binary"

                    Case dbLongBinary
                        strFlds = strFlds & "varbinary(max)"

                    Case dbMemo
                        If (fld.Attributes And dbHyperlinkField) = 0& Then
                            strFlds = strFlds & "varbinary(max)"
                        Else
                            strFlds = strFlds & "?"
                        End If

                    Case dbGUID
                        strFlds = strFlds & "?"
                    Case Else
                        strFlds = strFlds & "?"

                End Select
                strFlds = strFlds & vbCrLf

            Next

            ''  get rid of the first comma
            strSQL = strSQL & Mid(strFlds, 2) & " )" & vbCrLf

            f.WriteLine strSQL

            strSQL = ""

            'Indexes
            For Each ndx In tdf.Indexes

                If Left(ndx.Name, 1) <> "~" Then
                    If ndx.Primary Then
                        strSQL = "ALTER TABLE " & tdf.Name & " ADD  CONSTRAINT " & tdf.Name & "_primary" & " PRIMARY KEY CLUSTERED ( " & vbCrLf
                    Else
                        If ndx.Unique Then
                            strSQL = "CREATE UNIQUE NONCLUSTERED INDEX "
                        Else
                            strSQL = "CREATE NONCLUSTERED INDEX "
                        End If
                        strSQL = strSQL & "[" & tdf.Name & "_" & ndx.Name & "] ON [" & tdf.Name & "] ("
                    End If

                    strFlds = ""

                    '''  Assume that the index is only for the first field.  This will work for most primary keys
                    '''  Not sure how to get just the fields in the index
                    For Each fld In tdf.Fields
                        strFlds = strFlds & ",[" & fld.Name & "] ASC "
                        Exit For
                    Next

                    strSQL = strSQL & Mid(strFlds, 2) & ") "
                End If
            Next
           f.WriteLine strSQL & vbCrLf
        End If
    Next

    f.Close

End Function