Access to MSDE

[jwHerm]

Alright, I have a program that runs perfectly when dynamically connecting to an access database. The database is currently housed on a shared folder over our small 4 computer network. The only problem is it runs a little slow for my tastes. I have been reading up on MSDE and understand how to change the access database into SQL format. I also know that, if I change it over, my OLEDB namespaces will need to be changed to SQL. The database in question contains 24 tables with anywhere from 3 to 104 feilds.

 

My real question is will this speed up connectivity to the database in retrieving/changing data? If this were changed to MSDE, would it be more beneficial to switch to a bound connection since I would only be having 4 people, at the most, connecting at one time?

 

Now regarding reports. I love how access handles making reports. I wont be here much longer and will be turning this task over to people who will not be able to write sql to make nice looking reports. Is there anyway to "backup" the data that is in the SQL database to the access database so that reports can be more easily created? Would I just have to make a stand alone program that updates that access database with the new data? This would not be a real problem as I could just set it to run off business hours, but it would be nice if there was just a command that would do this instead.

 

Thanks for any help.

[Nerseus]

I can't speak on MSDE's performance directly. But for Access (which I haven't used in a few years), I never got horrible performance, even with 3 or 4 users at once. Are you sure it's all in the connectivity and not in the queries themselves?

 

You don't HAVE to switch DB providers if you don't want to. If you moved to MSDE you probably would want to though, for performance reasons (the sql provider is made for MSDE/SQL Server).

 

You'll always get better performance out of hard-coded connection (is that what you mean by bound?) vs creating the connection each time. However, I wouldn't think the performance would be that noticible with such few users. At least for MSDE, the connections are usually kept alive on the same machine for 3 minutes (the default, I think).

 

In access you can create a linked table to MSDE. If you moved the data to MSDE, this is the route I'd probably go with. You could also import the data into Access if you needed to, but I think a linked table is what you'd want.

 

-n

[Derek Stone]

You can work with external data sources from within Access. There are no additional steps needed to generate reports for them.