Access to SQL server (1 Viewer)

[ponneri]

Hi.

Can I use SQL server express (the free DB) as backend and Ms-Access 2007 as the front end for an application ?

The reason is that; the the application I designed is working fine - split over a network with as Access front end and backend with few hundred records.

But I guess, due to a biz requirement - we might soon import some 250,000+ records into one of the tables used in the application; which may slow down.

Is it possible to carefully replace the backend Access MDB with SQL server DB ?

Any help appreciated.

 

[pbaldy]

Sure, though Access may (should) handle that many records just fine.

 

[ponneri]

Thanks Paul.

But will Access really slow down ?

 

[pbaldy]

There are too many variables to make that determination. Like I said, Access may handle the extra records fine. Switching to SQL Server isn't necessarily a magic bullet, either. The app needs to be designed to use its tools. I'd import the records into your Access BE and test, then go from there.

 

[Simon_MT]

Performance can rely on Pre-Filtering records and careful indexing. It may pay to understand where the bottlenecks occur and fine-tune your Access database to see if any performance gains can be acheived.

25,000 records in a table is well within the cope of Access. I found that Indexing fields that are regularly used to clculate KPI's made a significant difference.

While not normalised I'm not against totalling large transactional datasets into tables at the lowest level of consolidation. 350,000 records could be consolidated to 70,000 records but each Remittance had no more than 20 records compare with over 200 individual transactions.

I agree with pbaldy try your inport and measure the performance issues.

Simon

 

[Galaxiom]

we might soon import some 250,000+ records into one of the tables used in the application;.

With proper indexing that isn't an issue for Access. I had a database working fine with nine million records in a table.