front end through the internet? possible?

[cityrock]

Hello All,
I am in a complicated situation....
I built a pretty nice access Db to our office, which has a back end on a server (not a real server, just a computer, the others connect to by linked tables...). We have around 7 users using it at all times.
Now the Db got more and more complicated (patches on patches, queries on queries etc..). Today the Db is very complicated, using loads of Qrys,Frms and rpts... Lets not forget big tables with loads of data.
Now here begins the problem, we have another office in Houston (our first office is in NY), that we decided its about time to connect to the database for full front end capabilites as the NY users. We have high speed internet, and will try to build a VPN that will let us connect fron ends at both locations to a server with the back end at NY.
First question is : Do I upsize to Sql server. If so, how do I do it, when most of my queries use controls on forms as criterias?? Also I am the only one that knows anything about computers in the office, and I can tell you I know alot about access, but very little about SQL. For me to upsize to SQL (all my queries) looks like a mission impossible, unless I rebuilt all queries from scrath.
Second question : can anyone give me any tips how can I keep a good connection speed, if I dont upsize, and still want to link the db back end to a frontend through a vpn over high speed internet. How would it be best to use temp tables, update queries and basicly minimum use of tables on the back end at real time.

Ofcourse all help will be very much appreiciated..
and sorry for spelling mistakes
Thank you
Eli

 

[JeremyNYC]

Eli,

There are several people who hang out at comp.databases.ms-access who have tried things like this. I don't think any of the results have been stellar, but my memory is that some of them have been close to acceptable. Access is a pipe hog, and I personally wouldn't want to try to get it to work in any of the set ups you mention.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.

 

[cityrock]

Jeremy,
Have any good suggestions?? At the moment I will do everything possible to make my boss happy in NY, and get the two offices togather..
Tnx in advance
Eli

 

[JeremyNYC]

Eli,

I've never done it, so I wouldn't know what to tell you. But if you go to groups.google.com and search through cdma's archive, you'll find a lot of stuff there. Probably best to do some thorough searching before posting on it, as it's been discussed a lot, and people there get touchy about going over the same thing too many times.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.

 

[jovoian]

you can but if you are not a SQL person it may be a bit difficult...
you could always hire a copnsultant off computergeeks.com to help install and import access to sql , but it is expensive, for Microsoft software, you could also set up linux with apache, MYSQL, which is all free, it iwll even run on NT/2k server and its still free, unless you want product support... then have some one ( again hire consultant, there are lots of talented IT prog unemployed ( like me , but i am not a DB guy) and have them write a php web interface that you can use to access the DB via a browser with or with out ssl or on itranet via vpn..

it will run like an expensive oracle DB with out the cost

hope this gives some ideas

 

[SgtJarrow]

Not exactly sure what your resources are like, but if you are using a connection similar to Citrix MetaFrame, you can have the users connect into your network in NY from TX and then run just the db through this connection. MetaFrame connection only passes key stokes/mouse clicks/video display over the connection.....all processing is done on the server connected to. For that matter, any true Terminal Service app, such as Windows 2000 Server set up with Terminal Services, might be able to get you into the same boat.

I put together Citrix setups for some of our users ( 4 remote sites in four states, all connecting to the same db at the home site) in the past...the connection, when established only opened the db in question, and the users never complained about any lag issues... Only two things are infinite, the universe and human stupidity, and I'm not sure about the former. (Albert Einstein)

Robert L. Johnson III, MCP, Network+, A+
Access Developer/Programmer
robert.l.johnson.iii@citigroup.com

 

[KUZZ]

I was thinking the same thing as MSTRMAGE1768

If you can create a VPN you could most definetly have the people in Houston open the database as if they were opening it from the office next door, provided the fact that the database is not split in front and back end, but it is all in one file. And then for data backup purposes you could import your tables to the backup file every day or other period at your discression, and every now and then you'd just have to backup your overall database design. The only thing that your users in Houston will need, is the system.mdb file prepared by you so that after copying it in the proper MS office directory on their local machine, they would have the proper permissions to ented the database.

Good luck,
Kuzz

"Time spent debating the impossible subtracts from the time during which you
can try to accomplish it."

 

[headachesRme]

Hello there Cityrock, Jeremy et al. I have the SAME EXACT problem, (except the city locations are different) so, first I was wondering-Cityrock-did you ever fix this and how? This is a frustrating thing for us too, and we are on a fairly limited budget so getting 10 programmers to rewrite the DB to SQL is not an option). I was hoping that someone out there knows of a way to get a VPN connection to make the "server" computer holding the Access DB at the other end to do all the other work to speed things up a bit. This would be the best of all worlds, without using Citrix or GoToMyPC.

I tried first to create a VPN between the two offices that are running the same "patched and over complicated database" however as was discussed in the thread, Access is a "tunnel hog", and rather simple queries got too bogged down to allow this method to be accebtable. Then I tried a product "like" Citrix called "go to my PC" however users complained that the delay of cursor movements was very annoying (there is anout a 2 second delay between mouse movements and cursor movements-I could have lived with this but no one else could).

FYI ladies and gentlemen, rewriting this enormously over-queried database with forms and reports on queries about 10 layers thick really is not an option in the immediate future. Does anyone have any quick fix answers? CityRock, what did you end up doing and how is it working?

Thank you all for an excellent forum and thank you in advance for your help. I am crossing my fingers that someone has the answer!

 

[headachesRme]

Just wanted to add that I did not understand 100% Kuzz's post saying
"If you can create a VPN you could most definetly have the people in Houston open the database as if they were opening it from the office next door, provided the fact that the database is not split in front and back end, but it is all in one file."

What do you mean, "one file"? One .mdb file? What do you mean by "front and back" end? When I get to the source db file, I can NOT seem to access individual tables or quereies (I assume this is what you meant???) All I see is the ***.mdb file. Is there a way to make the other computer (the source or server computer) do all the work through the vpn, and the "guest" simply sees the end result? This would probably make it a lot faster but then are we limited to the number of users that can be on there at on time? I already have the vpn set up so this may be an easy solution if I can understand it better.

Please clarify if possible-thanks.
BigHeadache

 

[cityrock]

Hello BigHeadAche
First, I want to tell you I have completly forgot about this thread, since it was over half a year when I wrote it.
I have long time ago solved my problem, and can tell you that after checking ALL other possibilties, there is no doubt, for my case this is the best :
Terminal services.
Configuring the server where the back end sits into a terminal server, lets users log in with terminal services.
Now while the basic idea is same as pcanywhere, or gotomypc, terminal services is 100% faster. It is actually amazing, my users work on the server, but dont even see the diffrence, as if they where working on their local computer. You can get more info about terminal services, from microsoft, or from the forums here.
For me it was the best solution, and now fater half a year of work, I can tell you I have 6 users in NY, connected 10 hours a day to the server in Houston with almost no problems at all.
We did get a T1 internet connection for the server, but I heard terminal server will work great with smaller connections as well.
Thats it, hope it helps
CityRock