Upsize, is it possible for me?? 1

[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

 

[cmmrfrds]

So, you have a back end on a server in NY with the front end on 7 desktops in the NY office. Are you planning on using Terminal Services for the Houston office where the front end is on a server in NY with a separate copy of the front end for each user that connects from Houston. You should go ahead and try it and see how it performs. It should perform okay since only keystrokes are sent and received from Houston.

The issue will be with printing since the app is actually running in NY. There are 3rd party products for this although I don't have the names. Search on the internet for these products.

A more costly solution is a Citrix server in NY that essentially takes the place of Terminal Services. You need a separate server for Citrix.

Going to SQL Server is probably not necessary unless the number of users is going to increase significantly, since it does not solve the remote issue.

Hope this gives you some ideas to start with.

 

[cityrock]

Hola cmmrfrds .
First thank you for your reply.
We were thinking of doing a VPN from NY to Houston, and link the front ends from all desktops (NY and Houston) to the server in houston. Now that I think of it , a terminal should work better since only key strokes go on the net, instead of all the data. What terminal software should I use, how will the image on my screen be?? The printing problems I think I can manage.
Thank you
Eli Gur

 

[cmmrfrds]

There is terminal services built into the windows 2000 operating system. I would experiment with that to start with. The 3rd party product that I am familiar with is Citrix, but if you can do it with Microsoft's built in terminal services the cost would be low compared to Citrix.

Perhaps other have some more suggestions.

Maybe you could put out another post asking about terminal services and what experiences people have had.

 

[IcesMaster]

Try SQL Server 2000 and you'll find easy upsizing from MS-Access 2000/2002 (in term only some querries can not upsized).

 

[cityrock]

Hi IcesMaster.
The upsizing wizard from access 2000 to sql only upsizes tables if I remember correctly. Alot (probably most) of my queries use a control on a form in their criteria, and by that cannot be upsized . Do I have to change the controls to pass parameters to the server?? I am not so familiar with sql, is there a tool that will help me upsize all kinds of queries from access?? Is there a better tool that will help me transfer a large database to sql??
Thanks in advance,
Eli

 

[cmmrfrds]

cityrock, you can certainly upsize if you wish, but if you are the only IS person then it is best to control the amount of new software that you are bringing into play at one time. I have done a number of Access to SQL Server implementation, but I do not think it is necessary for the number of users you have indicated. The biggest issue to solve first is the remote access issue and once that is working you can always convert to SQL Server. Upgrading to SQL Server is not going solve issues with remote connections and introduces a new set of software to learn. I believe it would delay and complicate getting the second office to use your access application - just my opinion.

 

[mfd777]

Just a note on upsizing to SQL server. I agree that you should not need to. If you do, you export tables to SQL server and then link to these tables. If the linked tables have the same name as the native access tables, then your application code/queries etc.. should not change. This will not improve performance however. To improve proformance you will need to change you queries to be pass-through to utilise server processing power.