Coverting Access 2010 32 bit to Access 2016 64bit

you should also do that with recordsets

Dim rs as DAO.Recordset

Thanks for all the suggestions.  I have any other question.  I know the 64 bit is lager, but beside this, what is the different between 32bit and 64bit?  
also when I checked the Microsoft Office 16 Access Database engine  Object Library instead the Microsoft DAO 3.6 Object Library , it gave me another error message on one of my code "rs.recordcount" type mismatch.

If a MDB format, it will need to be 4.0 format. what does that mean?

Thanks for all the suggestions.  I have any other question.  I know the 64 bit is lager, but beside this, what is the different between 32bit and 64bit?

The bitness refers to the size of a register which is what the operating system uses to store addresses.  Therefore, the larger the bit size, the larger the memory space or disk  that an app can reference.  There is absolutely NO advantage for Access to be 64 bit vs 32 bit.  Unlike spreadsheets which load the entire file (data set) into memory, relational database applications such as Access do not load the entire contents of the BE database into memory.  The data stays on disk and is worked on a record at a time and the database is updated immediately to reflect changes.  Memory is the working space on a particular PC and is never shared with other PC's.  Disk storage may be shared but not memory.  That is why Access and other RDBMS' always write changes back to disk so that all users can see the updates.

Typically Access just goes along for the ride since for any version of Office, ALL components must be the same bit version because they share object libraries.  It is possible to install a 32 bit version of Access provided it is a different Office version such as A2010 vs O2016 when you are running a 64 bit version of Office.  However, the 32 bit version of Access would not be able to automate any of the 64 bit Office componits so I would not recommend this.  As I said, if your users want to create humongous spreadsheets, they will insist on 64 bit office and you will need to modify your Access app to go along for the ride.

Until recently, MS recommended installing the 32 bit version of Office and only installing the 64 bit version if you needed to work with expanded spreadsheets.  Switching from 32 bit to 64 bit breaks any addins you created or bought which was one of the primary drawbacks.  The third-party vendors have had plenty of time to recode their addins using 64bit API's so if an addin is still available, there will most likely be a 64 bit version so the tide is shifting.  Companies at some point will have to recode their custom addins for 64 bit and as they do that, they will stop fighting the tide and install the 64 bit version of Office.  We've been using a 64 bit version of Windows since 64 bit hardware has been available and that it is at least a decade.  Possibly longer.  We may be getting ready to have another hardware revolution and go to 128 bit PC's.  At that point, 32 bit will no longer be supported except perhaps in emulation mode as is what happened to 16 bit apps during the last major upgrade.

There is absolutely NO advantage for Access to be 64 bit vs 32 bit.

  Yes and no.  While that's true feature wise, 64 bit does allow for a larger process address space.   So  there may be cases with later versions where you would not get "out of memory" messages where you would with 32 bit.

Jim.

If a MDB format, it will need to be 4.0 format. what does that mean?

  JET (database engine) has had a number for formats over the years.   Access 95/97 was JET 3.5. Starting with Access 2000, JET 4.0 was released.

So depending on how old a MDB file is, you may or may not be able to use it with the new versions of Access (JET 3.5 and prior support was dropped starting with Access 2013).

Jim.

I guess we are not able to use because our version was from access 2003 to access 2007 the to current version access 2010.  i'm just stuck after I checked the Microsoft Office 16 Access Database engine  Object Library instead the Microsoft DAO 3.6 Object Library , it gave me another error message on one of my code "rs.recordcount" saying type mismatch.

Private Sub Form_Current()
    Dim rs As Recordset
   
    Set rs = Me.RecordsetClone
    rs.MoveLast
    Text18 = rs.RecordCount
    NumberofRecords = rs.RecordCount
    rs.Close
   
End Sub

Try:

Dim rs as DAO.Recordset

you have not declared text18 or NumberOfRecors as variables.  If text18 is a control on the form, using "Me." is more efficient and may produce a different error message..  
Me.Text18 = rs.RecordCount

