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

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!
alevin16Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornSQL Server Data DudeCommented:
>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'.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
By the way, make sure you use a system DSN.

Jim.
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

PatHartmanCommented:
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.
Kelvin SparksCommented:
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
alevin16Author Commented:
That did it!  Thanks for all the help!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.