|
|
CADconform for MicroStation
|
This document explains issues related to using Microsoft Access® databases (*.mdb, *.accdb) with CADconform. If you use a different ODBC-compliant database with CADconform, such as SQL Server® or Oracle®, then this document is not relevant to you.
CADconform and its standards databases have been delivered with Microsoft
Access databases by default since MicroStation J. However, with newer versions
of the Access database, there may be some configuration changes necessary for
using Access with CADconform. The circumstances that may require additional
steps include:
The additional steps may involve installation of additional ODBC drivers, which may require administrative privileges. There may also be changes required to the File Data Source Names (*.DSN files), though CADconform can do this automatically.
When Microsoft Office 2007 was released, Microsoft introduced a few new technologies:
With Microsoft Office 2010, a few other changes were introduced:
All current versions of Windows from XP to 10 currently only ship with the older 32-bit-only JET drivers. These JET drivers cannot be used to connect to a database using the newer ACCDB file format, and nor are they compatible with 64-bit versions of Microsoft Office. Therefore, to connect to an Access database created with Access 2007 or higher, or to connect to a database from a 64-bit application such as MicroStation Connect Edition, the newer ACE drivers must be installed. These drivers may already be installed via Microsoft Office (2010, 2013, 2016), or installed separately if Office has not been installed.
The ACE drivers are provided for free on Microsoft's website and come in two versions: 32-bit and 64-bit. If you have previously installed Microsoft Office 2007 or higher, then you probably already have the ACE drivers installed. However, you may not have the correct flavour of ACE drivers to work with CADconform based on "bitness" - 32 or 64 bits.
The ACE ODBC drivers are the new drivers provided by Microsoft to connect to Access databases. They can be downloaded for free from Microsoft's website from the following URLs:
Microsoft Access Database Engine 2007 (32-bit only)
Microsoft Access Database Engine 2010
(32 / 64-bit)
Microsoft Access Database Engine 2016
(32 / 64-bit)
The 2016 version is currently (as of Dec 2017) the latest available, and is recommended for the broadest compatibility with all databases.
 |
| The webpage for downloading the Access Database Engine |
Each download will give you a choice of the 32-bit or 64-bit drivers (except 2007 which is 32-bit only). The 64-bit version has the "_X64" suffix.
 |
The Windows Operating System versions from Windows XP through to Windows 10 come in two different types: 32-bit and 64-bit. 64-bit versions of Windows can run 32-bit applications and 32-bit drivers under emulation, however a 32-bit application cannot use a 64-bit driver, and similarly, a 64-bit application cannot use a 32-bit driver. Therefore, the ACE drivers come in both 32-bit and 64-bit versions.
The older JET database did not have this issue, as it was available in 32-bits only. This meant that a native 64-bit application could not use JET. This is why a 64-bit application such as MicroStation Connect Edition requires the 64-bit ACE drivers be installed prior to connecting to an Access database.
The two basic flavours of the ACE drivers are 32-bit and 64-bit. The 32-bit drivers can be installed on any machine, however the 64-bit drivers can only be installed on a 64-bit machine. A 64-bit machine needs to have both a 64-bit CPU and a 64-bit version of Windows installed. However, this is not the only issue. The version that you will need to install will also depend on two other important factors:
If you are running MicroStation 2004, XM or V8i, you will need to install the 32-bit ACE drivers only if your database is in the newer *.accdb file format. Older databases in the *.mdb format will continue to work with the older JET drivers.
If you are running MicroStation Connect Edition, you will need the 64-bit ACE drivers.
If you are running a 32-bit version of Windows, then you must use a 32-bit ACE or JET driver. You will not be able to install MicroStation Connect Edition, as it requires a 64-bit installation of Windows, which in itself requires a 64-bit enabled CPU.
The version of MicroStation is displayed in the main menu of MicroStation under the "Help > About MicroStation". 32-bit versions of MicroStation usually have a version number of 8.11 or lower, and include MicroStation 2004, XM and V8i.
If you are unsure of your MicroStation version and you don't have a menubar then it is likely you are running MicroStation Connect Edition. If you have a ribbon instead of a menubar, then try clicking "File > Help > About MicroStation" to see the version number. MicroStation Connect Edition has a version number of 10.00 or higher.
 |
| The About MicroStation dialog in MicroStation Connect Edition, with the version highlighted in yellow. |
The version of Windows can be found by hitting the Windows key + "I" at the same time. From there, click "System > About". Under the Processor and RAM information, there should be a "System Type" showing whether the computer is 32 or 64-bits.
 |
