Solved

How do I best migrate data from thousands of MS Access files to MSSQL Server?

Posted on 2014-09-18
24
281 Views
Last Modified: 2014-10-05
I have production data in the form of an MS Access file for each shift.  There are more than 5600 of these files.  I need to get all of these data into an SQL database.  Obviously running the upsizing wizard on 5600 files isn't practical.  I'll also have to add some fields for shift and day, etc.

I looked at this question, Q_21255773, but it looks like that was a single Access file, with hundreds of tables.  I have thousands of Access files with about 4 tables each.

Using DTS sounds intriguing, but I have no idea where to "find" DTS.  In MSSMS, I can not find anything labeled Data Transformation Services or DTS.

I've gotten pretty good at VBA in Excel.  I could probably put something clunky together that way, but I would prefer to do it "right".

Additionally, if I should determine that this is more work than I want to tackle, where is the best place to find someone I can hire to do this?  I'm a member of Freelancer, but it doesn't seem like this is the kind of work that it is targeted at.  What is the best way to describe what I am asking for so that someone with the skill set I need will know what I want?
0
Comment
Question by:StudmillGuy
  • 12
  • 4
  • 4
  • +1
24 Comments
 
LVL 84
ID: 40330973
Are all those files in a single directory? If so, you could write a VBA routine that would iterate through the contents of that directory, open each database, and then use basic SQL INSERT queries to insert the data found there into your SQL Server tables.

If they're not in the same directory ... then you could do the same thing, but obviously you'd have to somehow find those files and dump them. That could be simple, or it could be extremely complex, depending on how they're stored.

Regarding hiring someone: EE has the new Hire Me feature. If you're interested, you can review the Profiles of the Experts who you think might be able to fulfill your request and see if they've enabled the Hire Me feature, or if they've provided contact information in their profiles. If so, you could certainly contact them to see if they're interested.

As far as the best way to describe it ... you've done a pretty good job here, so I don't think you'd need much more. The potential hire should have experience in working with Access and SQL Server, and have a very good grasp of relational data fundamentals and "raw" data handling (i.e. not in a Form or such, but directly in code/SQL).
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40330995
The problem would be with foreign keys.  Assuming there are no foreign keys among the tables then:
1. Create the tables in SQL Server.
2. Write a code loop that finds each database.
     2a. Link to each table
     2b. run an append query to copy the rows to the server table.

If the tables are related, the problem becomes more complex because once you append the records of the "parent" table, you have to be able to retrieve the new PK values so you can modify the FK's in the child tables.  One way to do that is to include the old PK value in the server side table.  Then once the parent data is appended, the append queries for the child tables include a join to the new server table on the old PK/FK field but the append value uses the new PK rather than the old FK.  After you have appended all the child records for the current database, I would run an update query to clear out the old FK field.  That way ONLY the database you are currently working on would have the old PK field populated.  That would avoid potential conflicts caused by one database table duplicating the keys of another which would almost certainly happen with that number of databases involved.
0
 

Author Comment

by:StudmillGuy
ID: 40331037
Well thanks for the feedback Scott.  You've confirmed that I am perhaps not completely out of my mind for considering the VBA approach (and yes, I've collected all of the files into a single directory on the server).  And I can handle whatever file/directory manipulation I need to do.  Writing that might be a pleasant break from my normal duties.  I've gotten pretty comfortable with the VBA object model for Excel, I should probably learn the Access one.

It used to be prohibited to talk about hiring people directly on EE - I haven't kept up with the times.
0
 
LVL 13

Assisted Solution

by:Russell Fox
Russell Fox earned 166 total points
ID: 40331042
If you can get a complete list of the Access databases into a table, you can use a cursor to loop through them and get the data with OPENROWSET:
DECLARE Droid CURSOR READ_ONLY FOR 
	SELECT DBName
	FROM dbo.DBList
------------------------------------------------------------------
DECLARE @DBName VARCHAR(200)

OPEN Droid

FETCH NEXT FROM Droid INTO @DBName

