Version of built database does not match the origin database

[cenx1]

Systeminfo

Access Version: MS Office 365 64bit msaccess-vcs-integration: 3.1.14

Details

When I create a new database in Access and enable BigInt (File-->Options-->Current Database-->Data Type Support Options-->Support Large Number...) the database version is bumped to 16.7 (see this in Immediate Window by running ?CurrentDb.Version).

After export and build from source the database version is 14.

Tables that are imported and uses the BigInt datatype are converted to Decimal and I suspect that this is due to the incorrect version?

[joyfullservice]

This might have something to do with the new database sort order... I noticed the same kind of thing in Access 2010 where the version of the new database didn't match the original. After changing the following setting from General - Legacy to General, the versions matched after build.

This seems to at least loosely correspond with the (read-only) Database.CollatingOrder property, but I don't see different enums for the legacy and non-legacy versions. You can also pass an argument when creating a database that seems to drive the format of the new database...

This obviously would need a bit more research and testing, but I think we may be on the right track...

[A9G-Data-Droid]

The BigInt feature was new in 2016 and I know that @joyfullservice uses Office 2010. That being the case I will try to work out the way to maintain this feature without breaking 2010 support.

[A9G-Data-Droid]

So the export is using the built-in function Application.ExportXML which currently doesn't include any indication that the field is BigInt. The only identifier I am seeing in my test code is this:

<xsd:restriction base="xsd:decimal">

This makes sense the the subsequent Application.ImportXML would use the decimal format.

We may need to look at the export format and either acExportAllTableAndFieldProperties or maybe just use the new JSON format to handle it manually.

[joyfullservice]

Yes, I would like to use acExportAllTableAndFieldProperties because there are other properties that are not being exported in the basic format. We just need to handle the sanitizing of the XML to remove GUIDs and other properties that we don't want in VCS.

[A9G-Data-Droid]

@joyfullservice If our goal is to be able to rebuild a new DB identical to the old one, why would there be anything we wouldn't want in the VCS? What is wrong with the GUIDs?

[A9G-Data-Droid]

I have verified this doesn't work using the built-in export and import functions of Access, even when acExportAllTableAndFieldProperties is used. If you export \import from the Access GUI it generates a table with Number instead of Large Number and then it fails to import the LongLong values. This being the case, we need to move away from the Access XML functions if we want to support BigInt databases. Or we would have to track the BigInt fields separately and set them up correctly in between schema and data import steps.

[A9G-Data-Droid]

NOTE: Field.Type = dbBigInt

dbBigInt = 16

[joyfullservice]

Nice! My hunch is that it is going to be easier for us to use XML to build the majority of the structure, and then supplement with a json file to add in the additional details like the bigInt data types when applicable.

[joyfullservice]

Regarding the GUIDs, these will change from build to build, so if you have multiple people working on the same project you will have collisions with data that is specific to that particular build. This is already implemented in forms and reports where the GUIDs are stripped out to avoid these very conflicts.

[A9G-Data-Droid]

Looks like the only way to modify an existing field is with SQL:

ALTER TABLE TableName ALTER COLUMN FieldName BIGINT

(You can't just change the .Type)

[hecon5]

This is old, but since the tables export as SQL as an option; what about using that; then you've already built the SQL (just like if you were to use SSMS to export a database)? When you import, just snag the SQL in lieu of the XML?

[joyfullservice]

Extended table properties are now exported as of version 3.3.28. See 11eb40b for details.

[joyfullservice]

I think I will go ahead and close this out since we have resolve the original issue of the database file version.

As to the BigInt data type issue, let's go ahead and create a new issue for that, and copy over some of these details. I don't have a way to easily test it on my end, but I am curious to see how close we are getting now that we are able to export the extend table properties.

[A9G-Data-Droid]

Is version 3.3.28 going to be the next release?

[joyfullservice]

I pushed out a few updates on the dev branch today, including a more significant one that improves the XML output files for table definitions. I am thinking we might want to do a little more testing before pushing out a general release, since this involves some bigger changes.