| The Windows 10 System Information window, with the "bit-ness" highlighted in yellow. |
The ACE ODBC drivers have a subtely different driver name in the ODBC Control Panel, both in 32-bit and 64-bit versions. If you require the 32-bit ACE drivers (see above), then you can tell if they are already installed by hitting <Windows> + <I> and typing "ODBC" in the search field. This should present the option "Setup ODBC Database Sources (32-bit)", which will open the ODBC Administrator, as shown below:
.png) |
.png) |
| The ODBC Administrator (32-bit) using the older JET driver | The ODBC Administrator (64-bit) using the newer ACE driver |
The older JET drivers will have a Driver Name set to "Microsoft Access Driver (*.mdb)" and the driver file set to "ODBCJT32.DLL".
The newer ACE drivers will have a Driver Name set to "Microsoft Access Driver (*.mdb, *.accdb)" and the driver file set to "ACEODBC.DLL"
If you are on a 64-bit machine and have installed the 32-bit version of Microsoft Office, then you will probably have the wrong version of the ACE drivers. In this case, attempting to install the 64-bit driver will produce an error. Similarly if you attempt to install the 32-bit ACE drivers on a machine with 64-bit Office, then you will also get an error message that will prevent the drivers from installing.
 |
| The warning that appears when installing the 32-bit ACE ODBC drivers on a machine which has Microsoft Office 2013 (64-bit) already installed |
Rather than uninstall Microsoft Office as recommended in the error message, it is better to force the drivers to install using the <QUIET> mode, as explained below. This forces the install to continue without the error message.
Attempting to install the 64-bit ACE driver on a 64-bit Windows machine that already has 32-bit Office preinstalled will result in an error message. This error can be bypassed if the installer is run in <QUIET> mode, however it may result in Microsoft Office attempting a self-repair the next time an Office product is installed. To get around both of these problems, follow these steps:
Edit the registry by holding down the <Windows> key and pressing the <R> key on the keyboard and type "regedit.exe".
Check the 64-bit registry key "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\16.0\Common\FilesPaths". If it does not contain the "mso.dll" registry value (see screenshot below), then you will need to rename or delete the value after installing AccessDatabaseEngine_X64.exe.
Download the AccessDatabaseEngine_X64 from Microsoft's website to a local directory, e.g. "C:\temp\".
Open a DOS command prompt by holding down the <Windows> key and pressing the <R> key on the keyboard and type "cmd.exe".
Note that the "mso.dll" registry value will already be present if a 64-bit version of MS Office is installed. In this case the value should not be deleted or renamed.
File DSNs are text files that tell Windows how to connect to an ODBC-compliant database. They contain the database settings that include where to find the database itself, as well as which database drivers to use. CADconform v8.01 onwards can automatically adjust the File DSNs if they are defined with the wrong driver details. This will be checked when a database is first attached, and the File DSN will be modified accordingly.
You can check whether your existing File DSN is set to use the older JET or the newer ACE driver by opening the File DSN using a text editor such as Notepad. The File DSNs used by CADconform are located on the server, pointed to by the MicroStation configuration variable: $(_CADCONFORM_FILEDSN_DIR). In a default installation of the CADconform server path, this will expand to the folder:
C:\ProgramData\Altiva Software\CADconform\File DSN\Feature Tables\*.dsn
The only difference between a File DSN configured to use the JET driver and the ACE driver is in the key-value pair named "DRIVER".
Example of a File DSN using the older JET driver:
DRIVER=Microsoft Access Driver (*.mdb)
Example of a File DSN using the newer ACE driver:
DRIVER=Microsoft Access Driver (*.mdb, *.accdb)
Note that the newer driver explicitly states support for the newer ACCDB file extension format.
To use this new ACE ODBC driver, existing File Data Source Names (DSN) can be manually modified to use the newer ACE driver instead of the older JET driver, or performed automatically by CADconform. Details to do this are provided below.
There are 3 main methods of updating your existing File DSNs:
 |