WHILE (@@fetch_status <> -1)
BEGIN
	IF (@@fetch_status <> -2)
	BEGIN

		INSERT INTO [dbo].[YourNewTable1]
			(DBName, Field1, Field2, Field3, Field4)
		SELECT @DBName, [Field1],[Field2],[Field3],[Field4] 
		FROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0', @DBName)...[AccessTableName1]
		--	------------------------
		INSERT INTO [dbo].[YourNewTable2]
			(DBName, Field1, Field2, Field3)
		SELECT @DBName, [Field1],[Field2],[Field3] 
		FROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0', @DBName)...[AccessTableName2]
		-- Etc.
	END
	
	FETCH NEXT FROM Droid INTO @DBName

END

------------------------------------------------------------------------------------------
CLOSE Droid
DEALLOCATE Droid

Open in new window

0
 

Author Comment

by:StudmillGuy
ID: 40331055
Very wise and good info Pat.  I'll have to verify how "key-ridden" the original data is.  I think it is pretty darn simple because these Access files are generated by a third app that is not going to have access to the full power of Access.
0
 

Author Comment

by:StudmillGuy
ID: 40331062
Wow.  I'm already getting actual code.  Awesome.

It certainly looks like DTS was a blind alley.  Thank goodness for EE.
0
 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 167 total points
ID: 40331172
It is relationships you need to look for.  You said there were only four tables in each database.  You need to figure out how they are related.  Given they are shift specific, you may actually need to add data to the rows you append if shift isn't in the data itself.

So, the databases might contain an employee table, a work stations table, and a login/out table.   The login/out would be the problem.  It would have a primary key and then foreign keys that point to both the Employee table and the workstations table.

This whole thing is terrifying to me.  It sounds like it was set up by a refugee from the Excel camp who forgot to check his spreadsheet at the door.  No one in their right mind would set up an Access application to have a separate database for each shift.
0
 

Author Comment

by:StudmillGuy
ID: 40331339
It's just production data, Pat, so I doubt there are many, if any relationships.  Recall that these Access files are created by a third piece of software.  I've worked with that software which has a miserable scripting language and horrible database connectivity.  I doubt the original programmer did anything fancy.

I work in a sawmill, so the number of people in their right mind who have gone before me are in short supply.
0
 
LVL 84
ID: 40331393
It used to be prohibited to talk about hiring people directly on EE - I haven't kept up with the times.
You can hire out the experts here, but you cannot directly solicit in the threads - for example, you cannot write "I want to hire someone to do xyz for me".

It's ok to post as you did, that you _might_ consider hiring someone ... but that does get somewhat close to the edge. I'm sure the Moderators would let this slide, however, since your intent is not to use the EE Q&A forum as a vehicle to your hire, and are instead attempting to do this yourself.
0
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 167 total points
ID: 40331409
And if you're doing this from Access:

Dim sFile As String
sFile = Dir("C:\YourDirectory\*.mdb")
'/ now open a connection to the Server:
Dim con As ADODB.Connection
con.ConnectionString  = "YourConnectionSTring"
con.Open

