Access to SQL. good idea?

Paul11 · 19 Jun 2009 at 13:50

at the moment we run a small access database but need two users to access it from different locations.

i have tried a VPN, but its not ideal (althought ive not tested it since the host officed move to BE from talktalk)

i can have the system moved to an online SQL type system. with a custom built front end , web based.

what are the pros and cons of doing this ?

do companies run online systems as far as losing net connection, means you lost access to all your info ?

how slow will it run on a hosted/SQL system compared to a local access app ?

thanks

daz · 19 Jun 2009 at 14:08

Access is not particularly scalable so the sooner you move to MS SQL (the easiest transition) the better in my opinion.

Paul11 · 19 Jun 2009 at 14:22

thanks daz,

i was trying to work out how much slower will it run using a web based system ?

compared to running access on a local pc ?

daz · 19 Jun 2009 at 14:25

Paul11 said:
thanks daz,

i was trying to work out how much slower will it run using a web based system ?

compared to running access on a local pc ?

It's always going to be slightly slower accessing something remotely rather than locally, but assuming you put some decent hardware behind it and you have a reasonable internet connection, it will be no slower than accessing most websites.

Paul11 · 19 Jun 2009 at 14:31

thanks.

sounds like a no brainer then.. i wasnt sure if it would be very slow to constantly update ..

adren · 19 Jun 2009 at 19:59

Depends how much data it will be passing back and forth, but as you said it is small it shouldn't be a problem. In my experience SQL server is much better for concurrent users, even on small limited user apps.

Nothing stopping you from using the access db as a web app back end either...

Paul11 · 19 Jun 2009 at 20:04

do you mean use access as the front end linked to the SQL server ?

would that be quicker than web based, or no difference in speed ?

adren · 19 Jun 2009 at 20:16

Paul11 said:
do you mean use access as the front end linked to the SQL server ?

would that be quicker than web based, or no difference in speed ?

I meant you could still use the actual access database as your back end to a web front, if for some reason you didn't use sqlserver. But yes, you could use your access front end to a SQL back end over the net with relative ease, we use it a fair bit with old access apps that we still need to maintain.

Paul11 · 19 Jun 2009 at 23:38

thanks

im going to look in to all the options

Dolph · 19 Jun 2009 at 23:42

You could also (if you haven't already) split the access database, keeping the data itself on the network and all the forms/queries etc running locally on each machine, which is much faster than keeping a single access database with everything in it.

Paul11 · 19 Jun 2009 at 23:46

Dolph said:
You could also (if you haven't already) split the access database, keeping the data itself on the network and all the forms/queries etc running locally on each machine, which is much faster than keeping a single access database with everything in it.

that is what we have.

and the remote office just links in via VPN to get to the Data file whilst having the app locally

it s just not very snappy and feels a bit laggy at times

i was hoping to upgrade our system to add more functions so thought it might be a good time to make the transition to SQL and web based as long as it was quicker (hopefully) than what we have now

lokkers · 20 Jun 2009 at 09:34

Paul11 said:
i can have the system moved to an online SQL type system. with a custom built front end , web based.

That sounds like a web application with a SQL backend - this would be much faster than Access, as the only speed bottleneck would be your internet connection to the site itself (rather than running queries across your VPN) if they are hosting the site.

Sounds like a much better solution to me (and one I was going to suggest in a similar thread to this last week).

Paul11 · 23 Jun 2009 at 22:52

lokkers said:
That sounds like a web application with a SQL backend - this would be much faster than Access, as the only speed bottleneck would be your internet connection to the site itself (rather than running queries across your VPN) if they are hosting the site.

Sounds like a much better solution to me (and one I was going to suggest in a similar thread to this last week).

we are looking at the option of keeping our access front end on local pc's and linking the backend to a MSSQL on a web server.

will this be as quick as a web based system ?

any downside to doing it this way ?

thanks

adren said:
. But yes, you could use your access front end to a SQL back end over the net with relative ease, we use it a fair bit with old access apps that we still need to maintain.

adren, does this system work at a decent speed ?

Pho · 23 Jun 2009 at 23:16

Any reason for choosing MySQL over MSSQL? MSSQL and Access are both Microsoft products so should (in theory) work better together.

Paul11 · 23 Jun 2009 at 23:26

sorry i meant MS SQL

edited post.