Access front-end / Microsoft SQL Backend? 2

[cutestuff]

hi,

I have a quick question.
I have an Access database wherein the data is getting too large for Access to handle.
I've read that you can do an Access front end (which I know) and an SQL backend.
What is the advantage of this? Will this help memory-wise?
I have a table in Access that has over 100 column fields that I was doing union queries to merge the data but Access is evaluating it as 'query too complex'. The table is actually linked from Excel and this is how they have it setup and is updated daily so I can't port it over to Access.

Can anyone give me some insights?

Thank you very much in advance.

 

[SQLSister]

yes you certainly can do that but to really see improvements you will need to rewrite your access code to stored procs and passthrough queries. YOu could set up a linked server to SQL server to the Excel spreadsheeto or import it on a schedule if you don't need it to be up to the minute.

Questions about posting. See faq183-874

 

[SQLBill]

SQL Server has a much larger storage capacity than Access does. The big difference, besides size, is that Access is a front- and back- end system. It tries to do everything. SQL Server is just a backend system.

Yes, you can use Access as a frontend to a SQL Server backend.

SQL Server has a 'system' that allows you to import from other data locations and 'transform' the data to put it into SQL Server. In SQL Server 2000 it is DTS and in 2005 it is SSIS.

So, you would create your database and tables, then create a DTS/SSIS package that would get the information from the Excel spreadsheet and put it into the tables.

-SQLBill

Posting advice: FAQ481-4875