msaccess-vcs-integration/msaccess-vcs-integration

Synchronize your Access Forms, Macros, Modules, Queries, and Reports with a version control system. This is a collaborative effort.

msaccess-vcs-integration

Warning

This tool can delete / break things, take a backup before getting started.

Alternatives

This project was forked and comprehensively re-written into an add-in for access. Considerably more work has been done on that version so I strongly suggest checking out joyfullservice/msaccess-vcs-addin before choosing which tool to use.

About

Synchronize your Microsoft Access Database definition with a version control system.

Included in the export/import:

• Queries
• Forms
• Reports
• Macros
• Modules
• Table Data
• Table Definitions
• Table Data Macros

Not included in the export/import:

• Customized toolbars, toolbar items, and menu items
• Any external files
• Pretty much anything that is not accessible by browsing the design, properties, and code of a Query, Form, Report, Macro, or Module object.

This code is built and tested on Microsoft Access 2010/2013. It will probably work in earlier/later versions, but YMMV.

This README shows how to synchronize all application code objects from an Access application with a source control system such as Mercurial or Git. (The provided import/export module is agnostic about the actual source control system you use.)

Encoding

For Access objects which are normally exported in UCS-2-little-endian encoding , the included module automatically converts to the source code to and from UTF-8 encoding during export/import; this is to ensure that you don't have trouble branching, merging, and comparing in tools such as Mercurial which treat any file containing 0x00 bytes as a non-diffable binary file.

Output

The module will put the files in a folder called source within the same folder as your database file. The import expects the files to be in the same folder.

Installing the Integration Scripts

For the purposes of these instructions, assume your database is called Application.accdb.

1. Load VCS_Loader.bas into a new module in your database with that exact name.
2. Go to the VBA editor (CTRL-G) and select "File" > "Import File..." (or you can just drag and drop the file from windows explorer into the vba editor module list).
3. Select the VCS_Loader.bas file.
4. Save the file (CTRL-S).
5. Type "loadVCS" into the immediate window followed by the directory where the other VCS files are located. If you don't specify a directory then it is assumed that the VCS code is contained in a folder called 'MSAccess-VCS' in the database directory; e.g. loadVCS "C:\Users\MyUserAccount\Documents\Access-Proj\MSAccess-VCS\" - the trailing slash is required or loadVCS will not import the new modules.
6. Edit your VCS_ImportExport and change the constant INCLUDE_TABLES to list any lookup tables that function more as part of your application code than as client data. (For example, "Countries", "Colors", and things like that.)

Updating UIRibbon

1. Right click anywhere on the ribbon, click customize ribbon.
2. Then click on Import/Export then click import customization file and open ./UIRibbon/FORM UPDATES.exportedUI.

Configuring export

By default, no table data is exported. You must specify which tables' data to include in the export/import process by editing the INCLUDE_TABLES variable in the supplied module. For example you might have "Countries" or "Colors" tables that populate dropdown lists. You shouldn't include regular data tables containing actual records, because this data doesn't belong in version control.

Additionally, if a type of data should not be exported, change the "Export_" constants to False in VCS_ImportExport.bas. Report, Query, Form, Macro, Module, and Table exports can be disabled individually.

Supplied databases

In the demo\ folder there's a blank database that you can use with to provide with your source-controlled files, or to test the import; and a demo database with a sample of all the things that this project can import/export for trying the project out and testing any code changes made to the project.

First Commit to Your Source Control System

1. Create a repository in the folder containing your database.
2. Compact and Repair Application.accdb and zip it to Application.zip using the Send to Compressed Folder command in Windows Explorer.
3. Using your repository's tools, set the repository to ignore any .accdb and .laccdb files, and then add and commit the zipped Access binary file Application.zip. Use a commit message like "Initial commit of [name] at version [number]."
4. Open the application, hit CTRL-G, and run the following VB code in the Immediate window: "ExportAllSource". Wait for the Immediate window to say the export job is "Done."
5. Using your repository's tools, add and commit all the new files that were created in the source folder. Use a commit message like "Initial commit of all source code for [name] at version [number]".
6. Publish your repository to your preferred central sharing location.

Committing New Progress and Pulling Changes from Other Developers

