0

Microsoft Access 64-bit driver

Geoff Elson 8 years ago in General updated by Scott P (Moderator (EN)) 5 years ago 14
Image 830
Does anyone know the fix for this. It this something that is downloaded from Microsoft?
Hi Geoff,

You can get the drivers but it creates a whole lot of headaches in a whole lot of areas. I run a 64bit system and had to install 64bit office for one of the other programs I use that painfully will only let you install their 64bit version on a 64bit machine and not operate unless 64bit office is installed. Because Microsoft will not allow 32bit and 62bit office to co-exist you must pick one or the other (in this case it was picked for me). You can download the access drivers (at least the 32bit ones, and I think the 64bit also) that can be installed alongside the opposite component but it does cause some issues and you have to manually re-install every time you install an upgrade/patch to your Office software as they will be automatically un-installed. My Mapinfo for instance no will longer read Microsoft excel or access data because of a glitch in an upgrade for office and I am having no end of issues getting the drivers to re-install. My suggestion is to follow the error - use the 32bit Micromine to run the import/connection once that's done both programs can access the data, the two operate well together and I have never had a problem. While I generally operate in the 64bit version I find that for 95% of the tasks I do the 32bit is fine so if I need to import something I just fire up the 32bit and run in that for the day. The only issue you might face is if you are using the older access based drillhole databases, however these are easily upgraded to the SQL format using the Drillhole-Database-Convert menu item (but older Micromine versions will no longer be able to read/write/use to these).
Gents,

Unless space is an issue I'd simply install 32-bit and 64-bit Micromine together. Then you can use the 32-bit version whenever a compatibility problem like this crops up, and switch back to 64-bit once the data is imported.

Alternatively, the Micromine online help contains suggestions and recommendations for dealing with cross-architecture conflicts.

Just open the help window, switch to the Search tab, search for "drivers", and open the topic called "Appendix: Choosing drivers for Microsoft Access compatibility". It should be the second item in the list.

This link includes strategies for installing both 32- and 64-bit Access drivers on the one machine.

Cheers,

Frank

Has there been any developments on this issue?  Has anyone found any 64-bit ODBC Access drivers that work?

I am currently running 32-bit MM to update/refresh database, then switching to 64-bit MM.

  

However, the latest release is the final 32-bit supported version of MM.

I've been reading 64 bit Access 2016 tables straight into Micromine (import) but haven't tried using Access as an ODBC data source.

I'm with Frank.  I retain an old 32 bit MM 2014 to import using 32 bit driver and then switch to work in 64 bit MM.  I guess not useful if you're wanting to maintain a live ODBC link though.

ODBC or MSAccess link.....both fail miserably. I (my IT guy) got it working using a MariaDB driver and some other black magic, it wasn't as easy as just installing the driver as the 'Help' suggests. But since the last update its dropped out.  I actually had some work to do so ended up linking my Access tables - excel workbook -ODBC - MM so at least I can refresh when new data comes in...

I have two methods I use to get data from Access into Micromine, both use other Microsoft tools as staging posts.