Go back to a PC with A2007 installed and convert the database to .accdb using that version.  Also convert the BE.  Once the databases work correctly in A2007, you will probably just be able to run them with A2016.  No conversion will be necessary.

Several people have suggested disambiguating your Dim statements and Dale has repeated the suggestion.  It avoids confusion and so is always a good idea.  it also looks like you do not have Option Explicit set for all modules.  I would go back to the A2003 version and do that and also take the time to  fix ALL compile errors before converting from A2003 to A2007.

Whenever you are converting from one version to another, you can run into changes in Access that cause failures because earlier versions of Access were sloppy about enforcing certain VBA rules.

Can you clarify why you want to jump to x64 ?

Dim rs as DAO.Recordset   -- this works perfect.   Thanks Dale!!
Is going forward, when it 64bit , I should use Dim rs as DAO.Recordset  or dim rst as DAO.Recordset instead dim rs as recordset.  am I correct?  what about Dim db As Database?  this should not be change?

It's the company decided to jump to 64bit. we are upgrade from window 7 to window 10 with office 2016.  I explained to my boss about the issue because it's really time consuming to make changes for all programs.  Can windows 10 have 32bit of the access 2016?

As Pat recommended above I explicitly type each of the following

Dim db as DAO.Database
Dim rs as DAO.Recordset
Dim td as DAO.Tabledef
Dim qd as DAO.Querydef

Can windows 10 have 32bit of the access 2016?

 Yes, but all the Office apps must run in 32 bit.   You cannot have 64 bit Excel and 32 bit Access, and that is true with every version of Office from 2013 and up.

Jim.

Office x64 is in general a marketing trick..it doesn't give any advantage except some Excel handling in huge sheets..in contrast applications like 3D had great performance boost due to better memory allocation and manipulation

@John,

   That's not really true though...there is one distinct difference which is entirely technical; the ability of a process to access more than 2GB of memory.  While that hasn't been a problem with Access up until now, it is becoming more of one (and for all Office apps).

 Later versions of Office are taking up more and more process memory, and as a result, more and more people are getting "out of resources" messages.   So there is an advantage.  

Jim.

@Jim interesting ...i haven't came anything similar across...good to know..
It would be interesting to see a real scenario to see if x64 Access provides any advantage.

Being able to address larger memory spaces helps Access whenever whatever you are doing takes place in memory.  So if you are creating huge arrays or opening huge recordsets, more memory would reduce the potential for out of memory errors as someone mentioned earlier.  Access uses a model that combines working in memory and allocating temp space in the database itself.  It is this allocation of temp space in the physical database that causes bloat so presumably, certain folks could end up with less of a bloat problem if their bloat is caused by this temporary allocation rather than make table and add/delete rows in temp tables processing which will still continue to cause bloat.

I can see the advantage of having 64bit.  we're currently using SQL server as back end and Access as front end.  is make any difference?  also I notice that I access 2016 showing "Enable Content" every time when open up the database, is any way to disable this message and make is automatically enable instead click every time when it opens?

Well i tried to cross check about "gains" of Access x64...it seems that Access 64 offers a new datatype(longLong)  that handles very long number and should/could be used when large numeric operations take place.and thats about all...
IMHO if they were real benefits it wouldn't be so hard to find solid references on the Internet that would "praise" the" shiny good" x64 Access and people would be screaming to jump on the performance wagon...but...
Some years ago when 64bit programs started to emerge i remember some cases where applications showed some clear advantages over their 32bit versions...but they had real cases with benchmarks ...not just a "it will /would work better"
The year right now is 2019 and Access 2003 still is going strong in terms of presence and we are talking about a product released 16 years ago...so i guess it would be safe to say that 32bit Access would/should run fine for many more years.
About the "Enable Content" it is just a security feature ...if you copy-paste all the time Access applications then you just have to make the destination folders "Trusted".
Also about Access & SQL....yes SQL should be x64 because we want it to use all the memory resources to do its job (which it does it well) ...so Access is just  a front end.passing command or parameters and receiving data.

