ms-access - sirve - que se puede hacer por medio de microsoft access
¿Cómo se usa el control de versiones con el desarrollo de Access? (21)
Estoy involucrado en la actualización de una solución de acceso. Tiene una buena cantidad de VBA, una serie de consultas, una pequeña cantidad de tablas y algunas formas de ingreso de datos y generación de informes. Es un candidato ideal para Access.
Quiero hacer cambios en el diseño de la tabla, el VBA, las consultas y los formularios. ¿Cómo puedo rastrear mis cambios con el control de la versión? (usamos Subversion, pero esto vale para cualquier sabor) Puedo poner todo el mdb en subversión, pero eso será almacenar un archivo binario, y no podré decir que acabo de cambiar una línea de código VBA.
Pensé en copiar el código de VBA para separar los archivos y guardarlos, pero podía verlos rápidamente sin sincronizar con lo que hay en la base de datos.
Solución de archivo de texto único (consultas, tablas y relaciones incluidas)
He alterado el par de scripts de Oliver para que exporten / importen relaciones, tablas y consultas además de módulos, clases, formularios y macros. Todo se guarda en archivos de texto plano, por lo que no se crea ningún archivo de base de datos para almacenar con los archivos de texto en el control de versiones.
Exportar a archivos de texto (decompose.vbs)
'' Usage:
'' cscript decompose.vbs <input file> <path>
'' Converts all modules, classes, forms and macros from an Access Project file (.adp) <input file> to
'' text and saves the results in separate files to <path>. Requires Microsoft Access.
Option Explicit
Const acForm = 2
Const acModule = 5
Const acMacro = 4
Const acReport = 3
Const acQuery = 1
Const acExportTable = 0
'' BEGIN CODE
Dim fso, relDoc, ACCDBFilename, sExportpath
Set fso = CreateObject("Scripting.FileSystemObject")
Set relDoc = CreateObject("Microsoft.XMLDOM")
If (Wscript.Arguments.Count = 0) Then
MsgBox "Please provide the .accdb database file", vbExclamation, "Error"
Wscript.Quit()
End If
ACCDBFilename = fso.GetAbsolutePathName(Wscript.Arguments(0))
If (Wscript.Arguments.Count = 1) Then
sExportpath = ""
Else
sExportpath = Wscript.Arguments(1)
End If
exportModulesTxt ACCDBFilename, sExportpath
If (Err <> 0) And (Err.Description <> Null) Then
MsgBox Err.Description, vbExclamation, "Error"
Err.Clear
End If
Function exportModulesTxt(ACCDBFilename, sExportpath)
Dim myComponent, sModuleType, sTempname, sOutstring
Dim myType, myName, myPath, hasRelations
myType = fso.GetExtensionName(ACCDBFilename)
myName = fso.GetBaseName(ACCDBFilename)
myPath = fso.GetParentFolderName(ACCDBFilename)
''if no path was given as argument, use a relative directory
If (sExportpath = "") Then
sExportpath = myPath & "/Source"
End If
''On Error Resume Next
fso.DeleteFolder (sExportpath)
fso.CreateFolder (sExportpath)
On Error GoTo 0
Wscript.Echo "starting Access..."
Dim oApplication
Set oApplication = CreateObject("Access.Application")
Wscript.Echo "Opening " & ACCDBFilename & " ..."
If (Right(ACCDBFilename, 4) = ".adp") Then
oApplication.OpenAccessProject ACCDBFilename
Else
oApplication.OpenCurrentDatabase ACCDBFilename
End If
oApplication.Visible = False
Wscript.Echo "exporting..."
Dim myObj
For Each myObj In oApplication.CurrentProject.AllForms
Wscript.Echo "Exporting FORM " & myObj.FullName
oApplication.SaveAsText acForm, myObj.FullName, sExportpath & "/" & myObj.FullName & ".form.txt"
oApplication.DoCmd.Close acForm, myObj.FullName
Next
For Each myObj In oApplication.CurrentProject.AllModules
Wscript.Echo "Exporting MODULE " & myObj.FullName
oApplication.SaveAsText acModule, myObj.FullName, sExportpath & "/" & myObj.FullName & ".module.txt"
Next
For Each myObj In oApplication.CurrentProject.AllMacros
Wscript.Echo "Exporting MACRO " & myObj.FullName
oApplication.SaveAsText acMacro, myObj.FullName, sExportpath & "/" & myObj.FullName & ".macro.txt"
Next
For Each myObj In oApplication.CurrentProject.AllReports
Wscript.Echo "Exporting REPORT " & myObj.FullName
oApplication.SaveAsText acReport, myObj.FullName, sExportpath & "/" & myObj.FullName & ".report.txt"
Next
For Each myObj In oApplication.CurrentDb.QueryDefs
Wscript.Echo "Exporting QUERY " & myObj.Name
oApplication.SaveAsText acQuery, myObj.Name, sExportpath & "/" & myObj.Name & ".query.txt"
Next
For Each myObj In oApplication.CurrentDb.TableDefs
If Not Left(myObj.Name, 4) = "MSys" Then
Wscript.Echo "Exporting TABLE " & myObj.Name
oApplication.ExportXml acExportTable, myObj.Name, , sExportpath & "/" & myObj.Name & ".table.txt"
''put the file path as a second parameter if you want to export the table data as well, instead of ommiting it and passing it into a third parameter for structure only
End If
Next
hasRelations = False
relDoc.appendChild relDoc.createElement("Relations")
For Each myObj In oApplication.CurrentDb.Relations ''loop though all the relations
If Not Left(myObj.Name, 4) = "MSys" Then
Dim relName, relAttrib, relTable, relFoTable, fld
hasRelations = True
relDoc.ChildNodes(0).appendChild relDoc.createElement("Relation")
Set relName = relDoc.createElement("Name")
relName.Text = myObj.Name
relDoc.ChildNodes(0).LastChild.appendChild relName
Set relAttrib = relDoc.createElement("Attributes")
relAttrib.Text = myObj.Attributes
relDoc.ChildNodes(0).LastChild.appendChild relAttrib
Set relTable = relDoc.createElement("Table")
relTable.Text = myObj.Table
relDoc.ChildNodes(0).LastChild.appendChild relTable
Set relFoTable = relDoc.createElement("ForeignTable")
relFoTable.Text = myObj.ForeignTable
relDoc.ChildNodes(0).LastChild.appendChild relFoTable
Wscript.Echo "Exporting relation " & myObj.Name & " between tables " & myObj.Table & " -> " & myObj.ForeignTable
For Each fld In myObj.Fields ''in case the relationship works with more fields
Dim lf, ff
relDoc.ChildNodes(0).LastChild.appendChild relDoc.createElement("Field")
Set lf = relDoc.createElement("Name")
lf.Text = fld.Name
relDoc.ChildNodes(0).LastChild.LastChild.appendChild lf
Set ff = relDoc.createElement("ForeignName")
ff.Text = fld.ForeignName
relDoc.ChildNodes(0).LastChild.LastChild.appendChild ff
Wscript.Echo " Involving fields " & fld.Name & " -> " & fld.ForeignName
Next
End If
Next
If hasRelations Then
relDoc.InsertBefore relDoc.createProcessingInstruction("xml", "version=''1.0''"), relDoc.ChildNodes(0)
relDoc.Save sExportpath & "/relations.rel.txt"
Wscript.Echo "Relations successfuly saved in file relations.rel.txt"
End If
oApplication.CloseCurrentDatabase
oApplication.Quit
End Function
Puede ejecutar este script llamando a cscript decompose.vbs <path to file to decompose> <folder to store text files> . En caso de que omita el segundo parámetro, creará la carpeta ''Fuente'' donde se encuentra la base de datos. Tenga en cuenta que la carpeta de destino se borrará si ya existe.
Incluir datos en las tablas exportadas
Reemplace la línea 93: oApplication.ExportXML acExportTable, myObj.Name, , sExportpath & "/" & myObj.Name & ".table.txt"
con la línea oApplication.ExportXML acExportTable, myObj.Name, sExportpath & "/" & myObj.Name & ".table.txt"
Importar en Crear archivo de base de datos (compose.vbs)
'' Usage:
'' cscript compose.vbs <file> <path>
'' Reads all modules, classes, forms, macros, queries, tables and their relationships in a directory created by "decompose.vbs"
'' and composes then into an Access Database file (.accdb).
'' Requires Microsoft Access.
Option Explicit
Const acForm = 2
Const acModule = 5
Const acMacro = 4
Const acReport = 3
Const acQuery = 1
Const acStructureOnly = 0 ''change 0 to 1 if you want import StructureAndData instead of StructureOnly
Const acCmdCompileAndSaveAllModules = &H7E
Dim fso, relDoc, ACCDBFilename, sPath
Set fso = CreateObject("Scripting.FileSystemObject")
Set relDoc = CreateObject("Microsoft.XMLDOM")
If (Wscript.Arguments.Count = 0) Then
MsgBox "Please provide the .accdb database file", vbExclamation, "Error"
Wscript.Quit()
End If
ACCDBFilename = fso.GetAbsolutePathName(Wscript.Arguments(0))
If (Wscript.Arguments.Count = 1) Then
sPath = ""
Else
sPath = Wscript.Arguments(1)
End If
importModulesTxt ACCDBFilename, sPath
If (Err <> 0) And (Err.Description <> Null) Then
MsgBox Err.Description, vbExclamation, "Error"
Err.Clear
End If
Function importModulesTxt(ACCDBFilename, sImportpath)
Dim myComponent, sModuleType, sTempname, sOutstring
'' Build file and pathnames
Dim myType, myName, myPath
myType = fso.GetExtensionName(ACCDBFilename)
myName = fso.GetBaseName(ACCDBFilename)
myPath = fso.GetParentFolderName(ACCDBFilename)
'' if no path was given as argument, use a relative directory
If (sImportpath = "") Then
sImportpath = myPath & "/Source/"
End If
'' check for existing file and ask to overwrite with the stub
If fso.FileExists(ACCDBFilename) Then
Wscript.StdOut.Write ACCDBFilename & " already exists. Overwrite? (y/n) "
Dim sInput
sInput = Wscript.StdIn.Read(1)
If (sInput <> "y") Then
Wscript.Quit
Else
If fso.FileExists(ACCDBFilename & ".bak") Then
fso.DeleteFile (ACCDBFilename & ".bak")
End If
fso.MoveFile ACCDBFilename, ACCDBFilename & ".bak"
End If
End If
Wscript.Echo "starting Access..."
Dim oApplication
Set oApplication = CreateObject("Access.Application")
Wscript.Echo "Opening " & ACCDBFilename
If (Right(ACCDBFilename, 4) = ".adp") Then
oApplication.CreateAccessProject ACCDBFilename
Else
oApplication.NewCurrentDatabase ACCDBFilename
End If
oApplication.Visible = False
Dim folder
Set folder = fso.GetFolder(sImportpath)
''load each file from the import path into the stub
Dim myFile, objectname, objecttype
For Each myFile In folder.Files
objectname = fso.GetBaseName(myFile.Name) ''get rid of .txt extension
objecttype = fso.GetExtensionName(objectname)
objectname = fso.GetBaseName(objectname)
Select Case objecttype
Case "form"
Wscript.Echo "Importing FORM from file " & myFile.Name
oApplication.LoadFromText acForm, objectname, myFile.Path
Case "module"
Wscript.Echo "Importing MODULE from file " & myFile.Name
oApplication.LoadFromText acModule, objectname, myFile.Path
Case "macro"
Wscript.Echo "Importing MACRO from file " & myFile.Name
oApplication.LoadFromText acMacro, objectname, myFile.Path
Case "report"
Wscript.Echo "Importing REPORT from file " & myFile.Name
oApplication.LoadFromText acReport, objectname, myFile.Path
Case "query"
Wscript.Echo "Importing QUERY from file " & myFile.Name
oApplication.LoadFromText acQuery, objectname, myFile.Path
Case "table"
Wscript.Echo "Importing TABLE from file " & myFile.Name
oApplication.ImportXml myFile.Path, acStructureOnly
Case "rel"
Wscript.Echo "Found RELATIONSHIPS file " & myFile.Name & " ... opening, it will be processed after everything else has been imported"
relDoc.Load (myFile.Path)
End Select
Next
If relDoc.readyState Then
Wscript.Echo "Preparing to build table dependencies..."
Dim xmlRel, xmlField, accessRel, relTable, relName, relFTable, relAttr, i
For Each xmlRel In relDoc.SelectNodes("/Relations/Relation") ''loop through every Relation node inside .xml file
relName = xmlRel.SelectSingleNode("Name").Text
relTable = xmlRel.SelectSingleNode("Table").Text
relFTable = xmlRel.SelectSingleNode("ForeignTable").Text
relAttr = xmlRel.SelectSingleNode("Attributes").Text
''remove any possible conflicting relations or indexes
On Error Resume Next
oApplication.CurrentDb.Relations.Delete (relName)
oApplication.CurrentDb.TableDefs(relTable).Indexes.Delete (relName)
oApplication.CurrentDb.TableDefs(relFTable).Indexes.Delete (relName)
On Error GoTo 0
Wscript.Echo "Creating relation " & relName & " between tables " & relTable & " -> " & relFTable
Set accessRel = oApplication.CurrentDb.CreateRelation(relName, relTable, relFTable, relAttr) ''create the relationship object
For Each xmlField In xmlRel.SelectNodes("Field") ''in case the relationship works with more fields
accessRel.Fields.Append accessRel.CreateField(xmlField.SelectSingleNode("Name").Text)
accessRel.Fields(xmlField.SelectSingleNode("Name").Text).ForeignName = xmlField.SelectSingleNode("ForeignName").Text
Wscript.Echo " Involving fields " & xmlField.SelectSingleNode("Name").Text & " -> " & xmlField.SelectSingleNode("ForeignName").Text
Next
oApplication.CurrentDb.Relations.Append accessRel ''append the newly created relationship to the database
Wscript.Echo " Relationship added"
Next
End If
oApplication.RunCommand acCmdCompileAndSaveAllModules
oApplication.Quit
End Function
You can execute this script by calling cscript compose.vbs <path to file which should be created> <folder with text files> . In case you omit the second parameter, it will look into ''Source'' folder where the database should be created.
Import data from text file
Replace line 14: const acStructureOnly = 0 with const acStructureOnly = 1 . This will work only if you have included the data in exported table.
Things that are not covered
- I have tested this only with .accdb files, so with anything else there might be some bugs.
- Setting are not exported, I would recommend creating the Macro that will apply the setting at start of the database.
- Some unknown queries sometimes get exported that are preceded with ''~''. I don''t know if they are necessary.
One of my other resources while working on this script was this answer , which helped me to figure out how to export relationships.
Basado en las ideas de esta publicación y en entradas similares en algunos blogs, he escrito una aplicación que funciona con los formatos de archivo mdb y adp. Importa / exporta todos los objetos de la base de datos (incluidas tablas, referencias, relaciones y propiedades de la base de datos) a archivos de texto sin formato. Con esos archivos puede trabajar con cualquier control de versión de origen. La próxima versión permitirá volver a importar los archivos de texto sin formato a la base de datos. También habrá una herramienta de línea de comando
Puede descargar la aplicación o el código fuente desde: http://accesssvn.codeplex.com/
Saludos
Desarrollamos nuestra propia herramienta interna, donde:
- Módulos: se exportan como archivos txt y luego se comparan con la "herramienta de comparación de archivos" (freeware)
- Formularios: se exportan a través del comando undocument application.saveAsText. Entonces es posible ver las diferencias entre 2 versiones diferentes ("herramienta de comparación de archivos" una vez más).
- Macros: no tenemos ninguna macro para comparar, ya que solo tenemos la macro "autoexec" con una línea que inicia el procedimiento principal de VBA
- Consultas: son solo cadenas de texto almacenadas en una tabla: ver más abajo
- tablas: escribimos nuestro propio comparador de tablas, enumerando las diferencias en los registros Y la estructura de la tabla.
Todo el sistema es lo suficientemente inteligente como para permitirnos producir versiones "en tiempo de ejecución" de nuestra aplicación Access, generadas automáticamente a partir de archivos txt (módulos y formularios que se recrean con el comando undocument application.loadFromText) y archivos mdb (tablas).
Puede sonar extraño pero funciona.
Escribimos nuestro propio script en VBScript, que utiliza el Application.SaveAsText () indocumentado en Access para exportar todos los módulos de código, formulario, macro e informe. Aquí está, debería darle algunos consejos. (Cuidado: algunos de los mensajes están en alemán, pero puedes cambiar eso fácilmente).
EDITAR: Para resumir varios comentarios a continuación: Nuestro proyecto asume un archivo .adp. Para que esto funcione con .mdb / .accdb, debe cambiar OpenAccessProject () a OpenCurrentDatabase () . (Se actualizó para usar OpenAccessProject() si ve una extensión .adp, sino que usa OpenCurrentDatabase() .)
decompose.vbs:
'' Usage:
'' CScript decompose.vbs <input file> <path>
'' Converts all modules, classes, forms and macros from an Access Project file (.adp) <input file> to
'' text and saves the results in separate files to <path>. Requires Microsoft Access.
''
Option Explicit
const acForm = 2
const acModule = 5
const acMacro = 4
const acReport = 3
'' BEGIN CODE
Dim fso
Set fso = CreateObject("Scripting.FileSystemObject")
dim sADPFilename
If (WScript.Arguments.Count = 0) then
MsgBox "Bitte den Dateinamen angeben!", vbExclamation, "Error"
Wscript.Quit()
End if
sADPFilename = fso.GetAbsolutePathName(WScript.Arguments(0))
Dim sExportpath
If (WScript.Arguments.Count = 1) then
sExportpath = ""
else
sExportpath = WScript.Arguments(1)
End If
exportModulesTxt sADPFilename, sExportpath
If (Err <> 0) and (Err.Description <> NULL) Then
MsgBox Err.Description, vbExclamation, "Error"
Err.Clear
End If
Function exportModulesTxt(sADPFilename, sExportpath)
Dim myComponent
Dim sModuleType
Dim sTempname
Dim sOutstring
dim myType, myName, myPath, sStubADPFilename
myType = fso.GetExtensionName(sADPFilename)
myName = fso.GetBaseName(sADPFilename)
myPath = fso.GetParentFolderName(sADPFilename)
If (sExportpath = "") then
sExportpath = myPath & "/Source/"
End If
sStubADPFilename = sExportpath & myName & "_stub." & myType
WScript.Echo "copy stub to " & sStubADPFilename & "..."
On Error Resume Next
fso.CreateFolder(sExportpath)
On Error Goto 0
fso.CopyFile sADPFilename, sStubADPFilename
WScript.Echo "starting Access..."
Dim oApplication
Set oApplication = CreateObject("Access.Application")
WScript.Echo "opening " & sStubADPFilename & " ..."
If (Right(sStubADPFilename,4) = ".adp") Then
oApplication.OpenAccessProject sStubADPFilename
Else
oApplication.OpenCurrentDatabase sStubADPFilename
End If
oApplication.Visible = false
dim dctDelete
Set dctDelete = CreateObject("Scripting.Dictionary")
WScript.Echo "exporting..."
Dim myObj
For Each myObj In oApplication.CurrentProject.AllForms
WScript.Echo " " & myObj.fullname
oApplication.SaveAsText acForm, myObj.fullname, sExportpath & "/" & myObj.fullname & ".form"
oApplication.DoCmd.Close acForm, myObj.fullname
dctDelete.Add "FO" & myObj.fullname, acForm
Next
For Each myObj In oApplication.CurrentProject.AllModules
WScript.Echo " " & myObj.fullname
oApplication.SaveAsText acModule, myObj.fullname, sExportpath & "/" & myObj.fullname & ".bas"
dctDelete.Add "MO" & myObj.fullname, acModule
Next
For Each myObj In oApplication.CurrentProject.AllMacros
WScript.Echo " " & myObj.fullname
oApplication.SaveAsText acMacro, myObj.fullname, sExportpath & "/" & myObj.fullname & ".mac"
dctDelete.Add "MA" & myObj.fullname, acMacro
Next
For Each myObj In oApplication.CurrentProject.AllReports
WScript.Echo " " & myObj.fullname
oApplication.SaveAsText acReport, myObj.fullname, sExportpath & "/" & myObj.fullname & ".report"
dctDelete.Add "RE" & myObj.fullname, acReport
Next
WScript.Echo "deleting..."
dim sObjectname
For Each sObjectname In dctDelete
WScript.Echo " " & Mid(sObjectname, 3)
oApplication.DoCmd.DeleteObject dctDelete(sObjectname), Mid(sObjectname, 3)
Next
oApplication.CloseCurrentDatabase
oApplication.CompactRepair sStubADPFilename, sStubADPFilename & "_"
oApplication.Quit
fso.CopyFile sStubADPFilename & "_", sStubADPFilename
fso.DeleteFile sStubADPFilename & "_"
End Function
Public Function getErr()
Dim strError
strError = vbCrLf & "----------------------------------------------------------------------------------------------------------------------------------------" & vbCrLf & _
"From " & Err.source & ":" & vbCrLf & _
" Description: " & Err.Description & vbCrLf & _
" Code: " & Err.Number & vbCrLf
getErr = strError
End Function
Si necesita un comando en el que se pueda hacer clic, en lugar de usar la línea de comando, cree un archivo llamado "decompose.cmd" con
cscript decompose.vbs youraccessapplication.adp
De forma predeterminada, todos los archivos exportados van a la subcarpeta "Scripts" de su aplicación de acceso. El archivo .adp / mdb también se copia a esta ubicación (con un sufijo "stub") y se elimina de todos los módulos exportados, lo que lo hace realmente pequeño.
DEBE registrar este talón con los archivos de origen, ya que la mayoría de las configuraciones de acceso y barras de menús personalizadas no se pueden exportar de ninguna otra manera. Solo asegúrate de realizar cambios en este archivo solo si realmente cambiaste alguna configuración o menú.
Nota: Si tiene Autoexec-Makros definido en su Aplicación, es posible que tenga que mantener presionada la tecla Mayús al invocar la descomposición para evitar que se ejecute e interfiera con la exportación.
Por supuesto, también existe la secuencia de comandos inversa, para construir la aplicación desde el "origen" -directorio:
compose.vbs:
'' Usage:
'' WScript compose.vbs <file> <path>
'' Converts all modules, classes, forms and macros in a directory created by "decompose.vbs"
'' and composes then into an Access Project file (.adp). This overwrites any existing Modules with the
'' same names without warning!!!
'' Requires Microsoft Access.
Option Explicit
const acForm = 2
const acModule = 5
const acMacro = 4
const acReport = 3
Const acCmdCompileAndSaveAllModules = &H7E
'' BEGIN CODE
Dim fso
Set fso = CreateObject("Scripting.FileSystemObject")
dim sADPFilename
If (WScript.Arguments.Count = 0) then
MsgBox "Please enter the file name!", vbExclamation, "Error"
Wscript.Quit()
End if
sADPFilename = fso.GetAbsolutePathName(WScript.Arguments(0))
Dim sPath
If (WScript.Arguments.Count = 1) then
sPath = ""
else
sPath = WScript.Arguments(1)
End If
importModulesTxt sADPFilename, sPath
If (Err <> 0) and (Err.Description <> NULL) Then
MsgBox Err.Description, vbExclamation, "Error"
Err.Clear
End If
Function importModulesTxt(sADPFilename, sImportpath)
Dim myComponent
Dim sModuleType
Dim sTempname
Dim sOutstring
'' Build file and pathnames
dim myType, myName, myPath, sStubADPFilename
myType = fso.GetExtensionName(sADPFilename)
myName = fso.GetBaseName(sADPFilename)
myPath = fso.GetParentFolderName(sADPFilename)
'' if no path was given as argument, use a relative directory
If (sImportpath = "") then
sImportpath = myPath & "/Source/"
End If
sStubADPFilename = sImportpath & myName & "_stub." & myType
'' check for existing file and ask to overwrite with the stub
if (fso.FileExists(sADPFilename)) Then
WScript.StdOut.Write sADPFilename & " exists. Overwrite? (y/n) "
dim sInput
sInput = WScript.StdIn.Read(1)
if (sInput <> "y") Then
WScript.Quit
end if
fso.CopyFile sADPFilename, sADPFilename & ".bak"
end if
fso.CopyFile sStubADPFilename, sADPFilename
'' launch MSAccess
WScript.Echo "starting Access..."
Dim oApplication
Set oApplication = CreateObject("Access.Application")
WScript.Echo "opening " & sADPFilename & " ..."
If (Right(sStubADPFilename,4) = ".adp") Then
oApplication.OpenAccessProject sADPFilename
Else
oApplication.OpenCurrentDatabase sADPFilename
End If
oApplication.Visible = false
Dim folder
Set folder = fso.GetFolder(sImportpath)
'' load each file from the import path into the stub
Dim myFile, objectname, objecttype
for each myFile in folder.Files
objecttype = fso.GetExtensionName(myFile.Name)
objectname = fso.GetBaseName(myFile.Name)
WScript.Echo " " & objectname & " (" & objecttype & ")"
if (objecttype = "form") then
oApplication.LoadFromText acForm, objectname, myFile.Path
elseif (objecttype = "bas") then
oApplication.LoadFromText acModule, objectname, myFile.Path
elseif (objecttype = "mac") then
oApplication.LoadFromText acMacro, objectname, myFile.Path
elseif (objecttype = "report") then
oApplication.LoadFromText acReport, objectname, myFile.Path
end if
next
oApplication.RunCommand acCmdCompileAndSaveAllModules
oApplication.Quit
End Function
Public Function getErr()
Dim strError
strError = vbCrLf & "----------------------------------------------------------------------------------------------------------------------------------------" & vbCrLf & _
"From " & Err.source & ":" & vbCrLf & _
" Description: " & Err.Description & vbCrLf & _
" Code: " & Err.Number & vbCrLf
getErr = strError
End Function
De nuevo, esto va con un compañero "compose.cmd" que contiene:
cscript compose.vbs youraccessapplication.adp
Le pide que confirme que sobrescribe su aplicación actual y primero crea una copia de seguridad, si lo hace. A continuación, recopila todos los archivos de origen en el directorio de origen y los vuelve a insertar en el resguardo.
¡Que te diviertas!
Hay un problema: VSS 6.0 solo puede aceptar MDB usando el complemento bajo una cierta cantidad de objetos, que incluye todas las tablas locales, consultas, módulos y formularios. No sabe el límite exacto del objeto.
Para construir nuestra aplicación de piso de prod de 10 años, que es enorme, nos vemos obligados a combinar 3 o 4 MDB separados de SS en un MDB, lo que complica las construcciones automatizadas hasta el punto de que no perdemos el tiempo haciéndolo.
Creo que probaré el script anterior para arrojar este MDb en SVN y simplificar las construcciones para todos.
La solución de composición / descomposición publicada por Oliver es excelente, pero tiene algunos problemas:
- Los archivos están codificados como UCS-2 (UTF-16) que pueden causar que los sistemas / herramientas de control de versiones consideren que los archivos son binarios.
- Los archivos contienen muchos errores que cambian a menudo: sumas de comprobación, información de la impresora y más. Este es un problema serio si quieres diferencias claras o necesitas cooperar en el proyecto.
Estaba planeando arreglarlo yo mismo, pero descubrí que ya hay una buena solución disponible: github.com/timabell/msaccess-vcs-integration en GitHub. He probado msaccess-vcs-integration y funciona muy bien.
Actualizado el 3 de marzo de 2015 : El proyecto fue originalmente mantenido / propiedad de bkidwell en Github, pero fue transferido a timabell . El enlace anterior al proyecto se actualiza en consecuencia. Hay algunas bifurcaciones del proyecto original por bkidwell, por ejemplo por ArminBra y por matonb , que AFAICT no se debe utilizar.
La desventaja de usar msaccess-vcs-integration en comparación con la solución de descomposición de Olivers:
- Es significativamente más lento. Estoy seguro de que se puede solucionar el problema de velocidad, pero no necesito exportar mi proyecto a un texto que a menudo ...
- No crea un proyecto de acceso auxiliar con las cosas exportadas eliminadas. Esto también se puede arreglar (adoptando el código del script de descomposición), pero de nuevo, no es tan importante.
De todos modos, mi recomendación clara es msaccess-vcs-integration. Resolvió todos los problemas que tuve al usar Git en los archivos exportados.
Olivers responde rocas, pero la referencia CurrentProject no funcionaba para mí. Terminé arrancando las agallas de la mitad de su exportación y reemplazándola con esto, basado en una solución similar de Arvin Meyer . Tiene la ventaja de exportar Consultas si está utilizando un mdb en lugar de un adp.
'' Writes database componenets to a series of text files
'' @author Arvin Meyer
'' @date June 02, 1999
Function DocDatabase(oApp)
Dim dbs
Dim cnt
Dim doc
Dim i
Dim prefix
Dim dctDelete
Dim docName
Const acQuery = 1
Set dctDelete = CreateObject("Scripting.Dictionary")
Set dbs = oApp.CurrentDb() '' use CurrentDb() to refresh Collections
Set cnt = dbs.Containers("Forms")
prefix = oApp.CurrentProject.Path & "/"
For Each doc In cnt.Documents
oApp.SaveAsText acForm, doc.Name, prefix & doc.Name & ".frm"
dctDelete.Add "frm_" & doc.Name, acForm
Next
Set cnt = dbs.Containers("Reports")
For Each doc In cnt.Documents
oApp.SaveAsText acReport, doc.Name, prefix & doc.Name & ".rpt"
dctDelete.Add "rpt_" & doc.Name, acReport
Next
Set cnt = dbs.Containers("Scripts")
For Each doc In cnt.Documents
oApp.SaveAsText acMacro, doc.Name, prefix & doc.Name & ".vbs"
dctDelete.Add "vbs_" & doc.Name, acMacro
Next
Set cnt = dbs.Containers("Modules")
For Each doc In cnt.Documents
oApp.SaveAsText acModule, doc.Name, prefix & doc.Name & ".bas"
dctDelete.Add "bas_" & doc.Name, acModule
Next
For i = 0 To dbs.QueryDefs.Count - 1
oApp.SaveAsText acQuery, dbs.QueryDefs(i).Name, prefix & dbs.QueryDefs(i).Name & ".txt"
dctDelete.Add "qry_" & dbs.QueryDefs(i).Name, acQuery
Next
WScript.Echo "deleting " & dctDelete.Count & " objects."
For Each docName In dctDelete
WScript.Echo " " & Mid(docName, 5)
oApp.DoCmd.DeleteObject dctDelete(docName), Mid(docName, 5)
Next
Set doc = Nothing
Set cnt = Nothing
Set dbs = Nothing
Set dctDelete = Nothing
End Function
Para aquellos que usan Access 2010, SaveAsText no es un método visible en Intellisense, pero parece ser un método válido, ya que el script de Arvin Meyer mencionado anteriormente funcionaba bien para mí.
Curiosamente, SaveAsAXL es nuevo en 2010 y tiene la misma firma que SaveAsText, aunque parece que solo funcionará con bases de datos web, que requieren SharePoint Server 2010.
Parece ser algo bastante disponible en Access:
Este link de msdn explica cómo instalar un complemento de control de código fuente para Microsoft Access. Esto se envió como una descarga gratuita como parte de Access Developer Extensions para Access 2007 y como un complemento independiente para Access 2003.
Me alegra que hayas hecho esta pregunta y me tomé el tiempo para buscarla, ya que también me gustaría esta habilidad. El enlace de arriba tiene más información sobre esto y enlaces a los complementos.
Actualizar:
Instalé el complemento para Access 2003. Solo funcionará con VSS, pero me permite poner objetos de Access (formularios, consultas, tablas, módulos, etc.) en el repositorio. Cuando vaya a editar cualquier artículo en el repositorio, se le pedirá que lo revise, pero no es necesario. A continuación, voy a comprobar cómo se maneja abrirse y modificarse en un sistema sin el complemento. No soy fanático de VSS, pero realmente me gusta la idea de almacenar objetos de acceso en un repositorio.
Actualización2:
Las máquinas sin el complemento no pueden realizar ningún cambio en la estructura de la base de datos (agregue campos de tabla, parámetros de consulta, etc.). Al principio, pensé que esto podría ser un problema si alguien lo necesitaba, ya que no había una forma evidente de eliminar la base de datos de Access del control de código fuente si Access no tenía el complemento cargado.
Id descubrió que la ejecución de la base de datos "compactar y reparar" le indica si desea eliminar la base de datos del control de origen. Opté que sí y pude editar la base de datos sin el complemento. El artículo en el link arriba también da instrucciones para configurar Access 2003 y 2007 para usar Team System. Si puede encontrar un proveedor de MSSCCI para SVN, hay muchas posibilidades de que pueda hacerlo funcionar.
Resucitar un hilo viejo pero este es bueno. Implementé las dos secuencias de comandos (compose.vbs / decompose.vbs) para mi propio proyecto y encontré un problema con los viejos archivos .mdb:
Se detiene cuando llega a un formulario que incluye el código:
NoSaveCTIWhenDisabled =1
Access dice que tiene un problema y que es el final de la historia. Realicé algunas pruebas y jugué tratando de evitar este problema y encontré este hilo con una solución al final:
No se puede crear una base de datos
Básicamente (en caso de que el hilo se muera), tomas el .mdb y haces un "Guardar como" en el nuevo formato .accdb. Entonces la fuente segura o componer / descomponer cosas funcionará. También tuve que jugar durante 10 minutos para obtener la sintaxis correcta de la línea de comando para que los guiones (de) componer funcionen correctamente, así que aquí está esa información también:
Para componer (supongamos que tus cosas se encuentran en C: / SControl (crea una subcarpeta llamada Fuente para almacenar los archivos extraídos):
''(to extract for importing to source control)
cscript compose.vbs database.accdb
''(to rebuild from extracted files saved from an earlier date)
cscript decompose.vbs database.accdb C:/SControl/Source/
¡Eso es!
Las versiones de Access en las que he experimentado el problema anterior incluyen las bases de datos ".mdb" de Access 2000-2003 y solucioné el problema guardándolas en los formatos ".accdb" 2007-2010 antes de ejecutar los scripts de redacción / descomposición. ¡Después de la conversión, los scripts funcionan bien!
Tuvimos el mismo problema hace un tiempo.
Nuestro primer intento fue una herramienta de terceros que ofrece un proxy de SourceSafe API para Subversion que se utilizará con MS Access y VB 6. La herramienta se puede encontrar here .
Como no estábamos tan satisfechos con esa herramienta, cambiamos a Visual SourceSafe y al complemento VSS Acces.
este script no funcionó para nosotros
For completeness...
There''s always "Visual Studio [YEAR] Tools for the Microsoft Office System" ( http://msdn.microsoft.com/en-us/vs2005/aa718673.aspx ) but that seems to require VSS. To me VSS (auto corrupting) is worse than my 347 save points on my uber backuped network share.
I found this tool on SourceForge: http://sourceforge.net/projects/avc/
I haven''t used it, but it may be a start for you. There may be some other 3rd party tools that integrate with VSS or SVN that do what you need.
Personally I just keep a plain text file handy to keep a change log. When I commit the binary MDB, I use the entries in the change log as my commit comment.
I tried to help contribute to his answer by adding an export option for Queries within the access database. (With ample help from other SO answers )
Dim def
Set stream = fso.CreateTextFile(sExportpath & "/" & myName & ".queries.txt")
For Each def In oApplication.CurrentDb.QueryDefs
WScript.Echo " Exporting Queries to Text..."
stream.WriteLine("Name: " & def.Name)
stream.WriteLine(def.SQL)
stream.writeline "--------------------------"
stream.writeline " "
Next
stream.Close
Haven''t be able to work that back into the ''compose'' feature, but that''s not what I need it to do right now.
Note: I also added ".txt" to each of the exported file names in decompose.vbs so that the source control would immediately show me the file diffs.
Hope that helps!
I''m using Oasis-Svn http://dev2dev.de/
I just can tell it has saved me at least once. My mdb was growing beyond 2 GB and that broke it. I could go back to an old version and import the Forms and just lost a day or so of work.
The answer from Oliver works great. Please find my extended version below that adds support for Access queries.
(please see answer from Oliver for more information/usage)
decompose.vbs:
'' Usage:
'' CScript decompose.vbs <input file> <path>
'' Converts all modules, classes, forms and macros from an Access Project file (.adp) <input file> to
'' text and saves the results in separate files to <path>. Requires Microsoft Access.
''
Option Explicit
const acForm = 2
const acModule = 5
const acMacro = 4
const acReport = 3
const acQuery = 1
'' BEGIN CODE
Dim fso
Set fso = CreateObject("Scripting.FileSystemObject")
dim sADPFilename
If (WScript.Arguments.Count = 0) then
MsgBox "Bitte den Dateinamen angeben!", vbExclamation, "Error"
Wscript.Quit()
End if
sADPFilename = fso.GetAbsolutePathName(WScript.Arguments(0))
Dim sExportpath
If (WScript.Arguments.Count = 1) then
sExportpath = ""
else
sExportpath = WScript.Arguments(1)
End If
exportModulesTxt sADPFilename, sExportpath
If (Err <> 0) and (Err.Description <> NULL) Then
MsgBox Err.Description, vbExclamation, "Error"
Err.Clear
End If
Function exportModulesTxt(sADPFilename, sExportpath)
Dim myComponent
Dim sModuleType
Dim sTempname
Dim sOutstring
dim myType, myName, myPath, sStubADPFilename
myType = fso.GetExtensionName(sADPFilename)
myName = fso.GetBaseName(sADPFilename)
myPath = fso.GetParentFolderName(sADPFilename)
If (sExportpath = "") then
sExportpath = myPath & "/Source/"
End If
sStubADPFilename = sExportpath & myName & "_stub." & myType
WScript.Echo "copy stub to " & sStubADPFilename & "..."
On Error Resume Next
fso.CreateFolder(sExportpath)
On Error Goto 0
fso.CopyFile sADPFilename, sStubADPFilename
WScript.Echo "starting Access..."
Dim oApplication
Set oApplication = CreateObject("Access.Application")
WScript.Echo "opening " & sStubADPFilename & " ..."
If (Right(sStubADPFilename,4) = ".adp") Then
oApplication.OpenAccessProject sStubADPFilename
Else
oApplication.OpenCurrentDatabase sStubADPFilename
End If
oApplication.Visible = false
dim dctDelete
Set dctDelete = CreateObject("Scripting.Dictionary")
WScript.Echo "exporting..."
Dim myObj
For Each myObj In oApplication.CurrentProject.AllForms
WScript.Echo " " & myObj.fullname
oApplication.SaveAsText acForm, myObj.fullname, sExportpath & "/" & myObj.fullname & ".form"
oApplication.DoCmd.Close acForm, myObj.fullname
dctDelete.Add "FO" & myObj.fullname, acForm
Next
For Each myObj In oApplication.CurrentProject.AllModules
WScript.Echo " " & myObj.fullname
oApplication.SaveAsText acModule, myObj.fullname, sExportpath & "/" & myObj.fullname & ".bas"
dctDelete.Add "MO" & myObj.fullname, acModule
Next
For Each myObj In oApplication.CurrentProject.AllMacros
WScript.Echo " " & myObj.fullname
oApplication.SaveAsText acMacro, myObj.fullname, sExportpath & "/" & myObj.fullname & ".mac"
dctDelete.Add "MA" & myObj.fullname, acMacro
Next
For Each myObj In oApplication.CurrentProject.AllReports
WScript.Echo " " & myObj.fullname
oApplication.SaveAsText acReport, myObj.fullname, sExportpath & "/" & myObj.fullname & ".report"
dctDelete.Add "RE" & myObj.fullname, acReport
Next
For Each myObj In oApplication.CurrentDb.QueryDefs
if not left(myObj.name,3) = "~sq" then ''exclude queries defined by the forms. Already included in the form itself
WScript.Echo " " & myObj.name
oApplication.SaveAsText acQuery, myObj.name, sExportpath & "/" & myObj.name & ".query"
oApplication.DoCmd.Close acQuery, myObj.name
dctDelete.Add "FO" & myObj.name, acQuery
end if
Next
WScript.Echo "deleting..."
dim sObjectname
For Each sObjectname In dctDelete
WScript.Echo " " & Mid(sObjectname, 3)
oApplication.DoCmd.DeleteObject dctDelete(sObjectname), Mid(sObjectname, 3)
Next
oApplication.CloseCurrentDatabase
oApplication.CompactRepair sStubADPFilename, sStubADPFilename & "_"
oApplication.Quit
fso.CopyFile sStubADPFilename & "_", sStubADPFilename
fso.DeleteFile sStubADPFilename & "_"
End Function
Public Function getErr()
Dim strError
strError = vbCrLf & "----------------------------------------------------------------------------------------------------------------------------------------" & vbCrLf & _
"From " & Err.source & ":" & vbCrLf & _
" Description: " & Err.Description & vbCrLf & _
" Code: " & Err.Number & vbCrLf
getErr = strError
End Function
compose.vbs:
'' Usage:
'' WScript compose.vbs <file> <path>
'' Converts all modules, classes, forms and macros in a directory created by "decompose.vbs"
'' and composes then into an Access Project file (.adp). This overwrites any existing Modules with the
'' same names without warning!!!
'' Requires Microsoft Access.
Option Explicit
const acForm = 2
const acModule = 5
const acMacro = 4
const acReport = 3
const acQuery = 1
Const acCmdCompileAndSaveAllModules = &H7E
'' BEGIN CODE
Dim fso
Set fso = CreateObject("Scripting.FileSystemObject")
dim sADPFilename
If (WScript.Arguments.Count = 0) then
MsgBox "Bitte den Dateinamen angeben!", vbExclamation, "Error"
Wscript.Quit()
End if
sADPFilename = fso.GetAbsolutePathName(WScript.Arguments(0))
Dim sPath
If (WScript.Arguments.Count = 1) then
sPath = ""
else
sPath = WScript.Arguments(1)
End If
importModulesTxt sADPFilename, sPath
If (Err <> 0) and (Err.Description <> NULL) Then
MsgBox Err.Description, vbExclamation, "Error"
Err.Clear
End If
Function importModulesTxt(sADPFilename, sImportpath)
Dim myComponent
Dim sModuleType
Dim sTempname
Dim sOutstring
'' Build file and pathnames
dim myType, myName, myPath, sStubADPFilename
myType = fso.GetExtensionName(sADPFilename)
myName = fso.GetBaseName(sADPFilename)
myPath = fso.GetParentFolderName(sADPFilename)
'' if no path was given as argument, use a relative directory
If (sImportpath = "") then
sImportpath = myPath & "/Source/"
End If
sStubADPFilename = sImportpath & myName & "_stub." & myType
'' check for existing file and ask to overwrite with the stub
if (fso.FileExists(sADPFilename)) Then
WScript.StdOut.Write sADPFilename & " existiert bereits. Überschreiben? (j/n) "
dim sInput
sInput = WScript.StdIn.Read(1)
if (sInput <> "j") Then
WScript.Quit
end if
fso.CopyFile sADPFilename, sADPFilename & ".bak"
end if
fso.CopyFile sStubADPFilename, sADPFilename
'' launch MSAccess
WScript.Echo "starting Access..."
Dim oApplication
Set oApplication = CreateObject("Access.Application")
WScript.Echo "opening " & sADPFilename & " ..."
If (Right(sStubADPFilename,4) = ".adp") Then
oApplication.OpenAccessProject sADPFilename
Else
oApplication.OpenCurrentDatabase sADPFilename
End If
oApplication.Visible = false
Dim folder
Set folder = fso.GetFolder(sImportpath)
'' load each file from the import path into the stub
Dim myFile, objectname, objecttype
for each myFile in folder.Files
objecttype = fso.GetExtensionName(myFile.Name)
objectname = fso.GetBaseName(myFile.Name)
WScript.Echo " " & objectname & " (" & objecttype & ")"
if (objecttype = "form") then
oApplication.LoadFromText acForm, objectname, myFile.Path
elseif (objecttype = "bas") then
oApplication.LoadFromText acModule, objectname, myFile.Path
elseif (objecttype = "mac") then
oApplication.LoadFromText acMacro, objectname, myFile.Path
elseif (objecttype = "report") then
oApplication.LoadFromText acReport, objectname, myFile.Path
elseif (objecttype = "query") then
oApplication.LoadFromText acQuery, objectname, myFile.Path
end if
next
oApplication.RunCommand acCmdCompileAndSaveAllModules
oApplication.Quit
End Function
Public Function getErr()
Dim strError
strError = vbCrLf & "----------------------------------------------------------------------------------------------------------------------------------------" & vbCrLf & _
"From " & Err.source & ":" & vbCrLf & _
" Description: " & Err.Description & vbCrLf & _
" Code: " & Err.Number & vbCrLf
getErr = strError
End Function
This entry describes a totally different approach from the other entries, and may not be what you''re looking for. So I won''t be offended if you ignore this. But at least it is food for thought.
In some professional commercial software development environments, configuration management (CM) of software deliverables is not normally done within the software application itself or software project itself. CM is imposed upon the final deliverable products, by saving the software in a special CM folder, where both the file and its folder are marked with version identification. For example, Clearcase allows the data manager to "check in" a software file, assign it a "branch", assign it a "bubble", and apply "labels". When you want to see and download a file, you have to configure your "config spec" to point to the version you want, then cd into the folder and there it is.
Just an idea.
You can also connect your MS Access to the Team Foundation Server. There is also a free Express variant for up to 5 developers. Works really well!
Edit: fixed link
i''m using the Access 2003 Add-in: Source Code Control . It works fine. One Problem are invalid characters like a ":".
I''m checkin in and out. Internly the Add-In do the same as the code up there, but with more tool support. I can see if an object is checked out and refresh the objects.
Para cualquiera que esté atascado con Access 97, no pude obtener las otras respuestas para trabajar. Utilizando una combinación de DaveParillo''s excelentes respuestas de Oliver''s y DaveParillo''s y realizando algunas modificaciones, pude obtener las secuencias de comandos trabajando con nuestras bases de datos de Access 97. También es un poco más fácil de usar, ya que pregunta qué carpeta colocar los archivos.
AccessExport.vbs:
'' Converts all modules, classes, forms and macros from an Access file (.mdb) <input file> to
'' text and saves the results in separate files to <path>. Requires Microsoft Access.
Option Explicit
Const acQuery = 1
Const acForm = 2
Const acModule = 5
Const acMacro = 4
Const acReport = 3
Const acCmdCompactDatabase = 4
Const TemporaryFolder = 2
Dim strMDBFileName : strMDBFileName = SelectDatabaseFile
Dim strExportPath : strExportPath = SelectExportFolder
CreateExportFolders(strExportPath)
Dim objProgressWindow
Dim strOverallProgress
CreateProgressWindow objProgressWindow
Dim strTempMDBFileName
CopyToTempDatabase strMDBFileName, strTempMDBFileName, strOverallProgress
Dim objAccess
Dim objDatabase
OpenAccessDatabase objAccess, objDatabase, strTempMDBFileName, strOverallProgress
ExportQueries objAccess, objDatabase, objProgressWindow, strExportPath, strOverallProgress
ExportForms objAccess, objDatabase, objProgressWindow, strExportPath, strOverallProgress
ExportReports objAccess, objDatabase, objProgressWindow, strExportPath, strOverallProgress
ExportMacros objAccess, objDatabase, objProgressWindow, strExportPath, strOverallProgress
ExportModules objAccess, objDatabase, objProgressWindow, strExportPath, strOverallProgress
objAccess.CloseCurrentDatabase
objAccess.Quit
DeleteTempDatabase strTempMDBFileName, strOverallProgress
objProgressWindow.Quit
MsgBox "Successfully exported database."
Private Function SelectDatabaseFile()
MsgBox "Please select the Access database to export."
Dim objFileOpen : Set objFileOpen = CreateObject("SAFRCFileDlg.FileOpen")
If objFileOpen.OpenFileOpenDlg Then
SelectDatabaseFile = objFileOpen.FileName
Else
WScript.Quit()
End If
End Function
Private Function SelectExportFolder()
Dim objShell : Set objShell = CreateObject("Shell.Application")
SelectExportFolder = objShell.BrowseForFolder(0, "Select folder to export the database to:", 0, "").self.path & "/"
End Function
Private Sub CreateExportFolders(strExportPath)
Dim objFileSystem : Set objFileSystem = CreateObject("Scripting.FileSystemObject")
MsgBox "Existing folders from a previous Access export under " & strExportPath & " will be deleted!"
If objFileSystem.FolderExists(strExportPath & "Queries/") Then
objFileSystem.DeleteFolder strExportPath & "Queries", true
End If
objFileSystem.CreateFolder(strExportPath & "Queries/")
If objFileSystem.FolderExists(strExportPath & "Forms/") Then
objFileSystem.DeleteFolder strExportPath & "Forms", true
End If
objFileSystem.CreateFolder(strExportPath & "Forms/")
If objFileSystem.FolderExists(strExportPath & "Reports/") Then
objFileSystem.DeleteFolder strExportPath & "Reports", true
End If
objFileSystem.CreateFolder(strExportPath & "Reports/")
If objFileSystem.FolderExists(strExportPath & "Macros/") Then
objFileSystem.DeleteFolder strExportPath & "Macros", true
End If
objFileSystem.CreateFolder(strExportPath & "Macros/")
If objFileSystem.FolderExists(strExportPath & "Modules/") Then
objFileSystem.DeleteFolder strExportPath & "Modules", true
End If
objFileSystem.CreateFolder(strExportPath & "Modules/")
End Sub
Private Sub CreateProgressWindow(objProgressWindow)
Set objProgressWindow = CreateObject ("InternetExplorer.Application")
objProgressWindow.Navigate "about:blank"
objProgressWindow.ToolBar = 0
objProgressWindow.StatusBar = 0
objProgressWindow.Width = 320
objProgressWindow.Height = 240
objProgressWindow.Visible = 1
objProgressWindow.Document.Title = "Access export in progress"
End Sub
Private Sub CopyToTempDatabase(strMDBFileName, strTempMDBFileName, strOverallProgress)
strOverallProgress = strOverallProgress & "Copying to temporary database...<br/>"
Dim objFileSystem : Set objFileSystem = CreateObject("Scripting.FileSystemObject")
strTempMDBFileName = objFileSystem.GetSpecialFolder(TemporaryFolder) & "/" & objFileSystem.GetBaseName(strMDBFileName) & "_temp.mdb"
objFileSystem.CopyFile strMDBFileName, strTempMDBFileName
End Sub
Private Sub OpenAccessDatabase(objAccess, objDatabase, strTempMDBFileName, strOverallProgress)
strOverallProgress = strOverallProgress & "Compacting temporary database...<br/>"
Set objAccess = CreateObject("Access.Application")
objAccess.Visible = false
CompactAccessDatabase objAccess, strTempMDBFileName
strOverallProgress = strOverallProgress & "Opening temporary database...<br/>"
objAccess.OpenCurrentDatabase strTempMDBFileName
Set objDatabase = objAccess.CurrentDb
End Sub
'' Sometimes the Compact Database command errors out, and it''s not serious if the database isn''t compacted first.
Private Sub CompactAccessDatabase(objAccess, strTempMDBFileName)
On Error Resume Next
Dim objFileSystem : Set objFileSystem = CreateObject("Scripting.FileSystemObject")
objAccess.DbEngine.CompactDatabase strTempMDBFileName, strTempMDBFileName & "_"
objFileSystem.CopyFile strTempMDBFileName & "_", strTempMDBFileName
objFileSystem.DeleteFile strTempMDBFileName & "_"
End Sub
Private Sub ExportQueries(objAccess, objDatabase, objProgressWindow, strExportPath, strOverallProgress)
strOverallProgress = strOverallProgress & "Exporting Queries (Step 1 of 5)...<br/>"
Dim counter
For counter = 0 To objDatabase.QueryDefs.Count - 1
objProgressWindow.Document.Body.InnerHTML = strOverallProgress & counter + 1 & " of " & objDatabase.QueryDefs.Count
objAccess.SaveAsText acQuery, objDatabase.QueryDefs(counter).Name, strExportPath & "Queries/" & Clean(objDatabase.QueryDefs(counter).Name) & ".sql"
Next
End Sub
Private Sub ExportForms(objAccess, objDatabase, objProgressWindow, strExportPath, strOverallProgress)
strOverallProgress = strOverallProgress & "Exporting Forms (Step 2 of 5)...<br/>"
Dim counter : counter = 1
Dim objContainer : Set objContainer = objDatabase.Containers("Forms")
Dim objDocument
For Each objDocument In objContainer.Documents
objProgressWindow.Document.Body.InnerHTML = strOverallProgress & counter & " of " & objContainer.Documents.Count
counter = counter + 1
objAccess.SaveAsText acForm, objDocument.Name, strExportPath & "Forms/" & Clean(objDocument.Name) & ".form"
objAccess.DoCmd.Close acForm, objDocument.Name
Next
End Sub
Private Sub ExportReports(objAccess, objDatabase, objProgressWindow, strExportPath, strOverallProgress)
strOverallProgress = strOverallProgress & "Exporting Reports (Step 3 of 5)...<br/>"
Dim counter : counter = 1
Dim objContainer : Set objContainer = objDatabase.Containers("Reports")
Dim objDocument
For Each objDocument In objContainer.Documents
objProgressWindow.Document.Body.InnerHTML = strOverallProgress & counter & " of " & objContainer.Documents.Count
counter = counter + 1
objAccess.SaveAsText acReport, objDocument.Name, strExportPath & "Reports/" & Clean(objDocument.Name) & ".report"
Next
End Sub
Private Sub ExportMacros(objAccess, objDatabase, objProgressWindow, strExportPath, strOverallProgress)
strOverallProgress = strOverallProgress & "Exporting Macros (Step 4 of 5)...<br/>"
Dim counter : counter = 1
Dim objContainer : Set objContainer = objDatabase.Containers("Scripts")
Dim objDocument
For Each objDocument In objContainer.Documents
objProgressWindow.Document.Body.InnerHTML = strOverallProgress & counter & " of " & objContainer.Documents.Count
counter = counter + 1
objAccess.SaveAsText acMacro, objDocument.Name, strExportPath & "Macros/" & Clean(objDocument.Name) & ".macro"
Next
End Sub
Private Sub ExportModules(objAccess, objDatabase, objProgressWindow, strExportPath, strOverallProgress)
strOverallProgress = strOverallProgress & "Exporting Modules (Step 5 of 5)...<br/>"
Dim counter : counter = 1
Dim objContainer : Set objContainer = objDatabase.Containers("Modules")
Dim objDocument
For Each objDocument In objContainer.Documents
objProgressWindow.Document.Body.InnerHTML = strOverallProgress & counter & " of " & objContainer.Documents.Count
counter = counter + 1
objAccess.SaveAsText acModule, objDocument.Name, strExportPath & "Modules/" & Clean(objDocument.Name) & ".module"
Next
End Sub
Private Sub DeleteTempDatabase(strTempMDBFileName, strOverallProgress)
On Error Resume Next
strOverallProgress = strOverallProgress & "Deleting temporary database...<br/>"
Dim objFileSystem : Set objFileSystem = CreateObject("Scripting.FileSystemObject")
objFileSystem.DeleteFile strTempMDBFileName, true
End Sub
'' Windows doesn''t like certain characters, so we have to filter those out of the name when exporting
Private Function Clean(strInput)
Dim objRegexp : Set objRegexp = New RegExp
objRegexp.IgnoreCase = True
objRegexp.Global = True
objRegexp.Pattern = "[///:*?""<>|]"
Dim strOutput
If objRegexp.Test(strInput) Then
strOutput = objRegexp.Replace(strInput, "")
MsgBox strInput & " is being exported as " & strOutput
Else
strOutput = strInput
End If
Clean = strOutput
End Function
Y para importar archivos a la base de datos, debería volver a crear la base de datos desde cero o si desea modificar archivos fuera de Access por alguna razón.
AccessImport.vbs:
'' Imports all of the queries, forms, reports, macros, and modules from text
'' files to an Access file (.mdb). Requires Microsoft Access.
Option Explicit
const acQuery = 1
const acForm = 2
const acModule = 5
const acMacro = 4
const acReport = 3
const acCmdCompileAndSaveAllModules = &H7E
Dim strMDBFilename : strMDBFilename = SelectDatabaseFile
CreateBackup strMDBFilename
Dim strImportPath : strImportPath = SelectImportFolder
Dim objAccess
Dim objDatabase
OpenAccessDatabase objAccess, objDatabase, strMDBFilename
Dim objProgressWindow
Dim strOverallProgress
CreateProgressWindow objProgressWindow
ImportQueries objAccess, objDatabase, objProgressWindow, strImportPath, strOverallProgress
ImportForms objAccess, objDatabase, objProgressWindow, strImportPath, strOverallProgress
ImportReports objAccess, objDatabase, objProgressWindow, strImportPath, strOverallProgress
ImportMacros objAccess, objDatabase, objProgressWindow, strImportPath, strOverallProgress
ImportModules objAccess, objDatabase, objProgressWindow, strImportPath, strOverallProgress
objAccess.CloseCurrentDatabase
objAccess.Quit
objProgressWindow.Quit
MsgBox "Successfully imported objects into the database."
Private Function SelectDatabaseFile()
MsgBox "Please select the Access database to import the objects from. ALL EXISTING OBJECTS WITH THE SAME NAME WILL BE OVERWRITTEN!"
Dim objFileOpen : Set objFileOpen = CreateObject( "SAFRCFileDlg.FileOpen" )
If objFileOpen.OpenFileOpenDlg Then
SelectDatabaseFile = objFileOpen.FileName
Else
WScript.Quit()
End If
End Function
Private Function SelectImportFolder()
Dim objShell : Set objShell = WScript.CreateObject("Shell.Application")
SelectImportFolder = objShell.BrowseForFolder(0, "Select folder to import the database objects from:", 0, "").self.path & "/"
End Function
Private Sub CreateBackup(strMDBFilename)
Dim objFileSystem : Set objFileSystem = CreateObject("Scripting.FileSystemObject")
objFileSystem.CopyFile strMDBFilename, strMDBFilename & ".bak"
End Sub
Private Sub OpenAccessDatabase(objAccess, objDatabase, strMDBFileName)
Set objAccess = CreateObject("Access.Application")
objAccess.OpenCurrentDatabase strMDBFilename
objAccess.Visible = false
Set objDatabase = objAccess.CurrentDb
End Sub
Private Sub CreateProgressWindow(ByRef objProgressWindow)
Set objProgressWindow = CreateObject ("InternetExplorer.Application")
objProgressWindow.Navigate "about:blank"
objProgressWindow.ToolBar = 0
objProgressWindow.StatusBar = 0
objProgressWindow.Width = 320
objProgressWindow.Height = 240
objProgressWindow.Visible = 1
objProgressWindow.Document.Title = "Access import in progress"
End Sub
Private Sub ImportQueries(objAccess, objDatabase, objProgressWindow, strImportPath, strOverallProgress)
strOverallProgress = "Importing Queries (Step 1 of 5)...<br/>"
Dim counter : counter = 0
Dim folder : Set folder = objFileSystem.GetFolder(strImportPath & "Queries/")
Dim objFileSystem : Set objFileSystem = CreateObject("Scripting.FileSystemObject")
Dim file
Dim strQueryName
For Each file in folder.Files
objProgressWindow.Document.Body.InnerHTML = strOverallProgress & counter + 1 & " of " & folder.Files.Count
strQueryName = objFileSystem.GetBaseName(file.Name)
objAccess.LoadFromText acQuery, strQueryName, file.Path
counter = counter + 1
Next
End Sub
Private Sub ImportForms(objAccess, objDatabase, objProgressWindow, strImportPath, strOverallProgress)
strOverallProgress = strOverallProgress & "Importing Forms (Step 2 of 5)...<br/>"
Dim counter : counter = 0
Dim folder : Set folder = objFileSystem.GetFolder(strImportPath & "Forms/")
Dim objFileSystem : Set objFileSystem = CreateObject("Scripting.FileSystemObject")
Dim file
Dim strFormName
For Each file in folder.Files
objProgressWindow.Document.Body.InnerHTML = strOverallProgress & counter + 1 & " of " & folder.Files.Count
strFormName = objFileSystem.GetBaseName(file.Name)
objAccess.LoadFromText acForm, strFormName, file.Path
counter = counter + 1
Next
End Sub
Private Sub ImportReports(objAccess, objDatabase, objProgressWindow, strImportPath, strOverallProgress)
strOverallProgress = strOverallProgress & "Importing Reports (Step 3 of 5)...<br/>"
Dim counter : counter = 0
Dim folder : Set folder = objFileSystem.GetFolder(strImportPath & "Reports/")
Dim objFileSystem : Set objFileSystem = CreateObject("Scripting.FileSystemObject")
Dim file
Dim strReportName
For Each file in folder.Files
objProgressWindow.Document.Body.InnerHTML = strOverallProgress & counter + 1 & " of " & folder.Files.Count
strReportName = objFileSystem.GetBaseName(file.Name)
objAccess.LoadFromText acReport, strReportName, file.Path
counter = counter + 1
Next
End Sub
Private Sub ImportMacros(objAccess, objDatabase, objProgressWindow, strImportPath, strOverallProgress)
strOverallProgress = strOverallProgress & "Importing Macros (Step 4 of 5)...<br/>"
Dim counter : counter = 0
Dim folder : Set folder = objFileSystem.GetFolder(strImportPath & "Macros/")
Dim objFileSystem : Set objFileSystem = CreateObject("Scripting.FileSystemObject")
Dim file
Dim strMacroName
For Each file in folder.Files
objProgressWindow.Document.Body.InnerHTML = strOverallProgress & counter + 1 & " of " & folder.Files.Count
strMacroName = objFileSystem.GetBaseName(file.Name)
objAccess.LoadFromText acMacro, strMacroName, file.Path
counter = counter + 1
Next
End Sub
Private Sub ImportModules(objAccess, objDatabase, objProgressWindow, strImportPath, strOverallProgress)
strOverallProgress = strOverallProgress & "Importing Modules (Step 5 of 5)...<br/>"
Dim counter : counter = 0
Dim folder : Set folder = objFileSystem.GetFolder(strImportPath & "Modules/")
Dim objFileSystem : Set objFileSystem = CreateObject("Scripting.FileSystemObject")
Dim file
Dim strModuleName
For Each file in folder.Files
objProgressWindow.Document.Body.InnerHTML = strOverallProgress & counter + 1 & " of " & folder.Files.Count
strModuleName = objFileSystem.GetBaseName(file.Name)
objAccess.LoadFromText acModule, strModuleName, file.Path
counter = counter + 1
Next
'' We need to compile the database whenever any module code changes.
If Not objAccess.IsCompiled Then
objAccess.RunCommand acCmdCompileAndSaveAllModules
End If
End Sub