| The CADconform prompt to
automatically update an existing File DSN from using Jet to ACE
drivers. |
This section will step through creating a File DSN for an Access file format CADconform standards database (for SQL Server Express File DSN creation please see the Using SQL Server Express section).
Go to the Windows Control Panel and open "Administrative Tools"
Under the "Adminstrative Tools" choose the "Data Sources (ODBC)" to open the Microsoft ODBC Administrator
Select the "File DSN" tab and click the "Add" button
Choose the "Microsoft Access Driver (*.mdb, *.accdb)" option from the list and click "Next"
Select "Browse" and create a new File DSN in the CADconform server directory (typically this will be named the same as the CADconform standards database). "Save" the new file and hit "Next"
The default CADconform install path to the File DSN folder is "C:\Program Files\Altiva Software\CADconform\File DSN\"
When you click on "Finish" another dialog will open that is used to select the database this File DSN will connect to
Choose the "Select" button and browse to choose the database this File DSN will connect to
The default CADconform install path to the database folder is "C:\Program Files\Altiva Software\CADconform\Database\"
Hit "OK" to finish the File DSN creation and "OK" on the Microsoft ODBC Administrator if you are finished creating new File DSNs
Load MicroStation and select your new File DSN from the "Data Source" drop down list on the CADconform login dialog to start using your newly configured CADconform standards database
If a recent version of Microsoft Office (2007-2016+) has been installed, then some of the ACE ODBC drivers will have been installed already. However, this may prove problematic depending on both your version of Windows and the version of Microsoft Office.
A 64 bit install of Office 2010, Office 2013, or Office 2016 should already have all the appropriate drivers for connecting to a Microsoft Access file format CADconform standards database. If you are experiencing problems connnecting you may need to create a new File DSN for your database. Please follow the steps outlined in the Creating a new File DSN section below.
Typically, the 64 bit Access drivers will not be available on your machine when you are running Office 2016 (32 bit). Microsoft has issued a 2010 Access Database Engine 64 bit installer that will add these drivers. The following steps will show you how to install the 64 bit Access drivers so you can use a Microsoft Access file format CADconform standards database.
Download the AccessDatabaseEngine_X64 installer. This can be found on the Microsoft site.We recommend saving/copying this installer to C:\ drive so that it is easier to find in step 2
You will need to run this installer in quiet mode to avoid uninstalling and reinstalling Office 2016. To do this open the DOS command prompt by selecting START > RUN, typing CMD and hitting OK
Browse to the folder where you saved the installer (if saved to C:\ drive simply type "CD\" and hit enter)
Type "AccessDatabaseEngine_X64 /Q" to run the installer (the /Q tells the installer to run in quiet mode)
The 64 bit Access drivers will now be available on your machine. You will need to create a File DSN to connect to your Access file format CADconform standards database. Please see the steps in Creating a new File DSN.
Typically, the 64 bit Access drivers will not be available on your machine when you are running Office 2013 (32 bit). Microsoft has issued a 2016 Access Database Engine 64 bit installer that will add these drivers. The following steps will show you how to install the 64 bit Access drivers so you can use a Microsoft Access file format CADconform standards database.
Download the AccessDatabaseEngine_X64 installer. This can be found on the Microsoft site. We recommend saving/copying this installer to C:\ drive so that it is easier to find in step 2
You will need to run this installer in quiet mode to avoid uninstalling and reinstalling Office 2013. To do this open the DOS command prompt by selecting START > RUN, typing CMD and hitting OK
Browse to the folder where you saved the installer (if saved to C:\ drive simply type "CD\" and hit enter)
Type "AccessDatabaseEngine_X64 /Q" to run the installer (the /Q tells the installer to run in quiet mode)
The 64 bit Access drivers will now be available on your machine. You will need to create a File DSN to connect to your Access file format CADconform standards database. Please see the steps in Creating a new File DSN.
Typically, the 64 bit Access drivers will not be available on your machine when you are running Office 2010 (32 bit). Microsoft has issued a 2016 Access Database Engine 64 bit installer that will add these drivers. The following steps will show you how to install the 64 bit Access drivers so you can use a Microsoft Access file format CADconform standards database.
Download the AccessDatabaseEngine_X64 installer. This can be found on the Microsoft site. We recommend saving/copying this installer to C:\ drive so that it is easier to find in step 2
You will need to run this installer in quiet mode to avoid uninstalling and reinstalling Office 2010. To do this open the DOS command prompt by selecting START > RUN, typing CMD and hitting OK
Browse to the folder where you saved the installer (if saved to C:\ drive simply type "CD\" and hit enter)
Type "AccessDatabaseEngine_X64 /Q" to run the installer (the /Q tells the installer to run in quiet mode)
The 64 bit Access drivers will now be available on your machine. You will need to create a File DSN to connect to your Access file format CADconform standards database. Please see the steps in Creating a new File DSN.
Office 2007 is a 32 bit application. This means that the 64 bit Access drivers will not be available on your machine. Microsoft has issued a 2010 Access Database Engine 64 bit installer that will add these drivers. The following steps will show you how to install the 64 bit Access drivers so you can use a Microsoft Access file format CADconform standards database.
Download the AccessDatabaseEngine_X64 installer. This can be found on the Microsoft site.We recommend saving/copying this installer to C:\ drive so that it is easier to find in step 2
You will need to run this installer in quiet mode to avoid uninstalling and reinstalling Office 2007. To do this open the DOS command prompt by selecting START > RUN, typing CMD and hitting OK
Browse to the folder where you saved the installer (if saved to C:\ drive simply type "CD\" and hit enter)
Type "AccessDatabaseEngine_X64 /Q" to run the installer (the /Q tells the installer to run in quiet mode)
The 64 bit Access drivers will now be available on your machine. You will need to create a File DSN to connect to your Access file format CADconform standards database. Please see the steps in Creating a new File DSN.
SQL Server Express is an option for users who are unable to, or do not wish to install the appropriate drivers for connecting to a Microsoft Access file format CADconform standards database. If you are working with standards delivered by a third party (i.e. following a client's standard that is issued to you) and the the delivered standard uses a Microsoft Access file format CADconform standards database keep in mind that when the standard is updated you will need to reimport these changes into SQL Server Express to stay current (Access is typically the prefered option in these situations as the MDB/ACCDB file can simply be replaced when new standards are issued).
The following steps will help you install and set up SQL Server Express on your local machine to use with a standalone installation of CADconform:
Install SQL Server Express
Download the 64 bit SQL Server Express installer. The current version at creation of this guide is SQL Server 2008 R2 Express and can be found on the Microsoft site.
Run this installer leaving all of the default settings. (During the "Instance Configuration" step, make note of what the "Named instance" is set to, this will be required later during the database setup. By default this should be "SQLExpress")
Import Access format CAD standards file into SQL Server Express
SQL Server 2008 R2 Express has an "Import and Export Data" tool to import data from a Microsoft Access file format database. I have found it more reliable however to open the database in Microsoft Access and use the "SQL Server" option in the "Move Data" panel of the "Database Tools" ribbon.
Open Access file format database in Microsoft Access
Select the "Database Tools" ribbon and choose the "SQL Server" option from the "Move Data" panel
The "Upsizing Wizard" will open and you will choose the "Create new database" option.
On the next page you will choose your "Server" from the drop-down list and select the option to "Use Trusted Connection".
Note: In most cases the wizard will only show the current computer name as an option, or nothing at all. You will need to enter your computer name and then add the "Named instance" you noted during the installation here (by default your server would read "<COMPUTERNAME>\SQLEXPRESS"). In some configurations you can simply use either "(local)" or the computer name as the name of the Server.
The wizard will now prompt you to choose which tables you want to copy to the SQL Server database and simply choose them all and hit the "Next" button..
The table attributes page should be left with the default properties (all of the table attributes checked on and "Use DRI" toggled. For the data options "Yes, let wizard decide" selected and "Only create table structure" unchecked)
The next page shows applications changes options and can also be left at the default settings ("Link SQL Server tables to existing application" selected)
The wizard now has all the information it needs and will import the data when you click on "Finish"
Once the upsizing is complete you will need to create a File DSN to allow CADconform to access the database. Please follow the steps in the next section "Create File DSN for a SQL Server Express standards database"
Create File DSN for a SQL Server Express standards database
Go to the Windows Control Panel and open "Administrative Tools"
Under the "Adminstrative Tools" choose the "Data Sources (ODBC)" to open the Microsoft ODBC Administrator
Select the "File DSN" tab and click the "Add" button
Choose the "SQL Server Native Client..." option from the list and click "Next"
Select "Browse" and create a new File DSN in the CADconform server directory (typically this will be named the same as the CADconform standards database). "Save" the new file and hit "Next"
The default CADconform install path to the File DSN folder is "C:\Program Files\Altiva Software\CADconform\File DSN\"When you click on "Finish" another dialog will open to step through the SQL Server side of the process
Choose your "Server" from the drop-down list.
Note: As before, in most cases the wizard will only show the current computer name as an option, or nothing at all. You will need to enter your computer name and then add the "Named instance" you noted during the installation here (by default your server would read "<COMPUTERNAME>\SQLEXPRESS"). In some configurations you can simply use either "(local)" or the computer name as the name of the Server.
Hit "Next" and leave the default option of using "Integrated Windows Authentication"
Toggle on "Change the default database to:" and select your newly imported database. Leave other options as defaults
On the final page of the wizard leave the options as defaults and click "Finish" to complete the File DSN creation
Hit "OK" to close the summary dialog and "OK" on the Microsoft ODBC Administrator if you are finished creating new File DSNs
Load MicroStation and select your new File DSN from the "Data Source" drop down list on the CADconform login dialog to start using your newly configured CADconform standards database
Copyright 2000-2018 Altiva Software, Inc. Last modified by Piers Porter.