so you do suggest we should use Access 2016 (64bit) instead of Access 2016(32Bit) because we have SQL server back end?  Am I understand correctly?

I suggest you use Access 32bit and leave the SQL server x64

IMHO if they were real benefits it wouldn't be so hard to find solid references on the Internet that would "praise" the" shiny good" x64 Access and people would be screaming to jump on the performance wagon...but...

Understand though that the lack of process space is something very new....people are just starting to bump into this.   In the coming years, you will see this happen more and more as more users migrate to latter versions of Office, but make the decision to stick with 32 bit for whatever reason.   So your really not going to find anything on the net yet about this.  

We're also at the point where many things are available now in 64 bit (ie. ODBC drivers).   I think Access is a bit behind in this area; many things are still not available in 64 bit (ie. 3rd party libs and dll's).  So for many, it will still be a bit early to shift to 64 bit, but it's something people need to be aware of that this is on the horizon.

Jim.

Understand though that the lack of process space is something very new

Office x64 was released almost 10 years ago....and 64bit applications that take advantage of 64bit are from 15+ years ago ..so....if Microsoft really wanted to boost Access it would/could be done ages ago and not suddenly wake up one day and decide to do so....
The truth is that Access is "dangerous" as hell to Ms...despite all the killing efforts...despite all the shiny rivals (Visual Studio) is still the fastest RAD tool on the market ....if for any reason (it won't happen) Ms decided to "boost it" it would wipe the floor on many occasions.

"make the destination folders "Trusted". not quite understand this.  can you tell me a little bit more details?

Office x64 was released almost 10 years ago

Actually, about eight.  But yes, it's been around for a while.   The point is though that it's taken that long to get everything that supports 64 bit in place (like drivers) and as time has marched on, the memory footprint of apps has been getting larger and larger.

A quick "fix" was being Large Address Aware, which let's a 32 bit app use up to about 3GB.  But Access is not one of those apps.   So 2GB is it.

But we are now at a point in time where moving to 64 bit will start wholesale.  Sure there will be holdouts, but 32 bit apps moving forward into the future are going bump into the 2GB limit more and more.

Jim.

The memory footprint is getting bigger and bigger because Ms just adds some eye-candy especially to Ms Access while keeping core functionality pretty much the same.
An application developed in Access 2003 besides the Ribbon and Recordset2 has almost nothing else to gain than an equal one developed in the newest versions....personally i just add a Ribbon and off i go...the code..the functionality is 99% the same....some minor enhancements only to be visible under special cases..like image handling
The funny thing is that Access 2007 tried to be a bit "rebelious" in terms of treating older Access applications (had some issues ..maybe were related more to the Vista fiasco but i haven't worked extensively in that version)..but Access 2013 and newer are much more "friendlier"..(under 7/10)

You guys are soooo helpful!!  very appreciated!!!  I learn a lot today.  Thanks every one!!!

Thanks again for your time, each one of you!!!!

The world of 64-bit Access/Office is here!  Better at least have a plan for using it, as you may not have the option between 32 and 64-bit in the future.  You'll have the same issue when 128-bit comes along....

Yes, that's why we are upgrade to 64bit

If it makes you feel any better, the .Net guys down have it any easier...  With Access, however, it is getting harder and harder to find 64-bit replacements for OCX controls and other library files...

Well , i hope you won't get me wrong but judging from your questions the decision in moving from 32bit to 64bit ..well its keeping you extra busy...just every single code (by 99,9%) is for 32 bit...just about everything you want to use has to be converted ...and you know that you are not getting any performance increase ...(rather on the contrary)...so are you sure you to move to this direction..
Give the fact that Access is only (should be) a simple FE ...why waste time on battling with 32bit vs 64bit when you can invest on the SQL BE to make it "fly"

thanks for your guys help and answer my questions even I still couldn't figure it out yet

that's the company's decision

Its the magic word...understood.