SQL 2K5 with MS Access linked database
-
I'm about to undertake a rewrite of a site built around a number of Access databases. Unfortunetly I'm stuck with this database setup.
Some of the site will be based round SQL 2K5 however I thought to streamline development I could use the Access databases through one SQL server with linked access database servers.
My main thought on this is that it would present a simpler upgrade path in the future if the site moved to soley SQL server plus I could take advantage of some of the features in SQL server.
My question is are there any issues with working this way as apose to working with the access database directly? A summary of pros and conns to this approach would be much apreciated with links to further reading on the matters if needed.
-
Well comparing Access and Sql server is like comparing a Lada to a Ferrari. It always depends of what they are doing but I would much preffer transfering everything to sql server for the backend. Now if you want to keep access as front end and lower the port cost, then I would understand but I would strongly suggest that you use only 1 backend... and the best of the two if possible
. -
Maybe I didn't explain myself to well....
I'm not comparing Access to SQL server I've worked with both before and I'm aware of the differences and this is a website therefore the front end is ASP.net.
What I'm looking at specifically is using linked servers where access is the back end. I beleive in SQL Server 2K5 it uses the OLEDB provider to link Access.
There is no real way to get away from the multiple data sources at this point in time as there are third party systems involved. The only element that can be SQL server is the bespoke part that I have full control over fortunetly this is the part that will take most of the load. Even if it went down to two datasources one of those would still be an Access database so the question would remain.
Really I want to know if using linked servers has any pitfalls over directly using the MS Access databases or if there are any advantages.
Like I said it's a way to get all the data into one place so I don't have to worry about multiple connection strings and data providers wthin the application itself and provides a simpler upgrade path in the future.
-
I never had any experience on that matter.. the only time I used a remote data source was when I had to work with a 3rd party budgeting software. I used the linked server then however I never was able to make it work fast so I had to move some stuff to sql server using a sort of manual replication. I hope however that access will be a faster mean to do the same thing for you
.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply