Link VB to online Access database

[omair1051992]

I want to link Visual Basic Form to an Access database that is uploaded somewhere online, and not on the same computer.

I can easily link the VB Form to a database that is somewhere on the hard drive, but in this scenario the same Form, linked to the same one database, will be used by multiple people on different computers.

Is there a way I can upload the Access file somewhere and then link it to one VB Form, send the Form to all the different people and allow them to make changes simultaneously?

Thanks in advance

[omair1051992]

I use the following code to link the Access Database (Project Database), and its table (VendorDetailsTable) to the VB Form, when the .mdb file is on the same computer.

Dim mycon As New ADODB.Connection
Dim mytable As New ADODB.Recordset

Private Sub Form_Load()
mycon.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=ProjectDatabase.mdb"
mycon.Open
mytable.CursorLocation = adUseClient
mytable.Open "VendorDetailsTable", mycon, adOpenDynamic, adLockOptimistic

If mytable.BOF And mytable.EOF Then
MsgBox "There are no records in the database...", vbInformation, "Notice"
Else
SHOWDETAILS
End If

[Doogle]

You need to change the Data Source to reflect the location of the Database in the Network. Ususally in the form of:

Data Source=\\Server name\Folder\database.mdb

Where Server name is the name on the Computer (or its IP Address) where the database resides.

Be aware however, that Access is not particularly 'good' and managing large numbers of multiple simultaneous users. I personally wouldn't use Access for more than 5 users. You may wish to look st SQL Server 2008 Express which is freely available from Microsoft. Access databases can be imported into SQL Server without change (in most cases).

[omair1051992]

Thanks for the help. I will see if that works.

As for the simultaneous users issue, several people would use the database but probably not at exactly the same time. Would it still be a problem?

[Doogle]

It may turn out to be a 'suck it and see' type of thing. I guess your users will soon let you know if the performance is not up to much.

I have an application deployed over a LAN with the Access Database on a Server, and normally, 4 users. However, each user accesses a different part of the system so there's very few occasions where the same Table is used by more than one user at a time, so performance is acceptable.

Saying that, should the requirements change, then the plan is to migrate to SQL Server 2008 Express. But at the moment it's a case of 'if it isn't broke then don't fix it'. I have already migrated the database in a test environment and had to make about 2 code changes.

[omair1051992]

I'll keep that in mind when testing the program. Thanks for the help!

[omair1051992]

There is some kind of error in the original code. I uploaded the Access file on Google Docs and copied the link to the Data Source.

For the first line of the code, mycon etc, it says that the 'User-defined type is not defined'.

What should be changed?

[dilettante]

You can't just upload an MDB file to some random upload/download service and expect to connect to it.

You can only use a location to which local PCs have full and complete file sharing access, like a LAN file share or local drive.

Google Docs is not a file sharing facility in any meaningful sense, no matter how those kinds of providers try to market themselves. It's like trying to connect to an MDB file on a floppy sitting in a box in the attic.