Honesty Rocks! truth rules.

Accessing Ms Access Database From A Centralized Location?

HOME      >>       Programming

himanshurulez

HiI am a manager at a trading/wholesaling company (and have no programming background). I customized the Northwind sample access database to make invoices and keep accounts for my company. We now opening another office at a distant location. So, the order entry will be done at two points(we plan to use the same Access database).I am not able to figure out how to access the same MS Access database from two different location(as LAN can't be used). Moreover, we can't afford to pay huge sums to the software developers. Can intranet or uploading the database to a web server be a solution to this?? How?? If not, plz suggest something else?PS: I just need a central location where the database can be stored, and all employees can run and modify the database via internet (or some other source)


faulty.lee

What's the UI/Form you're using to do the entry of your data? Another or the built in form in access. My guess is the latter. In that case, there's not much option left. You need to built a VPN network so that the remote client at your new office will appear as if it's running in LAN. I would recommend Hamachi, since it has the a free version for small scale deployment. Once your VPN is up, you simply shared the access file across the network. This might not sounds like a very scalable idea, but since you can't afford software developers, that should be the only way out. Btw, this option also limit you to a few connection per session. Don't expect to connect/run more than 5 client from the remote location, unless you have very reliable and fast internet connection.If you already have another app to access your "access database", then simply modifying the connection parameter of that app will allow you to connect remotely. You'll need to setup some form of domain name for your main office's internet connection. Try No-Ip or DynDNS.Webserver won't help, unless you're running a app and you can modify it to connect to database server instead of ms access.Actually depending on where you're from. Software developer ain't that expensive here in malaysia.


himanshurulez

What's the UI/Form you're using to do the entry of your data? Another or the built in form in access. My guess is the latter. In that case, there's not much option left. You need to built a VPN network so that the remote client at your new office will appear as if it's running in LAN. I would recommend Hamachi, since it has the a free version for small scale deployment. Once your VPN is up, you simply shared the access file across the network. This might not sounds like a very scalable idea, but since you can't afford software developers, that should be the only way out. Btw, this option also limit you to a few connection per session. Don't expect to connect/run more than 5 client from the remote location, unless you have very reliable and fast internet connection.
If you already have another app to access your "access database", then simply modifying the connection parameter of that app will allow you to connect remotely. You'll need to setup some form of domain name for your main office's internet connection. Try No-Ip or DynDNS.

Webserver won't help, unless you're running a app and you can modify it to connect to database server instead of ms access.

Actually depending on where you're from. Software developer ain't that expensive here in malaysia.


Hi
Thanks for the reply...I tried ur solution...But I now facing another problem..actually i have used database splitter in MS Access to split the database...now access does some internal coding to link the forms in the spitted database to the tables database...now how do i link the splitted database in the remote location to the tables database in the main server..??

faulty.lee

HiThanks for the reply...I tried ur solution...But I now facing another problem..actually i have used database splitter in MS Access to split the database...now access does some internal coding to link the forms in the spitted database to the tables database...now how do i link the splitted database in the remote location to the tables database in the main server..??

It's the same. As long as both the files are in the same folder, it would works. So when you share the folder, with both in it, it should works. I've not tested with a split database before, but I did run with a single database file shared using file sharing, and it works for quite a while now. If you face problem with the link, try map the folder as a drive, say W, so that plugging in thumbdrive won't effect it later on. Then open up the access file with the form, rebuilt the link (tables) from the mapped drive. Do the same mapping for all machine. I believe that should solve your problem, if the earlier solution doesn't work.

Btw, if it works in your lan enviroment before, running under vpn won't be much diff.

darasen

Some how I missed this post when I really should have replied to it. First off I so not know exactly how the DB is being used or how it is currently set up. Nor Do I know how many users are on the DB a each location. Thus I will have make some generalizations. If your main office has a LAN and you are using an Access application over that LAN that is all well and good. Now your company has decided to open a new office though it seems that the technical considerations have not all been taken into account. That is less good. Commonly in these instances a company will use a high-speed internet provider and VPN to connect the two offices forming a pseudo LAN. I do not suggest trying to run an MS Access application with a standard client/server configuration using the JET engine over a VPN connection. While this may seem like an easy solution in my professional opinion it is a poor idea. For one the speed of the connection is going to be a major bottle neck. If, for example, you have a super fast internet connection at 10 mbps that is still going to be only a tenth of the transfer rate of a modern LAN. Thus take any transfer time from the current setup in your office, times that by ten and that is the response time the new office can expect. (Note: 10 mbps is being extremely generous and based on your description probably closer to 3 mbps or slower.) Another bad thing about using a VPN with the JET engine is that it simply is not designed to run that way. One of the weaknesses of the JET engine over a VPN idea is that in the event of a disconnect between the two offices during a data transfer the data will become corrupt. Hopefully that idea is out of play now. Now let us come up with some solutions. One of the first things that needs to take place is dumping the JET database engine. You do not need to do any as fancy as upgrading to MS SQL Server or Oracle or even MySQL. Your Office CD also has on it the rather useful MSDE (Microsoft Database Engine), or use MS SQL server Express; Both are free. The advantages, for your use, are going to be nearly the same as using the full scale SQL server with out the costs and with out the limitations inherent in the JET engine for VPN use. The way the more robust engine works will ease the strain of using a slower connection and eliminate the concerns for file corruption. You can still use the exact same Access front end as well. a small bit of easy coding and your application is set to go. Another possible solution depends greatly on how exactly your database and your business is set up. If location A deals with customers X,Y, and Z while Location A deals with A,B, and C and up to the minute data synchronization is not factor you have another option. You can run a separate database back end at both locations then merge the data when needed, possibly nightly or weekly. This is called replication and would require a bit more programming work. I have heard of people using Terminal Services to run Access over but I have no experience in so doing. Also, of course, you can convert the entire project to a web based application. Lastly if the information in the DB is important and your company takes itself seriously then hire a professional to set it up. I would be willing to bet that the chairs in the conference room cost more each than an hour of an Access developers time. I hope this information is useful to someone.


iGuest

Connect an MS ACCESS db using VB6 over a VPN connection

Accessing Ms Access Database From A Centralized Location?

 

For the first time in my life, I am asking a question to a forum. With extreme hope that you will solve my problem, I am describing you the matter.

 

I have written an application in visual basic 6 that runs in 8 machines under a Local Area Network and accesses a MS ACCESS db. The program is running fine. But, now my client has decided to set up a new branch at a distant location which should be connected to the existing branch. How can I modify/extend my application to handle that situation. I would like to suggest my client to use a VPN connection to connect two branches.

 

Please help me.

 

-question by Srikumar Maitra


iGuest

Centralized MS AccessAccessing Ms Access Database From A Centralized Location?

Hi ,I want to know about access of centralized MS Access database using java applications.How can I do it?

- Rakesh


rnd-am

Centralized MS Access

Accessing Ms Access Database From A Centralized Location?

Hi ,I want to know about access of centralized MS Access database using java applications. How can I do it? - Rakesh


Hi,

 

You have to create DSN (DAta Source name) using ODBC manager of Windows Control Panel, and then from Java connect to it using Java's JDBC and passing your newly created DSN as connection string parameter to connect() function


iGuest

CONNECT TO MS-ACCESS DATABASE IN SERVER FROM ANOTHER COMPUTERAccessing Ms Access Database From A Centralized Location?

I need to connect a MS-acces in a server in my lan but it cannot find it ,  I don't know what to do

$pathDB = str_replace("/", "", "10.0.0.1BdCENTRAL.Mdb");

 if(!file_exists($pathDB)) { echo "! Base de datos no encontrada ".$pathDB; exit; } $DNS  =  "DRIVER={Microsoft Access Driver (*.Mdb)}; DBQ=".$pathDB; $conexion = odbc_connect($DNS, "aaa", "bbb"); $sql="select Desc_Producto from alm_producto where 1"; $resultado=odbc_exec($conexion,$sql); if($resultado) {  while($fila=odbc_fetch_array($resultado))  {  echo $fila['Desc_Producto']."<br />";    } odbc_close_all(); } 

-question by Rita Fonseca

iGuest

I have shared a MS Acess Date Base on LAN and one of LAN client can not access this Data Base (after installing windows again and configuring on domain), reply me in this regards.Accessing Ms Access Database From A Centralized Location?

I have shared a MS Acess Date Base on LAN and one of LAN client can not access this Data Base (after installing windows again and configuring on domain) but after maping the location of ms access db client can open that db and can't open directly from shared forlder in which I kept ms access db, reply me in this regards.

I think there is a flaw in domain configuring. Is anybody guide me that how I can resolve this matter??? I shall be thankful...


iGuest

how can i access stored passwords in Ms acsess using java?Accessing Ms Access Database From A Centralized Location?

how can I access stored usernames and passwords for several users in Ms access using java instead of defining them in java code?

-question by mike

iGuest

it is a great post. And it leads me to more ideas. Thank you all very much


iGuest

Dont you read the original post??Accessing Ms Access Database From A Centralized Location?

This always happens, people don't read the original post (or they only read what they want!) This guy doesnt know anything about coding, programing, or even manipulating an access database, he just customized the northwind database, but he doesnt know even how to link a front end an back end on Access. Based on that, is not a good answer to tell him "..A small bit of easy coding and your application is set to go"!! Please!

Access is a great tool because it alows non-programmers to develop great applications. I didn´t knew ANYTHING about programming (not even VBA) until 2008, but I have made access aplications since 1998 for companys running as much as 16 users. Sadly, there is no application that can do on web what access did in LAN. I don´t know why there is no one intelligent enough to create a really VISUAL application, I mean where a non-programmer can simply design the forms like we do on Access.

MySQL, SQL Server , all that is great, but to make your forms ( I mean sophisticated forms) on web you have to program on php or visual basic or anything else, and this means: if you made a sophisticated form on Access and it took you 2 hours, now you have to program the same form (on PHP, ASP, JAVA or anything else),  which will take you 6 hours!! , so may programmers, hackers, etc. And no one can solve this. To Bad.

-reply by javier

 


iGuest

Sharing the database .mdb file on LANAccessing Ms Access Database From A Centralized Location?

I am working with windowXP and MS Access 2003. I have kept the file in a Folder.And by using the properties of the folder, converted it as shared folder. Using tools menu and security options, I have created a new workgroup file and given permission to all users of user group to open the database and read tables and open form / report and macro etc. The problem is that once some user open the database, all other get the locked file and database can not be open in any other computer except at the master computer where the database folder was converted to shared one.In that case also the records are not locked. Conflict message is seen.

Please suggest some solution to make the database usable by 3 or 4 persons on their computers with different permissions to read only or add and edit data etc.

Thanks in anticipation.

G. P. Dixit, New Delhi


iGuest

ms ACCESS file to be shared over the internetAccessing Ms Access Database From A Centralized Location?

For the first time in my life, I am asking a question to a forum. WithExtreme hope that you will solve my problem, I am describing to you theMatter.I have written an application in visual basic 6 that accesses a MS ACCESSDb. The program is running fine. But, now my client has decided to setUp a new branch at a distant location which should be connected to theExisting branch. How can I modify/extend my application to handle thatSituation. Ineed all the users to use the same database file. How do I do it .

Thnks in advance. Your help will be highly appreciated

 

-question by Bryt