ms access - tenerlo - ¿Cómo extraer el esquema de una base de datos de Access(.mdb)?

la base de datos se creo con la version de 32 bits de microsoft access

Question

Estoy tratando de extraer el esquema de una base de datos .mdb, de modo que pueda recrear la base de datos en otro lugar.

¿Cómo puedo lograr algo como esto?

Answer 1

¡Publicación muy útil!

He revisado el script para generar el lenguaje de definición de datos para el servidor SQL. Pensé que podría ser útil para alguien, así que lo estoy compartiendo. El único problema con el que me encontré es que el script VBS extrae todos los campos en la tabla de índices. Todavía no estoy seguro de cómo resolver esto, así que extraigo solo el primer campo. Esto funcionará para la mayoría de las claves primarias. Finalmente, no todos los tipos de datos están probados, pero creo que obtuve la mayoría de ellos.

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

Answer 2

Compare''Em http://home.gci.net/~mike-noel/CompareEM-LITE/CompareEM.htm generará felizmente el código VBA necesario para recrear un MDB. O el código para crear las diferencias entre dos MDB para que pueda realizar una actualización de la versión de la MDB BE ya existente. Es un poco peculiar pero funciona. Tenga en cuenta que no es compatible con los nuevos formatos ACE (Access2007) ACCDB, etc.

Lo uso todo el tiempo.

(OneDayWhen la edición fue un tercio de la derecha y dos tercios mal).

Answer 3

El siguiente C # describe cómo obtener el esquema a partir de un archivo .mdb.

Obtenga una conexión a la base de datos:

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

Obtener el nombre de cada tabla:

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();

Obtenga los campos para cada tabla:

 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
}
}

¿De dónde vienen los 3 , 11 y 27 ? Los encontré inspeccionando DataRow.ItemArray con un depurador, ¿alguien sabe la forma "correcta"?

Answer 4

Es difícil hacer scripts / consultas DDL en Access. Se puede hacer, pero sería mejor simplemente crear una copia de la base de datos, borrando todos los datos y compactándolos. Luego use una copia de esto para recrear la base de datos en otro lugar.

Answer 5