Using the External Data option in Access you can export a table to Excel which is then easy to import into Micromine.  The other option is to use the External Data | More | ODBC Database  option and drop the data into a table in a SQL DB then just link to that DB in Micromine.  Both methods allow you to save the steps so that you do not have to specify all the steps each time.  Of course if you want to use Access as your DB interface you can always store the data in SQL link it to Access and then also link Micromine to SQL server (or of course use Micromine's Geobank and say good bye to Access).  SQL Express which will do everything most users are likely to want if they are not running a large corporate system is free.

Used to be simple to maintain a link from Micromine to ACCESS, now it doesn't work without a whole lot of hair pulling and maybe never.  Might as well get rid of File|LInk|Microsoft ACCESS as far as I'm concerned. Should be called Microsoft Abcess anyway--garbage software.

Hi All,

It is possible to provide access support in 64-bit Micromine provided you install the right drivers and architecture. This one-page document will help you to choose the right combination.

+1


Hi all,

There is a way, as Frank and Keith have noted - building on their notes, here are all the steps that I use. NOTE: although this works for me and many others that I have setup it up for but use caution to the point of being prepared to uninstall/reinstall MSOffice or Micromine, or even a machine rebuild. That said I and others have been using this method with no issues from MM 2016 through to current MM 2018.

This method assumes you have MSAccess 32 bit (MSOffice Professional or Office 365) as the source database, either as an ACCDB or MDB file. And you are running Micormine 64 bit  on Windows10 64 bit.

The method will result in a link between MM (2016 or 2018) and a 32 bit MSAccess database (MDB or ACCDB) :

  1. check if you already have 64bit ODBC for MSAccess. To check to see if your local machine already has 64 bit ODBC Access driver open/run the ODBC Data Source (64- bit ) app:


  • if you do have the 64 bit ODBC Microsoft Access Driver then skip to next step. Otherwise you will need to install MSAccess 64 bit runtime. Frank provided the link as https://www.microsoft.com/en-gb/download/details.aspx?id=13255 (be sure to download the x64 one, not 32 one).

    Each user machine will need this installed.
  • Once downloaded run the executable from the command prompt in passive mode. This has to be from a real hard drive (i.e. not a network drive):


  • once installed double click on the ODBC Data Source (64- bit ) app:


  • then add a 64 bit System DSN by:
    1. press ‘Add’ on the System DSN tab.
    2. double click on ‘Microsoft Access Driver (*.mdb, *.accdb)’.
    3. Enter the name and description for this database. (You’ll need to add a new data source link for each database you want to use. )
    4. navigate to location of the MSAccess db and select it. Press ok couple times to save.



    5. this creates a system (i.e. user level, not server level or file level) alias data source link in your ODBC.ini file in your 64bit Windows system folder. Essentially it is a 64 bit driver that is allowing the system to link to a 32 bit data source, thus masquerading it as a 64 bit source

  • Start up MM 64 bit:
    1. to link that new ODBC data source you setup , use either the ODBC link or import; both work fine, I tend to prefer the link:



    2. select a table from the DB and give it a target name and link/import.



    3. if you are importing/linking a event or interval table then use a SQL statement to sort it as it imports:




  • obviously, you can setup a macro to refresh the above link or import process.
  • for those of you using the QGIS 64 bit, the above also works for that too

Microsoft have actually (intentionally) made it quite hard to use Access as a database from a 3rd party application. They appear to not be installing the drivers by default (at least for Office 365), and make it hard to install both 32bit and 64bit drivers side by side.  It seems that their ultimate intent is to force people toward a proper database; SQL Express, SQL Server etc

I am an Office 365 user and Access is one of my main applications.  I have noticed that, while the Office 365 versions of Excel and Word are steadily getting new features and an updated look with new icons etc., Access hasn't received significant updates in a long time.  That would be congruent with Scott's observation that MS is trying to push people away from Access.

If they're really trying to deprecate Access that's too bad.  I completely agree that one should use a proper SQL database, if a database is what one needs.  I don't use Access for storing or archiving data myself, but I find Access to be a great data exploration tool.  I'm not proficient with SQL, but with Access I can do a quick query on-the-fly, just to see "what if".  Once upon a time, I would often make a quick link to Micromine to see what the data in my on-the-fly query look like in Visex, but it has gotten harder and harder to keep that working in recent years.  A few months ago, after spending an inordinate amount of time in Microsoft's labyrinth online help and forums, I had everything working, but then Microsoft did an update and blew my setup away.  Somehow they wiped the necessary drivers off my system.  I have't had the patience to go and hunt down the drivers needed for links since, so I've been going Access-CSV-Micromine, but that is quite tedious.

I have spent a lot of time looking at things like SQLite and other light-weight dbs, trying out the available graphical user interfaces to see if there's something out there that can replace the ease of Access for exploring data, but I have yet to find anything that works as well for me.  In part that's due to familiarity, after a couple of decades of working with Access, but maybe before too long Microsoft will force me to move on.

Peter, If you are deliberate in your choice of the 64bit driver to install to ensure it is not the same as the office version you also have installed (mostly office is 32bit) then there should be no problems with office trashing your driver setup.

Our MM help document tries to make this clear.

I would agree with your first comment Scott, it has become increasing harder to use Access with 3rd party apps - what a person thinks should work, doesn't. Very much a pain. But above should work for those of us faced with needing to link to MSAccess and not have to do too much of a workaround.

I won't get into whether MSAccess is a proper db; that said you are right, being their product MSFT does tend to ''forget' their own child in favour of SQL. But if people are working within a group/company then I agree, much better to go SQL all around. 


I do  quite like MSAccess as a simple DB and is very portable when we are working between clients as opposed to unweidly  SQL ones - about 80% of our contacts give us MSAccess which we prefer over XLS and CSV, The rest give us XLS/CSV. Yes, they could send us a SQL backup, but that is usually in their too hard basket ...

Hopefully this will all go away in near future once more and more companies, MM included, only develop 64 bit apps and drivers and they do away as much as possible with 32 bit.