Tag Archives: data

Standard

Posted by

Posted on

December 17, 2011

Posted under

Access 2010, Microsoft, SQL 2008r2, Technology

Comments

5 Comments

Migrating Access Data to SQL 2008r2

I recently spent some time building an Access database file full of 2010 US Census data… As an Access database, it’s not very useful since I want to use the data within as a feed for my Windows 8 Metro application.  I’ll need to move this data into my SQL 2008r2 server to make it available to my application.

To do this I’ll be using the Microsoft SQL Server Migration Assistant (SSMA) toolkit I recently installed.

 

image

Upon first use, I’ll need to acquire a license for the tool as instructed by the included Readme file.

SNAGHTML26d9097

image

image

After filling out the form, they provide me a file which I will through onto a network file share and give to the tool…

image

SNAGHTML27292d9

ok, in the above pic, I didn’t need to specify the filename… just the directory it sat in… then the license imported correctly.

SNAGHTML27426ed

SNAGHTML2766802

SNAGHTML276c8e6

SNAGHTML27702f9

SNAGHTML2774749

SNAGHTML2776831

After specifying the database, I had to give it a minute or so to completely read in and load my database file… the 2010 Census file is right at 100mbs.

SNAGHTML2785904

Then specify my SQL server, destination Database name, and my authentication method… I’m in a domain so I’ll simply use Windows Authentication since my Domain account has the appropriate level of access to the SQL Server.

SNAGHTML2795a3c

Yes Please…

SNAGHTML27a5a9a

SNAGHTML27a9633

I do not have any pre-existing applications which connected to this Access file… thus no need to link anything.

SNAGHTML27afacf

Let the Migration begin!

SNAGHTML27afacf[4]

At this point I could see the connection and the data migration was occurring…

image

SNAGHTML27d947e

back on the server, my tables are now created…

image

On the Migration Wizard, the data is slowly being copied over…

SNAGHTML27dd516

This is where my SQL server is running a tad slow… a lot of data being input into the database…

SNAGHTML27f5b55

SNAGHTML28012e1

Wizard complete, and I can see the newly arrived data on the server.

image

According to the Migration report… there were Warnings… but no Errors.  The Warnings mostly related to database quality… IE:  some tables did not have primary keys, others had their primary key nullable value configured.

SNAGHTML2823fe9

Those tables with nullable primary keys were corrected to not allow null values.

image

Those without primary keys were ignored since SQL server doesn’t consider that a serious problem…

image

That and I’m not worried about those two tables considering they were used during the “Import” process from the Census .dp files and won’t be used again.

It’s time to integrate the new found data into my Windows 8 application!

If you have any questions or comments on this, please leave them below!

Standard

Posted by

Posted on

December 16, 2011

Posted under

Access 2010, Microsoft, SQL 2008r2, Technology

Comments

1 Comment

Microsoft SQL Server Migration Assistant for Access

Microsoft SQL Server Migration Assistant (SSMA) is a toolkit for quickly and easily converting Access Databases to full fledged SQL Server Databases.  Exactly what I was looking for to move my newly created 2010 Census Access Database to someplace more usable for my purposes.

Download the toolkit at:  http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=959

And thanks goes out to the Access Team over at the Access Blog for pointing me to the right place thanks to a wonderful Bing search.

Here’s the installation of that tool…

SNAGHTML25c80fd

SNAGHTML25cb97b

SNAGHTML25ce951

I have plenty of space and do not like to go back and reinstall things I need… so I’ll do a complete installation.

SNAGHTML25d6e48

SNAGHTML25da253

SNAGHTML25de0f8

SNAGHTML25e09ac

That’s it… checkout my next article where I describe using it to actually migrate Access data to my SQL 2008r2 server.

If you have any questions or comments, please leave them below!

Standard

Posted by

Posted on

December 15, 2011

Posted under

Access 2010, HACK-The-Planet, Microsoft, Technology

Comments

11 Comments

Creating a 2010 Census Database

So you’re interested in the 2010 Census results.  Well they’re publicly available at http://www2.census.gov/census_2010/03-Demographic_Profile/ and with a little bit of work, you can have yourself a fully stocked Access Database with complete 2010 Census data.

Here’s a simple step-by-step using Microsoft Office – Access 2010 to create a database and import the data for the state( s ) you want to collect.  Later I’ll show you how to turn the resulting Access 2010 file into a SQL Database for SQL 2008r2.

Download the “DPSF2010_Access.accdb” file from the url above.  Also download the various .zip files under the states your interested in.  Then open up the Access file and perform the following steps as described in their documentation…

SNAGHTML1c746f7

SNAGHTML1c7c20f

We’ll first import the demographics data… so select the non-“geo” file.

SNAGHTML1c80833

SNAGHTML1c84a61

SNAGHTML1c88b95

SNAGHTML1c8bcd2

Select the “Data Part…” specification since this relates to the Demographics file.  When you repeat this wizard for the Population (geo) file, you’ll need to choose the other “GeoHeader…” option on this screen.

SNAGHTML1c9071a

The specs will automatically change to the correct format.

SNAGHTML1c931e2

SNAGHTML1c95e10

SNAGHTML1c98b37

SNAGHTML1c9adf3

This step is important… so specify the correct primary key.

SNAGHTML1ca1fa9

Name your table… I’m importing everything, so I’ll name it the state and attach Demo onto the end and Pop on the end of the geo file.  This will give me tons of data in two different tables for each state.

SNAGHTML1ca62b1

 

Have fun with data… if you have any questions or comments, please add it below!