1. Open the application, hit CTRL-G, and run the following VB code in the Immediate window: "ExportAllSource". Wait for the Immediate window to say the export job is "Done."
2. Using your repository's tools, commit all the new files that were created in the source folder. Use an appropriate commit message to describe your changes.
3. Pull new upstream changes (if any exist) from your central sharing location used by all developers. If necessary address any merge conflicts using your repository's merge and conflict resolution tools. If any work was done in this step, commit these changes to your local repository as well.
4. Push all local and merged changes back to the central sharing location.
5. Go back into the Access Immediate window (CTRL-G) and run the following VB code: "ImportAllSource". Wait for the Immediate window to say the export job is "Done."

Committing a New "Release" of Your Project

1. There may be application changes that aren't covered in the source code for Forms, Macros, Modules, Queries, and Reports. To make sure these changes are recorded, Compact and Repair Application.accdb and zip it to Application.zip (replacing the old copy) using the Send to Compressed Folder command in Windows Explorer. Commit the new Application.zip to your repository with a commit message like "Full application binary for release [number]".
2. Follow the usual steps in the previous section "Committing New Progress".
3. Use your repository's "tag" function to tag your last commit with the release number/name.

Loading/updating a database from the exported files

1. Create a new Access database (or use the supplied demo\blank.accdb).
2. Follow the instructions for installing the scripts.
3. Open the VBA editor (CTRL-G) and run the following VB code in the Immediate window: "ImportProject". You will be presented with a warning telling you that all database objects are about to be deleted, allowing you to cancel the operation if you change you mind.
4. Wait until the code finishes executing, Compact and Repair the database.

Caveats

• If you make changes to or add a new module, be sure to save it in the VB Editor window or else it will not be exported.
• If you make any changes to the scripts used in this process, the VCS_ modules, they will not be automatically imported when any developer runs the ImportProject method. The code skips these files because it causes a conflict when trying to update a module that is actively being executed.

Contributing

Pull requests, issue reports etc welcomed.

https://github.com/msaccess-vcs-integration/msaccess-vcs-integration is the most actively maintained branch, and Tim Abell will generally accept pull requests to keep the project alive but has minimal capacity to ensure correctness so please try and keep the quality as good as you can. Thanks!

Several other people have been made collaborators to help keep the repo alive. Better ideas for running this project would be welcome, please discuss in the related issue.

Related tools

• Add-in version of this: https://github.com/joyfullservice/msaccess-vcs-integration

msaccess-vcs-integration's Issues

reimporting reseting tables

I don't know if I misunderstood the documentation, but when I run the import it deletes any information in the tables, is there a way to disable this default behavior?

Force Saving of Table Relationships

Provide an option to force the saving of table relationships or provide documentation which describes the way to create relationships in databases in a manner which will be recorded by the VCS scripts.

Next Steps

This is the general plan for what will be done to the project next.

1. Fix bugs in current version
2. Release version
3. Refactor current version, seperating each responsibility into its own module
   • eg Functions to handle tables put into their own module
   • Will need to add a module to load in other modules or compile the modules together for each release
4. Add in missing features
   • Data Macro Import/Export
   • Linked table Import/Export (Link definition)
   • Table relationships Import/Export (This is only available for normal tables in the current version)
5. Release version
6. Rework & improve existing features

Discussion/Design: Change of paths & export of tables

This issue is:

• Bug report
• Feature request
• Improvement

Actual behaviour

1. I was thinking from a software update perspective it might be better to have the access db one level above this folder. Right now for access to work properly w/ this it must be in the same folder. E.g. place the db file inside the msaccess-vcs-integration folder. Then import the VCS_Loader.bas file.

msaccess-vcs-integration
  ./MSAccess-VCS
  ./blank.accdb
  ...

2. Kind of on this subject would be the separation of the export tables. Currently you can only export lookup tables to one location. It may be wise to separate the tables selected for export from the normal ones & maybe tables containing pii/Personally_identifiable_information.

Expected behaviour

1. It may be easier from an update perspective to have them one level above.

./blank.accdb
./msaccess-vcs-integration
   ./MSAccess-VCS
   ...

Doing it this way will make it easier to pull changes. Imagine working for a client & having them just pull the latest version from the release or download page & just replacing the msaccess-vcs-integration folder w/ the new update & then they can just update the .accdb file that is one level above.
This change would require some path changes & re-importing the VCS_Loader.bas file.

2. For your particular customization of this for a project you may want to store some lookup tables to git, while allow the export ability for others, but not track to git. You could then ignore these tables using .gitignore or make the export path be one level above outside of the /msaccess-vcs-integration folder.

