none
DAO 3.6 in Access 2010 64 Bit? RRS feed

  • Question

  • Hi,
       I have installed Office 2010 64 bit on our Windows Server 2008 for migrating a 2007 32 bit Access Project (.adp). I cannot seem to find the reference for the Microsoft DAO 3.6 Object Library. Any thoughts of why this has not installed on the first place?

    Thanks

    Friday, July 23, 2010 2:21 PM

Answers

  • After research, Office 2010 simply does not support DAO 3.6. There are plenty of articles on the web that discuss registration of DAO 3.6 for 2010 32-Bit. There is no articles that discuss a future release of 64 Bit DAO. The code I have been carried over from past years. The conversion was past due since Office 2003 maybe. The best solution I found is to invest time into converting the outdated code to 64 bit ADO. Some areas were fairly new experiences and some were straight forward.

    Good Luck

    • Marked as answer by DishDash Tuesday, July 27, 2010 5:01 PM
    Tuesday, July 27, 2010 5:01 PM

All replies

  • After research, Office 2010 simply does not support DAO 3.6. There are plenty of articles on the web that discuss registration of DAO 3.6 for 2010 32-Bit. There is no articles that discuss a future release of 64 Bit DAO. The code I have been carried over from past years. The conversion was past due since Office 2003 maybe. The best solution I found is to invest time into converting the outdated code to 64 bit ADO. Some areas were fairly new experiences and some were straight forward.

    Good Luck

    • Marked as answer by DishDash Tuesday, July 27, 2010 5:01 PM
    Tuesday, July 27, 2010 5:01 PM
  • Did you try using the

     

    Microsoft Office 14.0 Access Database Engine object

     

    For access 2010, the standard DAO reference is the above and NOT dao 3.6.

     

    I not tested this with 64 bit edition. Addition that ADP does confuse this issue even more.

    The above reference should be available and that is the correct DAO reference to use/try in your case.

     

    The fact that you trying to use/run DAO code inside of ADP which tends to be ADO is certainly going to be a source of problems. However, I would give the above reference a try.

     

    Albert D. Kallal  (Access MVP)

    Edmonton, Alberta Canada

    Tuesday, July 27, 2010 8:07 PM
  • as a follow up:

    JET direct has been depreciated, but the ACE engine is for all purposes the default. There would be no need to use DAO 2.0 or DAO 3.6.  The NEW DAO default for 2010 is ACE.

     

    Again: The default is DAO for Access 2010. However, that default is certainly not some older version of DAO, be it 2, or 3.6.

     

    Virtually ALL OF my current DAO code works 100% fine in access 2010. So, the only reason I can think that you need/want DAO 3.6 is for needing JET direct, and given that your talking about a ADP, this very well might be the case (but, I can't read your mind, so you have to clear up if in fact you are needing to use JET direct in your DAO code, or you just need to write/use DAO code).

     

    AGAIN:

    THE NEW DAO DEFAULT AND REFERENCE FOR ACCESS 2010 IS CALLED

     

         Microsoft Office 14.0 Access database engine Object Library

     

    AGAIN:

    If you set the above reference in a APP then you CAN USE dao reocrdsets and dao code will run and work just fine.

    AGAIN:

    You can use the above refernece in an ADP and it works just fine.

    So, if you are looking to set a DAO reference in Access 2010, use the above. The above will work for a 32 bit ADP access 2010, and I suspect it should work just find for the 64 bit ADP access 2010 also.

     

    • Proposed as answer by jsolebcn Friday, July 25, 2014 8:18 AM
    Tuesday, July 27, 2010 10:50 PM
  • Another option is to simply install the 32-bit version of Office on your server. Believe it or not, but that is actually the default installation.
    -Tom. Microsoft Access MVP
    Wednesday, July 28, 2010 3:26 AM
  • And recommended by Microsoft.


    A camel is a horse designed by a committee.
    Monday, January 31, 2011 12:22 AM
  • DAO 3.6 is avialable in 2010 64-bit but in MDB format. I'm not sure it avialable in ADP as I've not tested it out but it should be in.

    If you attempt to remove "Microsoft office 14.0 Access Database engine object libarary" from ACCDB format, it would not allow you and DAO 3.6 is not supported. And it won't allow you to add it in.

    ACE is the new version of DAO, so just use it and it is the default engine.

    Monday, January 31, 2011 1:45 AM
  • ADP has a superior technology, you know.. ADO- the technology embraced by millions of people who write Active Server Pages?
    Saturday, June 4, 2011 3:05 PM
  • Aaron Kempf MCITP DBA wrote:

    ADP has a superior technology, you know..

    ADPs haven't been enhanced since they were first introduced.

    Tony


    Tony Toews, Microsoft Access MVP
    Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
    Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
    For a convenient utility to keep your users FEs and other files   updated see http://www.autofeupdater.com/

    Monday, June 6, 2011 2:45 AM
  • >> ACE is the new version of DAO, so just use it and it is the default engine <<

    DAO is NOT a database engine, its an API that we use to interact with the Jet database engine.

    ACE (Access Connectivity Engine) is the Jet database engines successor and is indeed the database engine used by ACCDB/E files.  The Microsoft Access database engine Object Library (I call it AdeOL) API is the successor to Microsoft DAO 3.6 Object Library API.

    I think it is very important not to confuse an API with the actual database engine being interfaced with.  Interacting with the Jet/ACE database engines is not limited to the DAO/AdeOL API's respectively.  For example you can use ADO with the Microsoft.ACE.OLEDB.1x.x provider.


    Brent Spaulding | Access MVP
    Thursday, June 30, 2011 3:04 PM
  • Yes, good point – 100% agree.

    I really meant to say:

    You need to reference the ACE object library to use the new version of DAO. So, just use it and ACE is the now the default data engine.

    My real point here is that when using accDB format files, you do not actually set a reference to DAO anymore, but set a reference to the ACE object library. In VBA code, you still dim things such as

     Dim db  as DAO.database
     Dim rst  as DAO.Recordset


    Albert D. Kallal  (Access MVP)
    Edmonton, Alberta Canada

     

    Thursday, June 30, 2011 3:51 PM
  • Yes, good point – 100% agree.

    I really meant to say:

    You need to reference the ACE object library to use the new version of DAO. So, just use it and ACE is the now the default data engine.

    My real point here is that when using accDB format files, you do not actually set a reference to DAO anymore, but set a reference to the ACE object library. In VBA code, you still dim things such as

     Dim db  as DAO.database
     Dim rst  as DAO.Recordset


    Albert D. Kallal  (Access MVP)
    Edmonton, Alberta Canada

    Yes, that's what I do when I convert it to accdb or import an object from a mdb. I didn't need to remove DAO reference in every modules, reports and forms. And it works in 64-bit Access as well. I also have ADO references, so I would prefered to remain the status quo.
    Friday, July 1, 2011 1:21 AM
  • I am developing .accdb's on Access 2010 64-bit, and have no trouble finding Microsoft DAO Reference. It appears to be installed on my machine my default. Perhaps it's there because of some 32-bit software I have installed on my particular machine? Who knows but it works for me!

    [edit] p.s. I'm adding references by GUID.

     

    Application.References.AddFromGuid "{4AC9E1DA-5BAD-4AC7-86E3-24F4CDCECA28}"

     


    Matthew Slyman M.A. (Camb.)
    • Edited by Matthew Slyman Saturday, July 2, 2011 7:35 AM Accuracy and completion
    Friday, July 1, 2011 8:51 PM
  • @Moderators:

    This is simply wrong. MS Office 2010 incl. 64-bit does support DAO 3.6 - I'm using this without problems.

    The VBA Reference GUID changed from older versions:

    was : "{00025E01-0000-0000-C000-000000000046}"

    now : "{4AC9E1DA-5BAD-4AC7-86E3-24F4CDCECA28}"

    Try adding it... It should work fine.


    Matthew Slyman M.A. (Camb.)
    Saturday, July 2, 2011 7:13 AM
  • @Moderators:

    This is simply wrong. MS Office 2010 incl. 64-bit does support DAO 3.6 - I'm using this without problems.

    The VBA Reference GUID changed from older versions:

    was : "{00025E01-0000-0000-C000-000000000046}"

     

    now : "{4AC9E1DA-5BAD-4AC7-86E3-24F4CDCECA28}"

    Try adding it... It should work fine.

     


    Matthew Slyman M.A. (Camb.)

    Interesting, I haven't tried that out yet. Not sure it's a good idea with 64-bit Access because DAO 3.6 is 32-bit. Will not work? Well I guess time will tell.

    PS. Access 64-bit won't let me at DAO 3.6 and I can't remove ACE.


    Monday, July 4, 2011 1:49 AM
  • Correction: Access 2010 64-bit DOES support DAO. (Not sure about v. 3.6 but DAO is supported - I'm using DAO with the Reference GUID mentioned in my previous post.) Try adding the Reference by GUID.

    As for DAO being 32-bit code, I don't know one way or the other. But I do know that Access generally can only handle 32-bit numeric db fields. (64-bit VBA code works fine, but any 64-bit numeric data types that Access touches get implicitly converted into Text Strings. So you can interact with a 64-bit SQL database, only, you have to enclose your 64-bit numeric data in quotation marks, so as to trick Access into being compatible with this data type, and sending those data over to the SQL database that will treat the 64-bit quoted numeric type as a numeric type.)


    Matthew Slyman M.A. (Camb.)
    Monday, July 4, 2011 10:45 AM
  • Yes, there is 64 bit edition of DAO.

    The issue of sql server is NOT related to ths issue.

    When you use the 32 bit version of SQL server, or the 64 bit version of SQL server, it does not magically out of the blue and based on a whim all of a sudden change the data formats of the data word size being saved.  The way the data format is stored does not change when you use a 32 bit version of SQL server, or the 64 bit version.

    It would be pretty amazing and silly to mess with the data formats; else any 32 bit or 64 bit client, or any 32bit or 64 bit versions of SQL server would rapidly turn the whole computer industry into a tower of Babylon.

    The same goes for access when using the 64 bit edition, or the 32 bit edition, the format of DATA saved does not change.  However, to be clear; there is a REAL 64 bit edition of the database engine when using Access 64.

    Many programming languages when they made the jump from 16 to 32, didn't necessarily mess with the existing variable and word size (they added new ones as to allow existing code to work).  So the change from 32 to 64 bits does not mean nor imply that the database file format is going to change in any way shape or matter.  In other words the database file formats, and the programming language and word size are two different matters.  Now I will accept that one should make the assumption that the programming language as a general rule should be able to accept and manipulate the data and word size that comes out of the database system, but the reverse is not always the true either. In fact, it not true either way.

    So for access 64 bit version, the long variable type is still only 32 bits. Int variable type is still only 16 bits. However when you are using the 64 bit version of access, then the VBA language does offer NEW variable types. You have LongLong and this is a true 64 bit wide word size. However you do not have a change or equivalent of LongLong in the actual table designer, nor does the data format change nor has been a 64 bit word size added to the data file format.  So just like the 32 bit or 64 bit eddition of SQL server, the data file formats and word sizes for data saved does not change in Access data files either. 

    On the other hand the 64 bit edition a SQL server can address vast amounts of more memory directly in the computer, and the same would apply to access 64 bit version.

    Also keep in mind if you're using the 64 bit version of access, then you can only automate "in process" other 64 bit programs. So if you have the 32 bit version of outlook installed you WILL NOT be able to automate that version of outlook from access 64.

    And to save some posters coming back here to clarify this issue. You cannot mix and match 32 and 64 bit parts of office for the SAME version. So for office 2010 if you use 64 bit version of outlook, you'll have to use the 64 bit version of word.

    You CAN however install the 32 bit version of office from OTHER versions such as 2007. The reason for this is office is made of shared components, and if you install word on a machine, than any other part of office installed including Access will not even give you a choice as to what directory it's going to install into. The shared parts are many ranging from spell checking,  graphics filters, VBA editor and a whole slew of components are shared to all of office. Thus no mixing and matching of the same version of office is allowed in terms of word size (you cannot mix/match 32 and 64 for same version of office). And, this means that Access 32 cannot automatate Outlook 64 bit either.

    Now back to access 64 bit; You also have a new pointer variable type again that is 64 bits wide and this allows you to use the windows API (as once again with true 64 bit computing, you need a larger value than 32 bits to address memory and use the windows API). That new variable type is called longPtr (note that this variable is also available in the 32 bit version of access, and when you use the 32 bit version of access it is 32 bits long).  However the same cannot be said for the new variable type called LongLong, it is ALWAYS 64 bits and in fact is not available in the 32 bit version of access.

    And there are about 6-7 new functions to convert the 64 bit variable size into strings or integers or whatever – just like we had in the past - (clng, cdbl etc.).

    So the 64 bit of version of access is a true 64 bit in process version of access, and just like SQL server 32 or 64, this fact is not reflected in the data file formats used nor the CPU processor data word size used with data that you save into those data files and  formats.

    Albert D. Kallal  (Access MVP)
    Edmonton, Alberta Canada

    Monday, July 4, 2011 4:15 PM
  • I would agree if Access 2010 32-bit support DAO 3.6 32-bit. That's the way at the moment at my work place, I'm suggesting Access 2010 32-bit with Windows 7 32-bit instead of fully 64-bit in my company. So there's no need to convert anything especially like the APIs. I guess it is hard to find out why MS exclude DAO in 64-bit Access by default.

    The only problem I encounter is the VBA date format. Was running Access 2000 with Win 7 32-bit. It is showing a US date format instead of UK. That's normal in Access, but this works in Win XP with the Regional Settings(control panel) if it was set to UK. Win xp works fine but not in Win 7.

    I really hate to convert anything, wish MS make it more simple where you can just run your old 32-bit mdb without any conversion.

    Tuesday, July 5, 2011 1:43 AM
  • @Albert,

    You haven't taught me anything new in your essay, except to clarify a few things I'd already found out in practice about installation restrictions on different versions - a useful clarification for me.

    As for 64-bit data types...

    http://www.slyman.org/blog/2010/11/64-bit-windows-7office-2010-migration-experiences/

    I was writing about that on my blog eight months ago. Most of what you have written is accurate (although simultaneously irrelevant to the OP, excessively verbose and insufficiently detailed on a technical level regarding the subjects you are now discussing), although a few of your suggestions just leave me scratching my head.

    Have you tried the SQL Server BigInt data type? 64-bit numeric. Have you tried interfacing it via ODBC linked table from MS Access? Check out the data type according to MS Access table design view. "String". No, it's not. It's a 64-bit numeric type, that MS Access 64-bit doesn't understand. Have you tried creating a 3GB MS Access 64-bit .accdb file? Won't happen, at least not on the 2010 version.

    As for the rest, check out the signature on my MSDN posts, and you'll see that for me, most of that extra detail is totally unnecessary. If your post was shorter, I might vote it "helpful", but with posts of this length, you really have to dig to find what you're looking for... Just a helpful suggestion to shorten your posts a little? I think your more valuable contributions would get a lot more exposure.

    Matthew.


    Matthew Slyman M.A. (Camb.)
    Tuesday, July 5, 2011 6:42 AM
  • Until converted for human display, Date format fields/ variables are manipulated internally in Access using a standardised numeric representation (at least, internally standardised within MS Access/ VBA, I believe - with some possible conversion at the seams if you're interfacing with another RDBMS such as SQL Server). There's no US or UK date format, until you start looking at the values (at which point, there is an implicit type conversion to Text/String, to allow you to read the Date value more easily).

    If you're having trouble with date formats, I suggest:

    • If you're interfacing with another RDBMS such as SQL Server, check the date formats used by SQL Server and your ODBC drivers are what your client workstations expect.
    • In any case, you can explicitly use the Format() function to ensure that dates are displayed in the format you desire.

    Other than that, you might find this information helpful (about VBA, Windows API and locale settings):

    http://vbnet.mvps.org/index.html?code/locale/localedates.htm

    http://msdn.microsoft.com/en-us/library/dd318101%28VS.85%29.aspx

    http://msdn.microsoft.com/en-us/library/dd464799%28v=VS.85%29.aspx


    Matthew Slyman M.A. (Camb.)
    Tuesday, July 5, 2011 7:15 AM
  • Have you tried the SQL Server BigInt data type? 64-bit numeric. Have you tried interfacing it via ODBC linked table from MS Access? Check out the data type according to MS Access table design view. "String". No, it's not. It's a 64-bit numeric type, that MS Access 64-bit doesn't understand. Have you tried creating a 3GB MS Access 64-bit .accdb file? Won't happen, at least not on the 2010 version.

    Matthew.


    Matthew Slyman M.A. (Camb.)


    The above is really very much my whole point.

    For what reason would you assume that the file size is going to change because we moved to 64 bits for the data engine? My whole simple point is that just because you change the bits size of the data engine, that is not necessarily going to affect the file and data type size limits that you're allowed to work with.

    (it could but it is NOT necessary so and it is a poor assumption - these are mutually exclusive issues – ie: limits will *necessary* be increased). 

    If you look at SQL server 32 bit, or 64 bit edition, the max data size file allowed is 542 terabytes.  (I suspect this has something to do with the windows file limits, and not the database engine itself, but my point still remains intact). And what new data types did we get with 64 bit sql server?

    Some more SQL stuff:
    Number of columns per insert statement in 32 bits = 4096
    Number of columns per insert statement in 64 bits = 4096

    columns per primary key in 32bit = 16
    columns per primary key in 64bit = 16

    The list of NON changes for 32 vs 64 bit sql server is very long. A lot of these limits in particular thus did not change when moving from 32 to 64 for SQL server. So, if this is the case for SQL server, then why point out that somehow Access would be different in this regards? (it is not).

    You seem to be suggesting in some way that because we gone to a 64 bit edition of Access, then somehow the file size will necessarily increase (it did not for SQL server).  You're also suggesting that for some reason because we are now on Access 64 bits, all of a sudden the ODBC driver will return something different for bigInts (as number) and not a string as it did before.  Again such behaviors never changed with SQL server when it went from 32 to 64, so why then expect it to change with Access?

    Maybe if you quoted some good examples of what limits and how the drivers changed and WHAT they return when we went from 32 to 64 bit SQL server I might be able to better grasp your point as to why you suggest something different should occur when access went to 64 bits as opposed to SQL server?

    There's really nothing material or logically I can see as to why access would necessarily change these limits any more so then SQL server did.  If you're concluding that some of these limits are arbitrary limits and set the way they are, then I fully accept that. I'd also say the same for SQL server.

    A nice list for SQL server is here, and again going to 64 bits did not change many limits:

    http://msdn.microsoft.com/en-us/library/ms143432.aspx

    Thus based on recent computing history there's no reason to make such assumptions for Access any much different then what occurred for SQL server.


    Albert D. Kallal  (Access MVP)
    Edmonton, Alberta Canada

    Tuesday, July 5, 2011 9:28 AM
  • Matthew Slyman wrote:

    I am developing .accdb's on Access 2010 64-bit, and have no trouble finding the Microsoft DAO 3.6 Object Library. It appears to be installed on my machine my default. Perhaps it's there because of some 32-bit software I have installed on my particular machine? Who knows but it works for me!

    Jet 4.0, DAO 3.6 and MDAC/ADO have been installed as part of the OS
    since Windows 2000.

    Tony


    Tony Toews, Microsoft Access MVP
    Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
    Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
    For a convenient utility to keep your users FEs and other files   updated see http://www.autofeupdater.com/

    Wednesday, July 6, 2011 12:12 AM
  • Dear all,

    I am just a layman in computing. I want to use my old Access program with DAO3.6 codes in a system of Windows 7 64bit. Could you suggest me to stall which version of MS Access 2010, 64bit or 32bit? How to set Access 2010 to run the DAO3.6 codes. Thanks a lot.

    Monday, August 22, 2011 2:48 AM
  • The default installation for Office 2010 is the 32bit version.  I have installed the 64bit version personally to reflect what my employer has done, but likely would have installed 64bit anyway.  64bit is definately here to stay and knowing how to deal with situations in that environment was important to me. Its a catch 22 really because I want to be using the "lastest" thing, but going to Access 64bit, has some growing pains, especially if you will be in a mixed environment or depended on ActiveX COM Controls (ie: TreeView).  To handle that (the mixed mode) I have created Virtual Machine's that have the environments I needed, but definately wanted my "base" installation to be 64bit.
    Brent Spaulding | Access MVP
    Monday, August 22, 2011 3:04 PM
  • Many thanks for all prompt reply
    Wednesday, August 24, 2011 3:33 AM
  • I had similar problems using office 2010 64bits + Windows 7 prof. 64bits. 

    Resolved simply installing 32bits version of office 2010 and I got no further problems.

    Sunday, April 15, 2012 5:14 PM