Using Access to import data into SQL Server

Question

Using Access to import data into SQL Server

crowegreg

SSC Eights!

Points: 965
More actions
May 8, 2015 at 9:41 am

#307879

Is their an efficient way of using Access to import data into a SQL Server DB?

Viewing 7 posts - 1 through 6 (of 6 total)

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

WendellB SSCrazy Eights Points: 8678 More actions · Answer 1

Where is your data coming from? In other words, is it data in Excel to begin with, or is it delimited text from some external system, or is it in Access now and you want to move it to SQL Server. If it is the latter, there are two basic options, the Access Upsizing Wizard, and the SQL Server Migration Assistant. Depending on the version of Access, the first can be kind of problematic, but if you are using Access 2010 or 2013, the upsizing wizard does a decent job of moving tables, indexes and relationships and constraints. If you are using SQL Server 2012 or 2014, then I would probably choose the migration assistant, although I see they claim it can move to any SQL Server from 2005 forward.

If this doesn't help, give us more details about the source of your data and how it gets to Access.

Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!

crowegreg SSC Eights! Points: 965 More actions · Answer 2

Thanks for the reply.

I should have written I'm importing Excel spreadsheets on a weekly and monthly basis. I'm hoping their is some other way than using SQL import/Export wizard.

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 3

crowegreg (5/8/2015)
Thanks for the reply.
I should have written I'm importing Excel spreadsheets on a weekly and monthly basis. I'm hoping their is some other way than using SQL import/Export wizard.

for clarification....you have posted in an Access forum.....so are you looking to import excel to Access or SQL?

and it would be helpful if you could provide some examples of the source and destinations

thanks

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

crowegreg SSC Eights! Points: 965 More actions · Answer 4

I've been using an Access DB that consists of a front end DB and a back end DB. I've upsized the back end to SQL.

Previously, I would import an Excel spreadsheet into Access. Now I'm needing to import the spreadsheet into the SQL DB. I'm hoping I can use Access, efficiently to do this.

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 5

crowegreg (5/8/2015)
I've been using an Access DB that consists of a front end DB and a back end DB. I've upsized the back end to SQL.
Previously, I would import an Excel spreadsheet into Access. Now I'm needing to import the spreadsheet into the SQL DB. I'm hoping I can use Access, efficiently to do this.

sparse on details......can you explain a bit more

for example....how were you importing the excel data into Access before?

details and examples will help

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

WendellB SSCrazy Eights Points: 8678 More actions · Answer 6

OK - you still have two options - you can import Excel workbooks into SQL Server using that Import/Export capability, but the details vary depending on the version of SQL Server, and the configuration of SQL Server (Express, Developer, Standard, etc.) In Access there are actually a couple of way to do the import. One is to attach to the Excel workbook so it is treated as a table, and then run queries to append the data to a SQL Server table. The other is to use the Import/Export capabilities of Access and create a local table in Access. The latter option can be automated using VBA to invoke the TransferSpreadSheet() method.

The level of automation, and your familiarity with programming in Access VBA or in T-SQL/C# or some of the options with SQL Server. One issue I've seen repeatedly over the years is that Excel workbooks aren't always well structured and constrained. For example, somebody puts a formula or some other data in a cell that is supposed to be numeric or text, or they insert a row, etc. Another issue to be dealt with if you automate is how the workbooks are named. As J Livingston observed, those kind of details is where the devil lurks. Are they always consistently named, are they always in the same folder, and is the name for a given date predictable? Based on those kind of issues, many developers choose to replace the workbooks with a database front-end where those kind of issues can be controlled. But I've seen a number of situations where the approach you envision has been successfully implemented.

Peel the onion another layer and hopefully we can provide more specific guidance.

Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!