Access to SQL Express

[Learn2010]

I work for a large company. I use an Access DB over a LAN, FE/BE. We have 2 concurrent users that use it 24/7/365. There are several others, as many as 20, that use it at various times of the day. It slows down a great deal when network traffic is high. This is not a real problem at this time, but we are getting ready to open this up to several more concurrent users.

If I convert the back-end to SQL Express, will this increase the speed when network traffic is heavy? Or, is Access going to keep it slow?

 

[GohDiamond]

Try compacting the database both BE and FE. This removes some bloat that may affect speed.

Bandwidth is always going to be a factor but depending upon how well the database was built the speed should be there.

Ask the users that are editing or adding data to gather everything they need to fill out the form they are going to be working on and fill it out as quickly as possible.

In other words, don't get in the middle of the entry or edit and take a coffee/tea break with the edit active. That prevents others from gaining access to the record or possibly to the table being edited if the controls are connected directly to the table (not a 'best practice' for developing an editing form or data entry form).

Taking a few pre-emptive steps can help keep your workflow going smoothly for multi-user databases.

Cheers!
Goh

 

[spikepl]

My guess would be that your gain in network traffic will be zero. , because the same data needs to be dragged across the network, as long as JET is to do its trickery.. But using pass-through queries and just getting the results across might be helpful. Google pass-through queries

 

[Learn2010]

I have complete control over the database. I have been taking SQL classes and whenever I have time I create the queries in SQL instead of design view.

So, is SQL Express going to help me or not. I am not giving up on Access. I currently have over 20 databases in four departments, some very large. Everytime I get involved with the IT guys they want to blast Access. I have done things with Access that they didn't know you could even do.

What I am really trying to do is to get this one database working with SQL with you guys helping and without them being involved. What do I need to do?

Thank you.

 

[SmallTime]

I'm going through very same process myself and must admit it's a daunting task and not as strait forward as you might first expect.

Take a look at this.
http://www.access-programmers.co.uk/forums/showthread.php?t=211549

SmallTime