Access 2010 to SQL Express 2012

Report · Jan 29, 2014

I'm upgrading a client from CF5 to CF10. They are using Access 2010 and I need to get it to SQL Server Express 2012 as part of the upgrade.

This should be simple enough, right?

I have access to SQL Server 200 / enterprise manager, SQL Express 2005 / 2008 / 2010 / 2012.
I have access to a couple flavors of Access.
I have access to a 32 bit Vista machine, a 64 bit Windows 7 machine, a 32 bit Windows Server, a 64 bit Windows server, and more that I won't bore you with.
Of course, I have CF5, CF6.1, CF7, CF8, CF9, CF10

Got all the tools I should need, right?

Tried to import Access from SQL 2012; errors due to data.
Tried to push (export) Access to SQL 2012; errors.
Tried using every version to push and pull the data, always a couple errors keep it from being clean.

Let's be honest, here. There are so many databases, tables and columns, the last thing I want to do is data-map each and every field in every table in every database. I'm a programmer, right? I can do this.

I installed the Access database engine and get my Access databases connected in CF per some obscure instructions I found for 64 bit CF. Wrote a program that would inspect each database, each table, each field and tell me what's missing and what won't fit.

All Access and SQL connections verify in the CF datasource. I'm good to go!

Program won't run. CF doesn't like the 32 bit Access driver. "[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver]Optional feature not implemented", error code 106.

Okay, let's try the 64 bit version based on what my Google search about this found.

Huh. Installation failed because the client has some 32 bit MS Office programs loaded. Same with my local machine.

Oh, turns out I was trying to use CFDBINFO against the Access database. So, don't do that, but now my easy diagnoses of the databases, tables, and columns is out the window on the Access side.

Every which way I turn around, it seems something prevents me from importing, exporting, or connecting via DSN in order to write a program to handle the problem.

I seriously considered plunking down $300 for Spectral Core's Full Convert 6. Their trial worked for me, but buying programs goes against the grain when it's (normally) in my wheelhouse.

Found an article (http://www.coldfusionmuse.com/index.cfm/2005/8/26/access_metadata) that helped me mine Access the old fashioned way, once I learned how to exposed the MSysObjects table (Navigation Options) and allow them to be read (File, Users and Permissions, User and Group Permissions, select MSysObjects table and enable read).

Now to cycle through the tables and identify what's missing, set the new columns up and copy the data over.

I think I might be on my way, but this is still reeking of "the hard way to do it".

And my wife wonders why I suffer from indigestion.

Thoughts?

Report · Feb 04, 2014

RLS wrote:
They are using Access 2010 and I need to get it to SQL Server Express 2012 as part of the upgrade.

What about the following procedure, assuming Access and SQL Server are both installed:

1) Open a database in Access 2010;

2) Go to the Database Tools tab. Choose to move data to SQL Server, using the Upsizing Wizard;

3) Configure the SQL Server database so created in the ColdFusion 10 Administrator.

Report · Feb 04, 2014

Thanks, BKBK. Been there, done that (that was step #2 above).

Didn't work, and that was very dissapointing to me. Both newer products from the same manufacturer (Microsoft) and they can't have a working conversion in place? Bleah.

Some of the Access tables have records with + in front of them, giving them a complexity that the wizard apparently can't deal with.

After hours of attempts, I had my client purchase Spectral Core's Full Convert 6, and that worked pretty slick, but it was not flawless. On a couple of tables it left what I suspect is a checksum column ("upsize_ts") in place, created one table (the most complex one) as two tables (same name but one ended with $) but did not copy over all of the records, and for three tables it created the structure but did not transfer any data over.

Will still try to pull over those three or four tables again, but if I could just get a CF DSN to both versions on the same machine this would be a lot more simple.

RLS