Es posible hacer un poco con VBA. Por ejemplo, aquí comienza la creación de secuencias de comandos para una base de datos con tablas locales.

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 strCn As String
Dim fs, f
Set db = CurrentDb
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.CreateTextFile("C:/Docs/Schema.txt")
For Each tdf In db.TableDefs
If Left(tdf.Name, 4) <> "Msys" Then
strSQL = "strSQL=""CREATE TABLE [" & tdf.Name & "] ("
strFlds = ""
For Each fld In tdf.Fields
strFlds = strFlds & ",[" & fld.Name & "] "
Select Case fld.Type
Case dbText
''No look-up fields
strFlds = strFlds & "Text (" & fld.Size & ")"
Case dbLong
If (fld.Attributes And dbAutoIncrField) = 0& Then
strFlds = strFlds & "Long"
Else
strFlds = strFlds & "Counter"
End If
Case dbBoolean
strFlds = strFlds & "YesNo"
Case dbByte
strFlds = strFlds & "Byte"
Case dbInteger
strFlds = strFlds & "Integer"
Case dbCurrency
strFlds = strFlds & "Currency"
Case dbSingle
strFlds = strFlds & "Single"
Case dbDouble
strFlds = strFlds & "Double"
Case dbDate
strFlds = strFlds & "DateTime"
Case dbBinary
strFlds = strFlds & "Binary"
Case dbLongBinary
strFlds = strFlds & "OLE Object"
Case dbMemo
If (fld.Attributes And dbHyperlinkField) = 0& Then
strFlds = strFlds & "Memo"
Else
strFlds = strFlds & "Hyperlink"
End If
Case dbGUID
strFlds = strFlds & "GUID"
End Select
Next
strSQL = strSQL & Mid(strFlds, 2) & " )""" & vbCrLf & "Currentdb.Execute strSQL"
f.WriteLine vbCrLf & strSQL
''Indexes
For Each ndx In tdf.Indexes
If ndx.Unique Then
strSQL = "strSQL=""CREATE UNIQUE INDEX "
Else
strSQL = "strSQL=""CREATE INDEX "
End If
strSQL = strSQL & "[" & ndx.Name & "] ON [" & tdf.Name & "] ("
strFlds = ""
For Each fld In tdf.Fields
strFlds = ",[" & fld.Name & "]"
Next
strSQL = strSQL & Mid(strFlds, 2) & ") "
strCn = ""
If ndx.Primary Then
strCn = " PRIMARY"
End If
If ndx.Required Then
strCn = strCn & " DISALLOW NULL"
End If
If ndx.IgnoreNulls Then
strCn = strCn & " IGNORE NULL"
End If
If Trim(strCn) <> vbNullString Then
strSQL = strSQL & " WITH" & strCn & " "
End If
f.WriteLine vbCrLf & strSQL & """" & vbCrLf & "Currentdb.Execute strSQL"
Next
End If
Next
f.Close

Answer 6

Es una pregunta antigua ahora, pero desafortunadamente perenne :(

Pensé que este código puede ser útil para otros que buscan soluciones. Está diseñado para ejecutarse desde la línea de comandos a través de cscript, por lo que no es necesario importar código en su proyecto de Access. Similar a (e inspirado en) el código de Oliver en ¿Cómo se usa el control de versiones con el desarrollo de Access ?

'' 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

Si también está buscando exportar definiciones de consulta, esta pregunta debería ayudar. Es un poco diferente porque normalmente no se crean querydefs con la sintaxis DDL CREATE VIEW foo AS ... , de hecho, no estoy seguro de que se pueda (?)

Pero aquí hay un pequeño fragmento de un script que escribí para hacer una copia de seguridad de las consultas para separar archivos .sql (que es parte de un script más grande para hacer una copia de seguridad de todo el código db front-end, vea la respuesta de Oliver para .com/questions/187506/… ).

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

Answer 7

Mira el docmd. TransferDatabase . Probablemente sea su mejor apuesta para la integración de compilación que necesita replicar la estructura de datos

Answer 8

Puede usar el proveedor ACE / Jet OLE DB y el método OpenSchema de un objeto de conexión ADO para obtener información de esquema como un conjunto de registros (que es discutible mejor que una colección porque se puede filtrar, clasificar, etc.).

La metodología básica es usar adSchemaTables para obtener las tablas base (no VIEW), luego use cada TABLE_NAME para obtener adSchemaColumns para ORDINAL_POSITION,! DATA_TYPE,! IS_NULLABLE,! COLUMN_HASDEFAULT,! COLUMN_DEFAULT,! CHARACTER_MAXIMUM_LENGTH,! NUMERIC_PRECISION,! NUMERIC_SCALE.

adSchemaPrimaryKeys es sencillo. adSchemaIndexes es donde encontrará restricciones ÚNICAS, no estoy seguro si pueden distinguirse de índices únicos, también los nombres de LLAVES EXTRAÑAS para conectar al conjunto de filas adSchemaForeignKeys, p. ej. (pseudo código):

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

- ¡cuidado con el hecho de que Jet 3.51 permite un FK basado en un PK sin nombre!

Los nombres de las reglas de validación y las restricciones CHECK se pueden encontrar en el conjunto de filas adSchemaTableConstraints, usando el nombre de la tabla en la llamada OpenSchema, luego usar el nombre en la llamada al conjunto de filas adSchemaCheckConstraints, filtrar CONSTRAINT_TYPE = ''CHECK'' (un gotcha es una restricción llamada ''ValidationRule'' + Chr $ (0), así que es mejor escapar de los caracteres nulos del nombre. Recuerde que las reglas de validación de ACE / Jet pueden ser de nivel de fila o de tabla (las restricciones de CHECK son siempre a nivel de tabla), por lo que puede necesitar usar el nombre de tabla en el filtro: para adSchemaTableConstraints es []. []. ValidationRule será [] .ValidationRule en adSchemaCheckConstraints. Otro problema (error sospechado) es que el Campo tiene 255 caracteres de ancho, por lo que cualquier definición de restricción de Regla de Validación / CHEQUE de más de 255 caracteres tendrá un valor NULO.

adSchemaViews, para los objetos de Access Query basados ​​en SELECT SQL DML no paramaterizado, es sencillo; puede usar el nombre VIEW en adSchemaColumns para obtener los detalles de la columna.

Los PROCEDIMIENTOS se encuentran en adSchemaProcedures, siendo todos los demás sabores de los objetos de Access Query, incluido el parámetro parametrado DML; para este último prefiero reemplazar la sintaxis de PARAMETERS con CREATE PROCEDURE PROCEDURE_NAME en PROCEDURE_DEFINITION. No busque en los parámetros adSchemaProcedureParameters, no encontrará nada: los parámetros se pueden enumerar utilizando un objeto de Catálogo ADOX para devolver un Comando ADO, por ejemplo (pseudo código):

Set Command = Catalog.Procedures(PROCEDURE_NAME).Command

a continuación, enumere la colección Comm.Parameters para .Name, .Type para DATA_TYPE, (.Attributes And adParamNullable) para IS_NULLABLE, .Value para COLUMN_HASDEFAULT y COLUMN_DEFAULT, .Size, .Precision, .NumericScale.

Para las propiedades específicas de ACE / Jet, como la compresión Unicode, debe usar otro tipo de objeto. Por ejemplo, un Autonumérico de entero largo en Access-speak se puede encontrar utilizando un objeto de catálogo de ADO, por ejemplo (pseudo código):

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

Buena suerte :)

Answer 9

Si está contento de utilizar algo que no sea Access SQL puro, puede conservar una colección de objetos ADOX y usarlos para recrear la estructura de la tabla.

Ejemplo (en Python, actualmente no recrea relaciones e índices ya que no era necesario para el proyecto en el que estaba trabajando):

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()

Una función inversa similar reconstruye la base de datos utilizando la segunda cadena de conexión.