Upsizing an Access 32 bit backend database to SQL server 2014 64 bit

alevin16
alevin16 used Ask the Experts™
on
Hello All

I have an Access program that I created with Access 2013 32 bit.  I split the database into a front end and back end.  I wanted to move the backend onto an SQL server (or upsize it if you will).  When I try I get an error.  I am sure it has to do with the SQL server being 64 bit and the database being 32.

Is there anything I can do to convert the backend tables to 64 bit?

Thank you all!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
>When I try I get an error.
For starters, telling us the error message or including a screen shot would greatly help, as we're not very good at troubleshooting 'an error occurred'.
President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012
Commented:
You need to use 32 bit ODBC drivers with your 32 bit Access app, so you want to use the ODBC manager in:

C:\Windows\SysWOW64\odbcad32 exe

  You will then be able to connect to your back end tables.

  Also, if you have not already done so, add a TimeStamp column to every SQL table that you'll use with Access.  This turns on row versioning and allows JET to work with SQL more effectively.

Jim.
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
By the way, make sure you use a system DSN.

Jim.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Distinguished Expert 2017

Commented:
I agree with Jim but having done this recently, I may know what the problem is.

If you are using SSMA to do the migration, you will need to locate the 32-bit version of the product.  When you do the install, the 64-bit is what is presented as the version you should use but in fact, you MUST use the version that matches the Access database bit-wise.  The 32-bit version is also installed but you'll have to dig for it.

Another warning when using SSMA.  The default for the date data type is no longer DateTime and this will cause you enormous problems getting an ODBC driver installed on ALL user computers that will "see" the dates as a date data type rather than text.  Better to not use the wizard but to set up conversion parameters so you can control the upsizing at the data type level.  I also recommend using SmallInt for your yes/no data types rather than bit which I think is the default.

Before you do the upsizing, make sure that all your primary keys and RI is in place.  If it isn't, you'll need to do it in SQL Server and since most conversions are redone several times, this becomes a real PITA.  When I convert a database, I always go back to the Jet/ACE db and make the fixes there and then redo the conversion until it is clean.  That way, when I do the final conversion with the production data, I don't need to futz with the schema after it is built.
The other thing to take into account when upsizing tables to SQL Server is the security on the SQL Server end. If not a trusted connection, you need to create the user that Access will connect as and assign that user to be able use the database, and the objects inside it (tables, views, functions etc....)


Kelvin

Author

Commented:
That did it!  Thanks for all the help!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial