Come si usa il controllo della versione con lo sviluppo di Access?

Sono coinvolto nell’aggiornamento di una soluzione di accesso. Ha una buona quantità di VBA, un numero di query, una piccola quantità di tabelle e alcuni moduli per l’immissione di dati e la generazione di report. È un candidato ideale per l’accesso.

Voglio apportare modifiche al design della tabella, al VBA, alle query e ai moduli. Come posso monitorare le mie modifiche con il controllo della versione? (Usiamo Subversion, ma questo vale per ogni gusto) Posso attaccare l’intero mdb in subversion, ma questo sarà archiviare un file binario, e non sarò in grado di dire che ho appena cambiato una riga di codice VBA.

Ho pensato di copiare il codice VBA per separare i file e salvarli, ma ho notato che quelli rapidamente non erano sincronizzati con quelli contenuti nel database.

Abbiamo scritto il nostro script in VBScript, che utilizza l’Application.SaveAsText () non documentato in Access per esportare tutti i moduli di codice, modulo, macro e report. Eccolo, dovrebbe darti alcuni suggerimenti. (Attenzione: alcuni dei messaggi sono in tedesco, ma puoi facilmente cambiarlo.)

EDIT: per riassumere vari commenti di seguito: Il nostro progetto presuppone un file .adp. Per ottenere questo lavoro con .mdb / .accdb, devi modificare OpenAccessProject () in OpenCurrentDatabase () . (Aggiornato per usare OpenAccessProject() se vede un’estensione .adp, altrimenti usa OpenCurrentDatabase() .)

decompose.vbs:

 ' Usage: ' CScript decompose.vbs   ' Converts all modules, classs, forms and macros from an Access Project file (.adp)  to ' text and saves the results in separate files to . 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 

Se è necessario un comando selezionabile, anziché utilizzare la riga di comando, creare un file denominato “decompose.cmd” con

 cscript decompose.vbs youraccessapplication.adp 

Per impostazione predefinita, tutti i file esportati vengono inseriti in una sottocartella “Script” dell’applicazione di accesso. Il file .adp / mdb viene anche copiato in questa posizione (con un suffisso “stub”) e privato di tutti i moduli esportati, rendendolo davvero piccolo.

È NECESSARIO controllare questo stub con i file di origine, poiché la maggior parte delle impostazioni di accesso e le barre dei menu personalizzate non possono essere esportate in altro modo. Assicurati di confermare solo le modifiche a questo file, se hai veramente cambiato alcune impostazioni o menu.

Nota: se nella propria applicazione sono definiti Autoexec-Makros, potrebbe essere necessario tenere premuto il tasto Maiusc quando si invoca il decompose per impedirne l’esecuzione e interferire con l’esportazione!

Naturalmente, c’è anche lo script inverso, per build l’applicazione dalla directory “Source”:

compose.vbs:

 ' Usage: ' WScript compose.vbs   ' Converts all modules, classs, 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 

Di nuovo, questo va con un compagno “compose.cmd” contenente:

 cscript compose.vbs youraccessapplication.adp 

Ti chiede di confermare la sovrascrittura dell’applicazione corrente e prima crea un backup, se lo fai. Quindi raccoglie tutti i file di origine nella directory di origine e li reinserisce nello stub.

Divertiti!

Sembra essere qualcosa di abbastanza disponibile in Access:

Questo collegamento da msdn spiega come installare un componente aggiuntivo di controllo del codice sorgente per Microsoft Access. Questo è stato spedito come download gratuito come parte di Access Developer Extensions per Access 2007 e come componente aggiuntivo separato per Access 2003.

Sono felice che tu abbia fatto questa domanda e mi sono preso il tempo di cercarlo, visto che mi piacerebbe anche quest’abilità. Il link sopra ha più informazioni su questo e link ai componenti aggiuntivi.

Aggiornare:
Ho installato il componente aggiuntivo per Access 2003. Funzionerà solo con VSS, ma mi consente di inserire oggetti di Access (moduli, query, tabelle, moduli, ect) nel repository. Quando vai a modificare qualsiasi elemento nel repository ti viene chiesto di verificarlo, ma non devi. Successivamente vedrò come viene gestito l’apertura e la modifica su un sistema senza il componente aggiuntivo. Non sono un fan di VSS, ma mi piace davvero l’idea di archiviare gli oggetti di accesso in un repository.

Update2:
Le macchine senza il componente aggiuntivo non sono in grado di apportare modifiche alla struttura del database (aggiungere campi tabella, parametri di query, ecc.). All’inizio pensavo che questo potesse essere un problema se qualcuno ne avesse avuto bisogno, poiché non c’era un modo apparente per rimuovere il database di Access dal controllo del codice sorgente se Access non aveva caricato il componente aggiuntivo.

