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!
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!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
By the way, make sure you use a system DSN.
Jim.
Jim.
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.
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
Kelvin
ASKER
That did it! Thanks for all the help!
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'.