SQL server --> Linked servers

I am in need of some assistance.

• I currently use MS Access thru an ODBC driver (Cache) to get to customers database to retrieve data

• I need to run a report for several years worth of data that will bust the 2GB MS Access limit
• I’d like to go from SQL Server --> ODBC -->Cache database and retrieve the data into SQL server as a local table so I can manipulate it and report against it

•  I have no direct access to the cache database other than thru ther ODBC connection

Facts
SQL databse - name = Lee
Cache driver - uda_m_prd_lee
Cache table - is called charges

How can I run an import query into the SQL database to retrieve records from charges... / How do I setup the Linked server?

e.g. INSERT INTO Lee!charges  
    SELECT * FROM uda_m_prd_lee!charges
    WHERE trans_date>=20110900
hwassingerAsked:
Who is Participating?
 
ValentinoVBI ConsultantCommented:
Besides the linked server, you've got another option which seems to be interesting in this case: the Import and Export Wizard, launched from the Management Studio.

The following article explains how this wizard can be used to import Excel data. Not 100% what you need but it's very comparable and should help you get started.  Just select the correct data source for Access.

Here's the article: http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_3248-Importing-Excel-Data-Using-Integration-Services.html
0
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.

All Courses

From novice to tech pro — start learning today.