Configuration

• Microsoft Access version: N/A
• OS: N/A

Still trying to export table even though INCLUDE_TABLES = ""

Hi,

The title just about says it all. The process fails when it gets to the export table def function. It gets even weirder because the script ends up calling the export linked table function. Any ideas on why it would do this?

Error on export: Multi-valued field cannot be used in ORDER BY clause

It looks like it isn't exporting the multi-valued field data correctly.

I'm getting the error in VCS_ExportTableData on this line:
Set rs = CurrentDb.OpenRecordset(TableExportSql(tbl_name))

Stepping through, it looks like it is building the SQL query, but you can't order by a multi-value field.

The issue is specifically a value list - as in each option is just typed in row source.

Anyone know how to fix this?

"File exists" errors (error code 58) in SanitizeTextFiles()

While trying to ExportAllSource, I am receiving "file exists" errors (error code 58) from SanitizeTextFiles(). The error occurs at the call of thisFile.Move:

        FSO.DeleteFile (Path & fileName)

        Dim thisFile As Object
        Set thisFile = FSO.GetFile(Path & obj_name & ".sanitize")
        thisFile.Move (Path & fileName)
        fileName = Dir()
    Loop


End Sub

I have tried testing for file existence both after the FSO.DeleteFile call, and before the thisFile.Move call, but the error continues to occur. It does not occur on the same file each time. If anyone has any thoughts or has experienced the same problem, please let me know.

Feature Request: Select all or some objects for Import/Export

Hello,

I've been looking for a Source Control for quite awhile now. I did use VSS6 but found it unreliable ie problems building a new DB.

Found msaccess-vcs-integration and I'm currently exporting all my forms, reports ,modules etc.
Some forms and reports are quite large so when exporting/ importing all it will take time.

Is there a possability Tim or an active Contributor could take a look at implementing a form where you can choose which object to export or import?

A reason for this is because if you make a change in one object then ideally I'd just want to export that object and not wait 20 or more minutes.

Another thing I found was half way through importing reports it crashed (ms acccess has stopped working). I found out that one of the reports were currupt as I couldn't view this reports design, so I had to delete it. then I had to re-run the export :(, So in this senario I could of started the import from where access crashed.

Regards
Darren

Error 2128 on import into blank DB - file encoding issue

Is anyone else experiencing this/able to replicate?

Using Access 2013...

Steps taken:

1. Run an export from Access 2002-2003 format MDB.
2. Create a new database in either MDB or ACCDB format in the same folder as the original database.
3. Go into VBA Editor and import VCS_Loader.bas from this repository.
4. Run VCS_Loader.loadVCS
5. Run VCS_ImportExport.ImportAll.

Expected output:
Debug output in the Immediate window showing progress, and then the database populated with imported objects on finish.

What I got:
Error dialog saying:

Run-time error '2128':

Microsoft Access encountered errors while importing [object name].

Clicking Debug points the cursor to Line 37 of VCS_IE_Functions, Application.LoadFromText obj_type_num, obj_name, tempFileName.

Additionally, a text file named errors.txt appears in the same folder as the new MDB with the following text:

Microsoft Access encountered an error while importing the object '[object name]'.

Error encountered at line 1.
Expected: 'Operation'. Found: O.