L’ID ha rilevato che l’esecuzione del database “Compatta e ripristina” ti richiede se desideri rimuovere il database dal controllo del codice sorgente. Ho optato Sì ed è stato in grado di modificare il database senza il componente aggiuntivo. L’articolo nel link sopra riportato fornisce anche istruzioni per l’impostazione di Access 2003 e 2007 per l’utilizzo di Team System. Se riesci a trovare un provider MSSCCI per SVN, c’è una buona possibilità che tu possa farlo funzionare.

Gli Oliver rispondono alle rocce, ma il riferimento a CurrentProject non funzionava per me. Ho finito per strappare le budella dal centro dell’esportazione e sostituirla con questa, basata su una soluzione simile di Arvin Meyer . Ha il vantaggio di esportare le Query se stai usando un mdb invece di 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 

La soluzione di composizione / decomposizione pubblicata da Oliver è ottima, ma presenta alcuni problemi:

  • I file sono codificati come UCS-2 (UTF-16), il che può causare che i sistemi / gli strumenti di controllo di versione considerino i file binari.
  • I file contengono molti dettagli che cambiano spesso: checksum, informazioni sulla stampante e altro. Questo è un problema serio se si desidera che le differenze siano chiare o che sia necessario collaborare al progetto.

Stavo pianificando di risolvere il problema da solo, ma ho scoperto che esiste già una buona soluzione disponibile: timabell / msaccess-vcs-integration su GitHub. Ho testato msaccess-vcs-integration e funziona benissimo.

Aggiornato il 3 marzo 2015 : il progetto è stato originariamente mantenuto / di proprietà di bkidwell su Github, ma è stato trasferito su timabell – il link sopra al progetto è aggiornato di conseguenza. Ci sono alcune biforcazioni del progetto originale di bkidwell, ad esempio ArminBra e matonb , che AFAICT non dovrebbe essere utilizzato.

Lo svantaggio di utilizzare l’integrazione msaccess-vcs rispetto alla soluzione di decomposizione di Oliver:

  • È molto più lento. Sono sicuro che il problema di velocità può essere risolto, ma non ho bisogno di esportare il mio progetto in testo che spesso …
  • Non crea un progetto di accesso stub con le cose esportate rimosse. Questo può anche essere risolto (adottando il codice dallo script decompose), ma ancora una volta – non è così importante.

Ad ogni modo, la mia chiara raccomandazione è msaccess-vcs-integration. Ha risolto tutti i problemi che avevo con l’utilizzo di Git sui file esportati.

Abbiamo sviluppato il nostro strumento interno, dove:

  1. Moduli: vengono esportati come file txt e quindi confrontati con “strumento di confronto file” (freeware)
  2. Moduli: vengono esportati tramite il comando non documentato application.saveAsText. È quindi ansible vedere le differenze tra 2 versioni differenti (“strumento di confronto file” ancora una volta).
  3. Macro: non abbiamo macro da confrontare, poiché abbiamo solo la macro “autoexec” con una riga che avvia la procedura VBA principale
  4. Query: sono solo stringhe di testo memorizzate in una tabella: vedi infra
  5. tabelle: abbiamo scritto il nostro comparatore di tabelle, elencando le differenze nei record e nella struttura delle tabelle.

L’intero sistema è abbastanza intelligente da consentirci di produrre versioni “runtime” della nostra applicazione Access, generate automaticamente da file txt (moduli e moduli ricreati con il comando non documentato application.loadFromText) e file mdb (tabelle).

Potrebbe sembrare strano ma funziona.

Sulla base delle idee di questo post e di voci simili in alcuni blog, ho scritto un’applicazione che funziona con i formati di file mdb e adp. Importa / esporta tutti gli oggetti del database (comprese tabelle, riferimenti, relazioni e proprietà del database) in file di testo semplice. Con questi file puoi lavorare con qualsiasi controllo di versione di origine. La prossima versione consentirà di importare nuovamente i file di testo in chiaro nel database. Ci sarà anche uno strumento da riga di comando

È ansible scaricare l’applicazione o il codice sorgente da: http://accesssvn.codeplex.com/

Saluti

Resuscitare un thread vecchio ma questo è buono. Ho implementato i due script (compose.vbs / decompose.vbs) per il mio progetto e ho riscontrato un problema con i vecchi file .mdb:

Si blocca quando arriva a un modulo che include il codice:

 NoSaveCTIWhenDisabled =1 

Access dice che ha un problema e questa è la fine della storia. Ho eseguito alcuni test e ho cercato di aggirare questo problema e ho trovato questo thread con un aggiramento alla fine:

Imansible creare il database

Fondamentalmente (nel caso in cui il thread si spenga), prendi il .mdb e fai un “Salva con nome” nel nuovo formato .accdb. Quindi funzionerà la fonte sicura o comporre / decomporre. Ho dovuto anche giocare per 10 minuti per ottenere la syntax della riga di comando corretta per gli script (de) comporre per funzionare correttamente, quindi ecco anche queste informazioni:

