Web Migration

[AC5FF]

I'm not sure if this is even possible; but I figured if anyone knew the people here would!!

I've got a HUGE database that is shared out here locally. This DB has 70+, 150+ queries (I'm trying to whittle this down little by little), 60+ reports, and numerous macros. The DB resides on a local computer that is shared out throughout our local network.

We've come to the point now where I get asked several times a week to send data out to this place or that... Or, to send the whole DB out so another section in a different state can look at it. Pulling the data is easy, but the DB is too large to e-mail and since it is constantly updated (24/7 in use) uploading it to a file server just doesn't seem practicle.

How difficult would it be to migrate this over to a web product? Somethiing that could be accessed over the WWW. Would have to re-write several areas to add password protection/read-only permissions; but other than that, could it be done??

Thanks

 

[Alisa]

I don't have an answer for you, but I am in a similar boat, looking at trying to create a web-based version. The only thing I can tell you is that you will have to write your user interface (whatever portion of the frontend your users will have access to over the web) in some other program. And you will probably have to migrate your backend to something that can handle more users because Access doesn't like more than a few users at a time. Anyway, just thought I would reply to you since no-one else has. Good luck.

 

[AC5FF]

Alisa;
Thanks I've been browsing online a bit and was seeing about the same thing you describe. It's wierd though; the current DB is shared out to two work centers and I've never heard of/seen a problem with how many people were using it at one time....

I might try to start 'tracking' that or at least watching..

 

[HHUK]

How many users do you have roughly?

Once you go above 20 people using it simultaniously then it's worthwhile going the web way anyway, their technologies are more used to having that many users and won't get corrupted as easily.

Access features an upsizing wizard which can help you get your database onto the web using SQL Server.

I'd suggest reading up alot on this feature before running it.

 

[Alisa]

I agree, I have up to about 5 people using one of my programs simultaneously, and it is fine, but I wouldn't be comfortable going any higher with Access.

HHUK, do you have any suggestions about what to use for the front end? It seems like there are million languages out there, and no consensus on which ones are good.

 

[HHUK]

I'm in the same boat. I would like to try upscale but I'm not sure what to use as a front-end if I do.

I've read you can use an Access front-end to connect to an SQL Server which may be good news for you.

It's using an ADP file rather than an MDB.
http://office.microsoft.com/en-us/access/HP052731031033.aspx

That was the most useful looking document I saw in a brief search.

The eventual solution would be that you could e-mail the front-end away to someone and they could connect over the web to your online SQL Server.

 

[Alisa]

HHUK, unfortunately, that wouldn't really help in my situation. One of the main reasons to go to a web-based version in my case is to avoid Access distribution hell, which is where I have been living for the last few months.

 

[pbaldy]

For the record, you do not need to use an ADP to use SQL Server as a back end. An MDB/MDE can work perfectly well (most of my applications are that configuration). The ADP is something Microsoft pushed at first but has now backed away from somewhat.

As to the original question, I'm not very strong in web apps. I've personally used ASP, but only for some pretty basic apps that allow customers to see log in and see some basic data. I know you can also use PHP. I've heard mixed reviews on DAP's.

I've got a reservation/dispatch application that is run remotely, but the front end is still Access (the users connect via VPN to the main office). I've also seen Access connect to a remote SQL Server via IP address, as mentioned by HHUK, but have no experience with it. In either of these 2 situations, you'd want to minimize the data that goes over the wire as much as possible.

 

[HHUK]

Ah, well I guess I'm stuck at the same point as you.

Looks like programming a web based front-end could be difficult. I would imagine ASP/ASP.net would be one thing to look into as I've seen it recommended on a few forums.

 

[pbaldy]

Alisa, what do you find difficult about Access distribution?

 

[Alisa]

pbaldy, you are joking right? I mean that in the nicest possible way, but surely I can't be the first person to feel this way. Its a secure MDE, distributed with the 2003 runtime using sagekey, that has to run on computers with every combination of Windows 2000, XP, Vista, and all corresponding versions of Office, professional and not, in locations that do not have any technical staff to help troubleshoot anything.

 

[RossWindows]

Alisa,

I empathize with your situation.
Here's my .02
I've never been savvy with writing scripts,,, actually I avoided it at all costs... but when I started managing a few DBs like the one you mention, I just bit the bullet and stayed up to 4 in the morning for 2 days straight, just learning eveything I could about distributed DB's and scripts (batch files etc.)


It was DEFINATELY worth it!!

You can write a script to check the user's version of windows and/or version of Access and/or the user's runtime-version and run the correct front-end automatically.

Actually, with enough time, money and effort, you can write a script to do pretty much anything. (As I've come to find out)

Right now, I'm adding some code to my FE_AutoUpdate batch file to do exactly what I just mentioned.

 

[Alisa]

I am curious, how does that help with the distribution issues? Right now, I have someone with Access 2007 installed, and everytime she opens my database, she gets the "configuring Access 2003 runtime" message (my installation installs the 2003 runtime to run my database). What could I do with a script that would fix this issue?

 

[RossWindows]

I am curious, how does that help with the distribution issues? Right now, I have someone with Access 2007 installed, and everytime she opens my database, she gets the "configuring Access 2003 runtime" message (my installation installs the 2003 runtime to run my database). What could I do with a script that would fix this issue?

Well, let me start by saying that every version of access has certain things about it that are unique to each version.

You can write a script that checks which version of access is installed. You can use any number of criteria to determine this variable. Once the script determines the version, you can have it install the proper version of your db and/or runtime. The CD or whatever that you use to install from, will essentially contain all of the versions possible.

The script will only install the correct version.
This means that you only have to execute 1 script on each machine and the script can do the rest for you.

 

[Alisa]

The script will only install the correct version.

Yeah, the problem is, there IS only one version of my db, its in 2003, and I don't forsee making a 2007 version since there is no security in 2007.