Workaround:
Forcing Ucs2Convert in VCS_IE_Functions.ImportObject to False fixes this. (Alternatively, I commented out the If-Else-End block except for the Else block's contents - the call to Application.LoadFromText).

Clearly this is an encoding issue, but I don't know enough about how exports were encoded in the past to say for sure what to do here. Hell, for all I know this could be isolated to just me. It seems to me that Access 2013 doesn't like UCS2 Little Endian encoding for its imports?

Error exporting objects whose name contains a "/".

The export wants to write files that are named the same as the underlying object. So a query named "XXX" becomes a file named "query\XXX.bas". All well and good unless you have a query or other object whose name contains an illegal character for file naming, like "/" or ":". I have inherited a project that uses "/" extensively in query names. Clearly we can do some renaming on output, and un-renaming on input, to deal with this issue. I can even write the code to do it myself. But does anyone have suggestions about another way, or the best way, to handle this?

I guess another way to do it would be to add an "object_name=" to the exported data, and then process the object_name in the import process, rather than taking the object name from the file name. Then we could just rename the file by substituting an innocuous character for the offending character, and not worry about having a reversible translation.

Merge Hangie Develop Branch

@hangie doesn't appear to have been active in over a month. This person has made a lot of changes to the develop branch that solve many issues here. Some changes in the last month have caused merge conflicts. I plan to work on creating a new merge branch as I mentioned in #57 to work on settling these merge conflicts.

Unless @hangie has any comments against this, I'll be putting this together shortly and awaiting another reviewer.

This branch reflects the merge in process. merge/hangie_develop

The merge to this branch is complete, but it needs to be tested to validate the merge conflicts were solved as intented.

I'll be linking issues supposedly solved by @hangie's work here:
_ #

Backslash "\" in table field

Table field
"LIBRARY\NAME"

ExportAllSource will write to text file
"LIBRARY\\NAME"

ImportAllSource will interpret that as:

"LIBRARY\
AME"

A workaround could be adding in ExportTableData
Value = Replace(Value, "\", " \ ")

and ImportTableData
Value = Replace(Value, " \ ", "\")

Function UsingUcs2, output file format of SaveAsText

First, sorry about my English. It is not my language.

I have checked the output-exported format of SaveAsText (modules, forms, macros, reports and datamacros) depend on format of BBDD. For example, in files .mdb (acFileFormatAccess2002, ...) always have UTF8 format. In contrast to the .accdb (acFileFormatAccess12) that have UCS2 (except modules => UTF8).

I checked with Access 2003 and Access 2010 (see constant AcFileFormat):

App. Ver.		acFileFormatAccess12	acFileFormatAccess2002	acFileFormatAccess2000	acFileFormatAccess97	acFileFormatAccess95	acFileFormatAccess2
11	Access 2003	N/A	UTF8	UTF8	UTF8	??	??
12	Access 2007	??	??	??	??	??	??
14	Access 2010	UCS2 *	UTF8	UTF8	UTF8	??	??
15	Access 2013	??	??	??	??	??	??
??	Access 2016	??	??	??	??	??	??

* Except modules (=> UTF8)

Then I think that the function UsingUcs2 can be simplified as dramosr/msaccess-vcs-integration@fb5d117

support storing access-only relationships between linked tables

Request made as part of the discussion of issue #10

The original breakage has been resolved (probably) in pr #22 by not exporting relationships for linked tables at all, so this new issue records the desire to record relationships that were created in access that don't exist on the underlying sql server table

While I think their existence is a bad idea and the relationships should in theory I can see how you'd end up in this situation in the real world and still want to be able to record their existence.

Shared images are not exported/imported

I know that, according to the Readme, not all the elements are included in the export, but I wonder whether this would be impossible or it is only that it has not be taken into account. I see that embedded images are in fact exported.

Shared images is a useful feature since Acesss 2010: https://msdn.microsoft.com/en-us/library/office/gg490661%28v=office.14%29.aspx

The workaround is to restore them by hand. Enter in the properties of one of the elements lacking the image and press the [...] button in the Image property to browse and assign the image. The image will be then visible in all the location sharing it.

Failed to register everything from references.csv

Error on import:
---------------------------
Microsoft Access
---------------------------
Failed to register {000204EF-0000-0000-C000-000000000046}
---------------------------
OK
---------------------------

references.csv:

{000204EF-0000-0000-C000-000000000046},4,2
{4AFFC9A0-5F99-101B-AF4E-00AA003F0F07},9,0
{00020430-0000-0000-C000-000000000046},2,0
{4AC9E1DA-5BAD-4AC7-86E3-24F4CDCECA28},12,0
{B691E011-1797-432E-907A-4D8C69339129},6,1
{00020813-0000-0000-C000-000000000046},1,8
{420B2830-E718-11CF-893D-00A0C9054228},1,0
{00020905-0000-0000-C000-000000000046},8,6
,0,0

Message repeated for about half of the refs, couldn't get any to load by elimination

Seems to have loaded them anyway so may be a red herring.

linked tables - Chokes on Relations export: Bad file name or number

I've been put in charge of managing an Access database that's been around for a while (so it's in MDB format), and when I installed this to try out possible version control solutions, it gets caught up on the relationship export and throws a error 52 "Bad file name or number".

On closer inspection I find that the reason this happens is because my relationships are named as such: [<full path to MDB file>].[<guid>]

EDIT: Actually, only one relationship is a GUID. The rest of them are like [<full path to MDB file>].[<TableOneTableTwo>]

Can anyone suggest a workaround for this?

Table 'General Tab' Values Not Recorded

Any values entered into the General Tab for a field are not stored in the VCS.

e.g. the default value in my date field is set to =Now() but this does not appear in the VCS.

Documentation corrections

This is a great project - thanks for all of your work. I had a couple of issues with the documentation that I believe are very easy to tackle:

1. References: With Office 2013 I had to enable the following references for it to work: and "Microsoft ADO Ext. 6.0 for DLL and Security" AND "Microsoft ActiveX Data Objects 6.1 Library". If you already have "Microsoft Office 15.0 Access Database Engine Object Library", then "Microsoft DAO 3.6 Object Library" will throw an error and should be unnecessary.
2. INCLUDE_TABLES variable is a string with the format "Table1,Table2,Table3" - I had to look into the code to figure it out.

New Release/Tag

I've noticed it's been like several years since a downloadable release has been made. Could you @timabell push a new release?

Typically to push a release you use tags.
E.g.
git tag "v1.0.1" -a "2018 release \n template support, ui_ribbon w/ buttons \n etc"
git push origin --tags

Also @timabell is there a chance you could add me on as a direct contributor to this project so that I may more directly work on the issues & pull requests?

I'm also thinking about writing some unit tests using rubberduckvba. They have some code analysis that gives a bunch of naming recommendations & explicitly variable declarations that should be implemented.

Query Export

If a query uses a single letter as an alias to another table or the alias has an underscore in it, when you try to import the data back in, the import fails. If you remove the underscore or make the alias at least 2 characters the import works.

Table name with "/" in name

If a table name has a "/" in the name, for example, "Table/Name" the export will fail on Line 124 in VCS_Table

Set OutFile = FSO.CreateTextFile(fileName, overwrite:=True, Unicode:=False)

Foreign keys causing import failure - import order issue

When the SQL for TableDefs is created, it includes foreign key CONSTRAINTs. In the case of one of my backend databases, this makes it impossible to import the TableDefs into an empty database as the importer executes the SQL of each TableDef in the order it's encountered (i.e. alpha order), and if a FK references a table that hasn't been built yet, the engine throws an error stating as such.

On a related note, if tables with FKs import correctly (e.g. tables being depended on are higher in alpha order), Access then attempts to import Relationships, and that fails for me because the relationships already exist on account of the FKs being defined in TableDefs.

My question is, is it necessary to export both FK CONSTRAINTs in TableDefs alongside Relationships if they'll be built later on the Relationship import? I can see how it can be useful to have that information there, like if you wanted to rebuild the DB structure in a proper RDBMS like MySQL or SQL Server, but for the purposes of reconstituting Access databases from code it seems redundant.

MS Access 2013

Hi to All!,
Is there a chance that msaccess-vcs-integration could be possible to work with MS Access 2013?
Please reply soon!
Thanks!
Jhun Cabas

Invalid filename characters in object names...

A colleague of mine preferred to name their queries by prepending a question mark ?. That is unfortunately an invalid filename character, this breaking ExportAllSource. Maybe in such cases something percent encoding could be used?

Attribute VB_Name: trouble in and out

The .bas files have Attribute VB_Name = "VCS_xxx" as their first line. When I attempted to import a file with such a line (VCS_Query.bas) VBA and the regular Access window hung and I had to kill Access. This was repeatable.

Further, when I file out code with VCS_IE_Functions.VCS_ExportObject acModule, m.name, fpath the resulting file does not have Attribute VB_Name.

When I deleted the first line from VCS_Query.bas I was able to import it. I made similar deletions from the other files awhile ago--I noticed this becaue VCS_Query is new to me (just did a fetch).

VBA version 1643, also identified as Retail 7.0.1643
Access 14.0.7197.5000 (32 bit) part of Office Pro 2010.
Win 7 Ultimate 6.1.7601 SP 1 Build 7601 (64 bit)
Database is an .mdb file, the front end of a split database. Probably created with an earlier version of Access.

DeleteFile function

This isn't being used (it's commented out).
@timabell Do you want to keep this or can it be deleted?

Initial Import Stops

Hi Tim,

Followed readme but when I do ExportAllSource its stops after exporting forms. I see it calls SanitizeTextFiles after the form count is displayed but it just stops. When I ctrl+break the code I DoEvent is highlighted in yellow but I cannot step over/into.
Code window says running in title bar.

Any advice?

Regards,
Darren

Mixed line endings

This project seems to have CRLF line endings as the default in the repo, however if using autcrlf this can cause issues as you can push files with LF endings instead.

Change repository to reflect this default.

Error 2950 "Reservated Error" on ExportAllSource

Error 2950 "Reservated Error" on ExportAllSource in modules.
I think the module name is Módulo Validación NIF/IBAN. maybe is the erro?

better approach to managing this project needed

call for ideas!

although I've promised to keep this project alive and accept pull requests, I don't have the time / resources to properly review and test them myself. I wonder if there's a way of allowing the most interested / capable people to contribute without me having to decide on a whim who gets the "commit bit"

please pile in here with thoughts as I feel like it's starting to stagnate already

support for adp

Since adp files don't support CurrentDB we can't use

Dim Db As Object ' DAO.Database
Set Db = CurrentDb
For Each qry In Db.QueryDefs -> leads to error 91 Object variable or with block variable not set
For Each doc In Db.Containers(obj_type_name).Documents

so we have to use CurrentProject instead of Currentdb

If CurrentDb Is Nothing Then
For Each Module in CurrentProject.AllModules

Doesn't export/import CurrentDB.Properties

I use properties (instead of a local table) for the handful of local values I need for the current user. Unfortunately, this doesn't (yet?) support exporting and importing them. I would gather an active developer could fix this faster than I could.

ExportAllSource Stalls Access after 30 minutes

First, many thanks for this excellent tool! It does exactly what we need it to and has been working great for most of our Access databases.

Unfortunately I have one database that has a large number of forms and queries, that has stalled out and crashed Access each time I've run the command. It's successful for some forms and I'm not seeing an error, just a long churning process which fails after about 30 minutes.

One thing I've considered is trying to "chunk" the export by type, although I'm thinking this is more an issue of large numbers of forms (~60) so type wouldn't handle it as much as only exporting a certain number of forms, reports, queries, etc. at a time.

Any suggestions on how to get past the stall? I'm thinking perhaps its a tweak to ExportAllSource to comment out a certain type or something similar. Perhaps others have experienced a similar roadblock where they are unable to fully get through the export?

Thanks again for any/all recommendations.

Error handling in ExportAllSource appears to be incorrect

The table export logic includes the following:

                ElseIf (Len(Replace(INCLUDE_TABLES, " ", "")) > 0) And INCLUDE_TABLES <> "*" Then
                    DoEvents
                    On Error GoTo Err_TableNotFound
                    If IncludeTablesCol(td.Name) = td.Name Then
                        VCS_Table.ExportTableData CStr(td.Name), source_path & "tables\"
                        obj_data_count = obj_data_count + 1
                    End If
Err_TableNotFound:

                'else don't export table data
                End If

This fails for me on the second instance of td.Name that is not in INCLUDE_TABLES. The problem appears to be an assumption that On Error GoTo works the same as a regular GoTo. A VBA error handler must use a Resume statement to exit the error handler and resume normal execution. If it doesn't, the next error will cause an abend, as there is no valid error handler. I found that the following change fixed the problem:

                ElseIf (Len(Replace(INCLUDE_TABLES, " ", "")) > 0) And INCLUDE_TABLES <> "*" Then
                    DoEvents
                    On Error GoTo Err_TableNotFound
                    If IncludeTablesCol(td.Name) = td.Name Then
                        VCS_Table.ExportTableData CStr(td.Name), source_path & "tables\"
                        obj_data_count = obj_data_count + 1
                    End If
Resume_TableNotFound:

                'else don't export table data
                End If

...

    Debug.Print "Done."
    Exit Sub

Err_TableNotFound:
    Resume Resume_TableNotFound


End Sub

I would do a "pull request", but I'm not really familiar with git yet, and I'm not sure that I want to set up a repository (until I have more changes to suggest).

Failed to register {000204EF-0000-0000-C000-000000000046}

Tested on two machines and gets the same error on both of them when trying the import function from Access 2013

failed to register {000204EF-0000-0000-C000-000000000046}

Code Analysis Refactoring: rubberduckvba analysis

I've been able to get rubberduckvba. it had a bunch of recommendations for naming conventions, explicit variable declarations & etc. I was thinking about doing a pull request with these changes. This would be a major change, but the functionality would be the same. It also would be nice to start adding some unit tests for regression testing on pull requests.

I looked into it and it is not possible to automate this with travis b/c to do unit tests on vba or using rubberduckvba an office application must be open & it won't work from command line. E.g you can only run this from microsoft access vba editor.

I did find a previous issue #17. But I'm proposing running its analysis not coupling this w/ rubberduckvba. If we were to add unit tests these would have to be in some sort of ignored folder or folder treated appropriately.

Access 2013 loadfromtext ... Use Theme / other control properties not set on import

Hello,

I recently upgraded to Access 2013 and noticed that importing no longer brings in certain control settings. So far, I've only noticed this on command buttons: the "use theme" setting is always "no" on import to 2013, and the hover color, pressed color, etc. are set to #FFFFFF (white). I'm having a tough time tracking this down as loadfromtext isn't documented. Has anyone else experienced this and/or have a working fix? My fix for the time being will be to 1) open the form created from the loadfromtext, 2) open / iterate over the command buttons from the form.bas, and reset the control properties in the newly-created form. I was just hoping there was a way around that, if known. Thanks.

convert to access add-in

as suggested in issue #17

this might avoid the use of vba.

I'm not volunteering by the way ;-)

Possible optimization using MSysObjects

Hi, we have a rather large MDB application, importing and exporting the changes takes a long time (about 15-25 minutes).

Would it be feasible to check the DateUpdate column in MSysObjects table and export only those objects that have changed since the last export (or commit)?

load-VCS hangs indefinitely

Hi,

just started playing around with msaccess-vcs-integration yesterday, I like what I see so far, this will be very useful. One small bug report:
If you issue the initial loadVCS command in the direct window, things get stuck when the file VCS_Button_Functions.bas is being processed. The VBA backend just hangs indefinitely. I load the MSAccess-VCS files from a different location than the database, so load-VCS "\\someunc\MSAccess-VCS". Removing VCS_Button_Functions.bas helps, all other files then load flawlessly. I can't really see why this is happening, VCS_Button_Functions.bas just contains two function definitions.

best regards,

Can't import a query just exported: error 2128

I got the latest version of this tool from git last week and installed it into my application.
Ran ExportAllSource, with no apparent problems.
Closed Access, failing to save some of the changes I made to the VCS code (to get a list of tables to export and to export VCS_ImportExport since I modified it).
Restarted Access and ran ImportAllSource
Importing queries failed with tmpq_Prep. Error was 2128 and errors.txt has

WKTrack 1.84a encountered an error while importing the object 'tmpq_Prep'.

Error encountered at line 6.
Could not create or set the property Connect.
Error encountered at line 8.
Could not create or set the property SQL.

When I try to save the query to a file from the main Access GUI it complains the query can't be shown in designer view because it is not representable there. And when I try to view it in Access Design View I get the error

It does show in Sql view. If I right click on the top of the frame and attempt to save I get JOIN expression not supported. The query is.
SELECT tblPreparation.PreparationID is not null AS inTable, tblPreparation.PreparationID, tmpq_Proc.ProcedureID, tmpq_Proc.collectiondate, tmpq_Proc.participantid, tmpq_Proc.proctyp, 20 AS preptyp, now() AS curdt, 47 AS prepby FROM tmpq_Proc LEFT JOIN tblPreparation ON tmpq_Proc.ProcedureID=tblPreparation.ProcedureID and tblPreparation.PreparationType=20;
I note that the ON clause looks very odd since tblPreparation.Preparation=20 compares a key to a constant (this is legacy code). However, the queries do run in Access.

I also tried manually importing frmBST.bas to get code changes I made to the application. The importer says the form class contained in the .bas file is not supported in VBE and the file can't be loaded. The .bas file contains both the form specification and the module code.

I don't know how closely LoadFromText and SaveAsText are related to the file save and load functions available from the GUI. Since I can't seem to save the query from the GUI, they must differ.

VCS_UsingUcs2 is False for me.

The database is an .mdb file created by splitting a database, originally probably in Office c 2003. I am running 32 bit Office 2010 on 64-bit Windows 7. The default format for a blank database is Access 2002-2003 (some similarish error reports indicated mismatch between the default format and the format of the active db could cause trouble).

VCS_IE_Functions includes
#If VBA7 Then Private Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long) #Else Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long) #End If
I have some doubts about whether that will work on a 64-bit system; it seems a stretch, but perhaps it's related to the main problem. Help | About says I am running VBA 7.0.

Error deleting relations: 3211

Run-time error '3211': The database engine could not lock table 'employees_tbl' because it is already in use by another person or process

On a split database, CurrentDb.Relations.delete fails on a locked table. This kind of makes sense, but a workaround would be good. Deleting the relation of a locked table from the NavPane works.

creates blank xml files on *import*

very odd, ran out of time debugging this, it's something to to do with the ImportObject() function in IE_Functions

https://github.com/timabell/msaccess-vcs-integration/blob/master/MSAccess-VCS/VCS_IE_Functions.bas#L30

eg msaccess-vcs-integration\demo\source\tbldef\color_lookup.xml

import crashes on importing forms

This seems to be happening since moving to Access 2016.
When I try to import a freshly exported project into a blank database, the following happens:

• the import complains that it failed to register {05BFD3F1-6319-4F30-B752-C7A22889BCC4} and {05BFD3F1-6319-4F30-B752-C7A22889BCC4}
• import begins but during "importing forms" phase, Access crashes completely

When I reopen the DB, I see that only 3 forms have been imported out of the 14 expected.

Any idea why this would happen?

Decimal types are exported as VARCHAR

With the current version, when exporting decimal types, they are converted to VARCHAR without size. This is due to the function VCS_Table.strType returning VARCHAR in the Else branch.

I have a solution for this, but it has a drawback: for extracting the Precision and NumericScale we have to use another library in the references: Microsoft ADO Ext. 2.x for DLL and Security. See https://social.msdn.microsoft.com/Forums/office/en-US/883087ba-2c25-4571-bd3c-706061466a11/how-can-i-programmatically-access-scale-property-of-a-decimal-data-type-field?forum=accessdev

I don't know wether that would be a problem.

Another change should be done in the import in order to recognize the DECIMAL field in the SQL. One must use ADODB.Connection for the Execute of the SQL. This is working for me as far as the references problem is reached (Issue #16).

I don't know if both changes are problematic or not. Would this be accepted in the project? I can make a pull request if you want to see the code.

Rubberduck integration?

In my search for a cvs I came across this project but also I found RubberDuck here: https://github.com/rubberduck-vba/Rubberduck/wiki

Both are actually incomplete: this project allows all components to be under source countrol, but rubberduck has better integration with git.

It could be an idea to use both apporaches to (initially?) create a rubberduck -accessvcs integartion fork?

Syntax error in CONSTRAINT clause

Our setup is that we use a sql server backend.

On trying to 'importallsource', I encountered the error in the subject.

(exportallsource worked ok)

Please let me know if you need more information

[not sure if this related to #16 ]

variable name capitalisation being changed - vba editor issue

We have noticed that sometimes doing ExportAllSource results in huge numbers of commits, with the apparent change being along these lines:
-On Error GoTo Err_Form_Open
+On Error GoTo err_Form_Open
or
-If Me!PerUID.oldValue > "" And Me!PerUID.oldValue <> ...
+If Me!PerUID.OldValue > "" And Me!PerUID.OldValue <> ...
This does not happen every time, but when it does, our repo is flooded with spurious commits.
At first sight I can't see why this is happening - in both the above cases it is hard to see how the file could genuinely have been in the previous state (e.g. .oldValue is always instantly converted to .OldValue on entry). And we know that all/most of the files showing changes have not been edited by a human since the previous export.
Any ideas? Will post if we find the cause.

Error handler in ExportAllSource section for INCLUDE_TABLES fails

I have tried this with "INCLUDE_TABLES" variable set and it systematically raises an error with the second table. I believe that the problem is that the recalling the collection item with a non-existing key raises an error and the second time it does it, the ON ERROR does not work because the error Status is set already. I do not know if there is something particular in my configuration that raises this error. In any case, I need to clean up the last error before running the next ON ERROR. I have solved it easily by adding an ON ERROR GOTO -1 just after the Err_TableNotFound: label, as in

Err_TableNotFound:
'else don't export table data
'Clean up error status so that the error handler works again.
On Error GoTo -1 End If

Could be done a little more elegantly but I think this is a simple solution using the same approach intended in the original code. Another way would be to use a function to determine if td.name is a member of the collection. The function uses On ERROR anyway:

Public Function Contains(col As Collection, key As Variant) As Boolean
Dim obj As Variant
On Error GoTo err
Contains = True
IsObject(col(key))
Exit Function
err:
Contains = False
End Function

This is from:
http://stackoverflow.com/questions/137845/determining-whether-an-object-is-a-member-of-a-collection-in-vba