Accessing Ms Access Database From A Centralized Location?

Hi I 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)

Comment/Reply (w/o sign-up)

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.

 

 

 

Comment/Reply (w/o sign-up)

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..??

Comment/Reply (w/o sign-up)

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.

Comment/Reply (w/o sign-up)

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.

Comment/Reply (w/o sign-up)

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

Comment/Reply (w/o sign-up)

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

Comment/Reply (w/o sign-up)

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

Comment/Reply (w/o sign-up)

CONNECT TO MS-ACCESS DATABASE IN SERVER FROM ANOTHER COMPUTER
Accessing 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.1\\Bd\\CENTRAL.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

Comment/Reply (w/o sign-up)

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...

Comment/Reply (w/o sign-up)