Sharing access database

[BOAC]

I have a friend who wishes to share an Access 2007 d base with another (remote) so that either can work on it. I am a bit out of my depth here, but to me it appears that MS Sharepoint requires Windows Server installed, which is not appropriate really. I have suggested either peer-to-peer internet access with a shared folder or FTP'ing the file to a server for download. Am I being over-simplistic and does anyone have a nifty and simple solution?

[Jofm5]

If you can create a virtual private network (VPN) so the remote machine has access to the local network you can leave the mdb on the same box/share and it should work fine.

It may be easier from a networking point of view to use a SQL server such as MySQL or SQL Server express - however if an access database is essential then your options are limited.

[BOAC]

Thanks Jof - I detect 'the parties' are hooked on Access, so I guess 1) will be the answer. Would the server FTP plan work?

I do not use Access so can anyone tell me which files need to go onto the server - let's say using the Northwind db as a case?

[phineas]

If the machine you are trying to connect to has a fixed IP, and you are prepared to share the dirve then it may be possible to connect via the internet, see

Accessing Data with ADO (http://msdn.microsoft.com/en-us/library/ms524771.aspx)

the connection here is a physical drive, this would require a VPN and a drive mapping, it should be possible to pass the IP of the machine on which the database is. the issue here is: does the IP have to be in the same domain as the machine

You could get lucky and find some code on the web you can adapt or you could spend a week an discover it is not possible.

[Xtiff]

All required files are contained in the XXX.MDB file.
ie Tables ,Queries, Forms etc.
This is the only file needed to be transferred

[BOAC]

Thanks both - (phineas- liked the last bit:)). I have followed xtiff's line and uploaded the mdb to my server so we'll see how friend gets on with that.

[Gertrude the Wombat]

A typical Access application designed for sharing traditionally consisted of at least two mdb files, one containing the data, which is put on a shared disk, and the other containing the user interface, which is put on each machine that wants to use it. This was because loading all the forms, code, etc round the network was unnecessarily slow ... but this may not be an issue with modern networking.

If the application hasn't been written with the intention of being multi-user then it will mostly work most of the time, but every now and then something will go wrong, when two people try to do things to the same data at the same time. "Go wrong" will at best be an incomprehensible error message and try again in a minute, at worst a mangled database.

Exposing an mdb file over the internet via a share exposed over the internet doesn't sound too clever to me if you haven't set up a VPN. I wouldn't like to think about the security issues - I'd choose a different approach.

What you probably want to do is put the data into MySQL running on a server somewhere. That doesn't stop the application itself being in Access - an application in Access can have its back end pointed at a MySQL database instead of data tables in an mdb file, thus retaining the existing user interface and development environment. This (mostly) solves the "hooked on Access" issue.

[Bushfiva]

VPN: If you sign up with a free/paid (depending on what your needs are) service like Hamachi, you'll get a fixed IP address per machine. I use Hamachi a lot; it's pretty much zero configuration. In broad terms, you can use the free version unless throughput is low, in which case try the paid version. Encryption is via SSL, so if you trust SSL, you can trust Hamachi.

There are alternatives, of course.

[BOAC]

Thanks BF - talking to No 1 son re SSL at the moment and waiting to hear back from friend about options.

[Jofm5]

BOAC using an FTP server is not really an option, you would have to download the mdb update it and then re-upload it.

I would suggest to your son to download SQLServer express edition, as gertrude says you will be able to redirect the front end to it quite easily. There are also upsizing wizards available and plenty of reference material available (How do I upsize from Access to SQL Server? (http://sqlserver2000.databases.aspfaq.com/how-do-i-upsize-from-access-to-sql-server.html)).


SQL Server Express edition is free but will limit your database to 4gb in size - if your project takes off you can buy a licence for SQL Server and get the full edition without restriction (No changes required to access front end).

If you have downloaded SQLServer express you only need to look at using the "Link External Tables" option and the rest of the development is pretty much the same. Feel free to PM me if you want more info on SQL Server or if you have any problems (Saab if its of benefit to the forum will paste any answers here also).

[BOAC]

Jof - we are looking at a scripted FTP cycle (or even a Windows Gui - I'd have a go!) for simplicity as a primary option at the mo. Brainstorming tonite (if I can find mine).

[phineas]

This should gve you the code to write an FTP app in Excel, Access or VB.net

SAMPLE: VBFTP.EXE: Implementing FTP Using WinInet API from VB (http://support.microsoft.com/kb/175179)

It's designed to work with VB 6 so it may take a bit of hacking, but I don't think it is impossible.

Best of luck,

[BOAC]

Thanks all so far - a brief hiatus while we look at options. I think a secure folder with scripted up and download is favourite right now.

[BOAC]

Update: at the moment we have 2 batch files to up and download the dbase. Obviously minimal security due to username and password being passed on one of the lines. Can any bat file experts offer a way to prompt the user to enter the password please?

It would also be good to call MS Access AFTER download is complete. I cannot find a batch file command to wait for execution.

[Jofm5]

BOAC

If you use %1 %2 %3 etc as variables it will substitute when running with parameter 1 .... xxx

e.g.

The contents of Dataload.bat could be


@echo off
cls
bcp test..test in %1 -S. -U%2 -P%3 -c -t,


If I then called it with the following command line:-

Dataload.bat loadfile.csv myuser mypassword

%1 would be replaced with the filename
%2 with myuser
%3 with mypassword

Put parameters in quotes (") if they contain spaces.

It would also be good to call MS Access AFTER download is complete. I cannot find a batch file command to wait for execution.

A batch file will wait until the ftp application is complete if it is a console application. So it sounds like you are executing a forms application which will not perform the same way.

You can either switch to using a proper command line ftp application (NT has its own type ftp /? in a command prompt) or you could pause your batch file using the PAUSE keyword which will ask the user to "Press any key to continue ..." and will then continue.

[BOAC]

Thanks Jof - I am trying to avoid having a password 'stored' on the machine (unencrypted) so thsat the user has to type it in each time. If I read your idea properly the password would be in a csv file, so I gain no security over having it in the batch file lines?

The file is to be run via a desktop icon, but at the moment I am testing it via the 'Run' command in XP. I don't think I'm making a 'form'! It does not halt while the ftp transfer takes place before calling 'access' at the moment and I have already tried 'pause' with no effect. I have tried it after the 'upload file' line and it just goes straight past while uploading.

This is the relevant part of the file so far

::Variables
set f=%temp%\ftpc.txt

echo open THE.FTP.SERVER.IP>%f%
echo user USERNAME PASSWORD>>%f%
echo binary>>%f%
echo cd REMOTE_DIRECTORY>>%f%
echo cd Out>>%f%
echo lcd LOCAL_DIRECTORY>>%f%
echo put FILE_NAME>>%f%

Tried pause here, then

echo MSACCESS PATH
blah blah

Just looking at the ftp command now.

[Jofm5]

BOAC,

My idea was not to store it in a csv fie, my idea was to parameterise your batch file

then when you want to run it you open a command window and then just type in:-


C:\> Batchfilename username password


As far as I know there is no obvious way without downloading an application to prompt you for those details in a popup window.

By forms, I meant that the ftp application you are using is not a console application but a windows application. A windows application will return the thread of execution back to windows after start as they are written to run in a multi-tasking manner - whereas a console application will hog the thread of execution until it terminates - thus preventing the batch file continuing until its completed.