Per comporre (diciamo che i tuoi contenuti si trovano in C: \ SControl (crea una sotto cartella chiamata Source per memorizzare i file estratti):

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

Questo è tutto!

Le versioni di Access in cui ho riscontrato il problema sopra includono i database “.mdb” di Access 2000-2003 e risolvono il problema salvandole nei formati “.accdb” del 2007-2010 prima di eseguire gli script compose / decompose. Dopo la conversione gli script funzionano bene!

C’è un trucco – VSS 6.0 può accettare solo MDB utilizzando il componente aggiuntivo sotto un certo numero di oggetti, che include tutte le tabelle locali, query, moduli e moduli. Non conosco il limite dell’object esatto.

Per build la nostra app di pavimento da 10 anni, che è enorme, siamo costretti a combinare 3 o 4 MDB separati da SS in un MDB, il che complica le build automatizzate al punto che non perdiamo tempo a farlo.

Penso che proverò lo script di cui sopra per diffondere questo MDb in SVN e semplificare le build per tutti.

questo script non ha funzionato per noi.

questo ha fatto: http://www.accessmvp.com/Arvin/DocDatabase.txt

Per coloro che utilizzano Access 2010, SaveAsText non è un metodo visibile in Intellisense ma sembra essere un metodo valido, come lo script di Arvin Meyer menzionato in precedenza ha funzionato bene per me.

È interessante notare che SaveAsAXL è nuovo al 2010 e ha la stessa firma di SaveAsText, anche se sembra funzionerà solo con i database Web, che richiedono SharePoint Server 2010.

Abbiamo avuto lo stesso problema qualche tempo fa.

Il nostro primo tentativo è stato uno strumento di terze parti che offre un proxy dell’API SourceSafe per Subversion da utilizzare con MS Access e VB 6. Lo strumento può essere trovato qui .

Poiché non eravamo soddisfatti di tale strumento, siamo passati a Visual SourceSafe e al plug-in VSS Acces.

Soluzione solo per file di testo (query, tabelle e relazioni incluse)

Ho modificato la coppia di script di Oliver in modo che esportino / importino relazioni, tabelle e query oltre a moduli, classi, moduli e macro. Tutto è salvato in file di testo in chiaro, quindi non c’è nessun file di database creato per essere archiviato con i file di testo nel controllo di versione.

Esportare in file di testo (decompose.vbs)

 ' Usage: ' cscript decompose.vbs   ' Converts all modules, classs, forms and macros from an Access Project file (.adp)  to ' text and saves the results in separate files to . 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 

È ansible eseguire questo script chiamando cscript decompose.vbs . Se si omette il secondo parametro, verrà creata la cartella “Origine” in cui si trova il database. Si noti che la cartella di destinazione verrà cancellata se già esiste.

Includere i dati nelle tabelle esportate

Sostituisci riga 93: oApplication.ExportXML acExportTable, myObj.Name, , sExportpath & "\" & myObj.Name & ".table.txt"

con la riga oApplication.ExportXML acExportTable, myObj.Name, sExportpath & "\" & myObj.Name & ".table.txt"

Importa in Crea file di database (compose.vbs)

 ' Usage: ' cscript compose.vbs   ' Reads all modules, classs, 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 . 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

  1. I have tested this only with .accdb files, so with anything else there might be some bugs.
  2. Setting are not exported, I would recommend creating the Macro that will apply the setting at start of the database.
  3. 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.

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.

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’m using the Access 2003 Add-in: Source Code Control . Funziona bene. 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.

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.

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!

  • English guide
  • Team Foundation Server 2012 Express

Edit: fixed link

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   ' Converts all modules, classs, forms and macros from an Access Project file (.adp)  to ' text and saves the results in separate files to . 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   ' Converts all modules, classs, 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 

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.

Spero possa aiutare!


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.

Solo un’idea

For anyone stuck with Access 97, I was not able to get the other answers to work. Using a combination of Oliver’s and DaveParillo’s excellent answers and making some modifications, I was able to get the scripts working with our Access 97 databases. It’s also a bit more user-friendly since it asks which folder to place the files.

AccessExport.vbs:

 ' Converts all modules, classs, forms and macros from an Access file (.mdb)  to ' text and saves the results in separate files to . 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...
" 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...
" Set objAccess = CreateObject("Access.Application") objAccess.Visible = false CompactAccessDatabase objAccess, strTempMDBFileName strOverallProgress = strOverallProgress & "Opening temporary database...
" 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)...
" 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)...
" 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)...
" 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)...
" 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)...
" 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...
" 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

And for importing files into the database, should you need to recreate the database from scratch or you wish to modify files outside of Access for some reason.

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)...
" 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)...
" 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)...
" 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)...
" 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)...
" 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