CODEWITHSUNDEEP
sql-server-2008ms-access
Michiel Soede
Michiel Soede

Reputation: 55

From Access to SQL Server 2008

I am struggling a bit with the following. Our forecasting tool generates some 100k of records of forecast information in Access each month. The tool is an Excel - Access combination. I want to upload the data from Access to SQL Server after the forecast is done (for multiple purposes). I tried to look up the most easy way but not sure what to use. ADO seems handy where I could integrate that in an existing macro that cleans up the database. Anybody done this before and has some snippet of code to use?

We use SQL Server Management Studio 2008, but not sure whether that's relevant here.

Thanks in advance,

Michiel

Upvotes: 0

Views: 57

Answers (3)

Michiel Soede
Michiel Soede

Reputation: 55

Thanks for the answers. I will try to do the linked table way, but I already had a look to that and it seems that due to the fact that I use citrix server I was not able to link it directly to SQL server via ODBC. So I have to check what's different there. Not familiar with the SSIS package yet but I will definitely look into that if the first option does not seem to work out.

Thanks again guys

Upvotes: 0

Gustav
Gustav

Reputation: 56026

In Access, link via ODBC the tables in SQL Server you wish to upload to.

Then create and run append queries to insert the data in the linked tables.

Upvotes: 1

gofr1
gofr1

Reputation: 15997

I suggest using SSIS package which is started by job on sql server side (by shedule or manual). You can create package with the help of sql server import/export wizard. I think this is the best way to import data from access to sql server.

Upvotes: 0

Related Questions