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

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?
StudmillGuyAsked:
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.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
PatHartmanCommented:
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
StudmillGuyAuthor Commented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Russell FoxDatabase DeveloperCommented:
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
StudmillGuyAuthor Commented:
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
StudmillGuyAuthor Commented:
Wow.  I'm already getting actual code.  Awesome.

It certainly looks like DTS was a blind alley.  Thank goodness for EE.
0
PatHartmanCommented:
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
StudmillGuyAuthor Commented:
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
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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

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
Russell FoxDatabase DeveloperCommented:
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
StudmillGuyAuthor Commented:
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
StudmillGuyAuthor Commented:
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
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
It's going to keep me out of the bars for a while.
It would probably keep me IN the bars :)
0
PatHartmanCommented:
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
Russell FoxDatabase DeveloperCommented:
Today is International Talk Like a Pirate Day, so some rum is in order...
0
StudmillGuyAuthor Commented:
Aye.  That's right me hearties.  A tot of rum to the crew and eye patches all around.
0
StudmillGuyAuthor Commented:
A double for the captain.
0
StudmillGuyAuthor Commented:
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
PatHartmanCommented:
Please, you're making me nauseous.
0
StudmillGuyAuthor Commented:
That's seasickness from the rocking of the boat.  Here, try an eye patch.  It ruins your depth perception.
0
Russell FoxDatabase DeveloperCommented:
"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
StudmillGuyAuthor Commented:
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
StudmillGuyAuthor Commented:
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
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.