Migrating data from Access to SQL Server 2005

Question

Migrating data from Access to SQL Server 2005

Newton Soares

SSC Enthusiast

Points: 191
More actions
August 7, 2007 at 11:37 am

#68878

My problem is that I have to migrate several tables from Access to SQL Server 2005. Those tables come from Excel, which are converted into Access and then, my idea, is to migrate them into SQL. One important point is that I need those tables, in SQL Server, to be updated constantly. How can I do it? If is possibly guide me through the steps necessaries to achieve my purpose.
Thanks

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

Steve-335899 SSChasing Mays Points: 639 More actions · Answer 1

Hey,

What do you mean by constantly? I do this kind of import and update of SQL tables. Give me a littel more information and I'm sure I can help you out!

Thanks,

Steve

Newton Soares SSC Enthusiast Points: 191 More actions · Answer 2

Thanks for your help.

Let me give you the hole idea. I get some information from the web through Excel, this information is linked to an Access database, so every time that I update my Excel tables my Access database get updated. What I am trying to do now is link the Access database into a SQL Server database, so when I update the Excel my tables in the SQL get updated.I use to update my tables about 20 times a day randomly.

Thank you again

Steve-335899 SSChasing Mays Points: 639 More actions · Answer 3

Hey Newton,

I'm at home right now, but I'll look in the morning to see if I have anything that might do that. Unfortunately i dont think there is a way to do this the way you want to. Access has issues with the 64 bit migration and Locks. You can set up an SSIS package to migrate but automatically when Access changes, I dont think so. If you update 20 times a day you could create a scheduled job that runs an SSIS package that would pull any new information or update information to your SQL tables. ie every 30 mins, or every hour for example.

Again, let me look to see if I might have anything remotely close to your situation in the morning.

Steve

Newton Soares SSC Enthusiast Points: 191 More actions · Answer 4

Newton Soares

SSC Enthusiast

Points: 191

August 7, 2007 at 5:26 pm

#725320

Thank you Steve.

Steve-335899 SSChasing Mays Points: 639 More actions · Answer 5

Hey Newton,

Sorry for the delay here.

Just as I thought yesterday, there is no way that SQL Server can automatically detect changes in an Access DB.

But, as I said, you can create an SSIS package and a job that is scheduled to update your SQL tables from Access at any given time you like. Now, if its a random thing, you can execute the job from a sp which you could run at any time no problem.

In the data flow task you can query the Access tables to only bring over new or changed records to update SQL. If it were up to me, I would by pass the Access step and have my SSIS package take the information straight from Excel.

If you have any questions, please feel free.

Steve

Newton Soares SSC Enthusiast Points: 191 More actions · Answer 6

Hello Steve, Your information was very handy, but the problem is that I am new with SQL and don't know all its features. Could you guide through the process of creating an SSIS package and a job to get the information straight from Excel.

PS. I have the total of 211 tables to import to SQL Server.

Thank you for the help

Steve-335899 SSChasing Mays Points: 639 More actions · Answer 7

When you import all of these tables, are each of the tables going into their own tables or do they all go into one common table? Or if you import from Access is it still 211 tables?

SSIS packages are fairly simple to set up, but do have complexities of course.

I'll do what I can to help

Newton Soares SSC Enthusiast Points: 191 More actions · Answer 8

Hi Steve,

When I import from each of one (Excel or Access) there are 211 tables, all of them going into its own table. I don't know if it changes the procedures, but I prefer doing as you said before, import all tables from Excel. I agree with you that this way is easier.

Thanks

aljtdj SSC Enthusiast Points: 149 More actions · Answer 9

Just to add to the fun, I know this is SQL based forum, but, using Access, you could have an app with a custom form running in the background. The form has a timer property that could be used to launch any process from importing from Excel to inserting those records into Access/SQL tables all at once. You would simply code the events on the form timer property.

TJ

Newton Soares SSC Enthusiast Points: 191 More actions · Answer 10

Hi TJ,

How can I do that? The problem is that I am not familiar with all features of SQL or Access, but the thing is I need to link my Excel tables to SQL Server, the best way possible. Any help will be welcome.

PS.: Since I am not an expert, I would need a guidance to implement the suggestions.

Thanks

Steve-335899 SSChasing Mays Points: 639 More actions · Answer 11

I would avoid Access at all costs....especially if you have SQL at your disposal.

Newton, if you still want to go the SSIS route, you will have to build a package with a DataFlowTask for each sheet and import it that way. This is fairly simple to set up. Create two connections, one is your SQL and one is Excel. These are reusable so you only have to set them up once. Then create a DataFlowTask and within it you will need an Excel Source and a OLE DB Destination. There is a "wizard" that allows you to create a table in your destination based on the source data. You mihgt need, depending on data, a Data Conversion task inbetween the two as you have have to convert unicode characters. This is a starting point

Newton Soares SSC Enthusiast Points: 191 More actions · Answer 12

Hi Steve,

Thanks for the help, but the problem is that I did not understand a word you said. I am completely blind on this. If is not to much trouble could you be more specific, something like a step-by-step guidance? Could you send me a word document with all the screens and procedures that I have to go through???

Thanks again and sorry for the trouble.

aljtdj SSC Enthusiast Points: 149 More actions · Answer 13

Spoken like a true DBA SQL Server is definitely getting easier to use, but some things are accomplished easier outside of SQL Server. Access isn't the end all beat all for sure, but it is very handy for a great many things.

For the rest of us Newton, you would need set the 'timer interval' with a millisecond value. Using the 'On Timer' event, which would occur every time the timer value was met, create an event procedure that would call the queries that would move the data from the linked Excel spreadsheets to the linked SQL Tables.

TJ

Steve-335899 SSChasing Mays Points: 639 More actions · Answer 14

lol...thanks aljtdj

aljtdj, if its easy to do what you suggest, maybe Newton should go with that.

However, Newton, if you want to do it the right way then I can try a simple word doc for you to follow. Warning though, you will have to set up 221 Data Flow Tasks. Seems daunting, but once it is complete, you will have the speed and power of SQL Server on your side