Do Until sFile = ""
  Dim dbs As DAO.Database
  Set dbs = DAO.OpenDatabase("C:\YourDirectory\" & sFile & ".mdb")
  Dim rst As DAO.Recordset
  Set rst = dbs.OpenRecordset("SELECT * FROM TAble1")
 
  Do Until rst.EOF
    Dim sql As String
    sql = "INSERT INTO YourSQLTable(Field1, Field2, Field3) VALUES(" & rst("Field1") & "," & rst("Field2") & ","& rst("Field3") & ")"
    con.Execute sql
    rst.MoveNext
  Loop
'/ get the next file
Dir
Loop

Open in new window

As Pat said, the relationships might give you trouble, even if they are not explicitly defined in the Access databases. For example, if Table1.Field1 is used to relate to records in Table2, then you'd have to be SURE to store the original value from Field1 in the SQL table, then run a routine to match up the records in the NEW Table2 before you move on to the next Access database.
0
 
LVL 13

Expert Comment

by:Russell Fox
ID: 40331438
And FYI, DTS, Data Transformation Services, was replaced by SSIS, SQL Server Integration Services, as of SQL Server 2005. You can use those tools to accomplish what you're doing, but I think just using code is easier unless you really want to learn about them.
0
 

Author Closing Comment

by:StudmillGuy
ID: 40331723
I am going to incorporate either code or advice from all of the accepted solutions.  I will attempt looping through the files in VBA.

Thanks experts.
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

Author Comment

by:StudmillGuy
ID: 40331753
And Pat, your comment
This whole thing is terrifying to me.  It sounds like it was set up by a refugee from the Excel camp who forgot to check his spreadsheet at the door.  No one in their right mind would set up an Access application to have a separate database for each shift.
is definitely justified, but reality is far worse than you imagined.

As an example, nowhere in any of the reports in the Access files does the name of the file or the date of the data appear.  If you have printed out multiple reports, you have no idea what the data is for.  None of the data is even date stamped.  As I write the VBA code, I'm going to have to parse the date out of the filename and add fields to the data for that.  There is a log number and a stem number in the files, so I have 5600 log #1s.

Oh well.  It's going to keep me out of the bars for a while.
0
 
LVL 84
ID: 40332261
It's going to keep me out of the bars for a while.
It would probably keep me IN the bars :)
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40333037
As I write the VBA code, I'm going to have to parse the date out of the filename and add fields to the data for that.
I warned you:)  It scares me when I can predict how bad these things are.

What are you going to do going forward?  Do you have to set up a daily sweep to pick up the files created the previous day?

I'm with Scott, bring out the booze.
0
 
LVL 13

Expert Comment

by:Russell Fox
ID: 40333130
Today is International Talk Like a Pirate Day, so some rum is in order...
0
 

Author Comment

by:StudmillGuy
ID: 40333143
Aye.  That's right me hearties.  A tot of rum to the crew and eye patches all around.
0
 

Author Comment

by:StudmillGuy
ID: 40333146
A double for the captain.
0
 

Author Comment

by:StudmillGuy
ID: 40333176
Going forward I already have a system in place.  I have already built a separate system that is directly inserting most of the data directly into an SQL database.  The issue of getting the old data into the DB arose when my boss brought up the desire to compare different shifts to one another.  That's where we're going to need the old data.

And the situation is even worse than I've let on so far.

The filenames are of the format "Production_D-M-YYYY_TTTT.mdb", so they are impossible to sort.  You can't sort by the file date because most of the files have been opened at one time or another.  I think the first thing I will do is write some code to simply rename all of the files to something sortable, THEN go back and write the code to open the files in order and insert the data.
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40333211
Please, you're making me nauseous.
0
 

Author Comment

by:StudmillGuy
ID: 40333219
That's seasickness from the rocking of the boat.  Here, try an eye patch.  It ruins your depth perception.
0
 
LVL 13

Expert Comment

by:Russell Fox
ID: 40333723
"Nauseated". But yes, you're going to need a way to separate out the records from each database. If you can get the name into a field it would be pretty trivial to extract that D-M-YYYY into a usable date field:
SELECT CAST(LEFT(REPLACE(FileName1, 'Production_', ''), CHARINDEX(REPLACE(FileName1, 'Production_', ''), '_')-1) AS DATE)

Open in new window

0
 

Author Comment

by:StudmillGuy
ID: 40335420
An additional bit of fun.  Our night shift spans midnight so all of the PM times are actually from the day before the date of the file name.  Nothing I can't handle - it just adds to the fun.
0
 

Author Comment

by:StudmillGuy
ID: 40362705
Just to keep everyone up to date...I got the code written.  The number of files has grown to 5642 viable files in the meantime.  It's taking around 2 minutes to process each file.  It's looking like about 7.83 days.  I brought in a spare laptop to grind away on it.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now