Connect to a remote Access 2010 database from Excel using VBA

Question

I am a beginner user of VBA and I want to connect remotely to an Access 2010 database from Excel using VBA.

I have found this connection string that works in local situations:

Public objCon As New ADODB.Connection
objCon.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ActiveWorkbook.Path & _
            "\asset_database.accdb;ACE OLEDB:Database Password=password;"

I wrote this string, but it does not work:

$ dim cnn as new ADODB.connection
$ cnn.open  "Provider=Microsoft.ACE.OLEDB.12.0;Remote Server=http://'remote_server_ip';" &_ 
$ "Remote Provider=Microsoft.Jet.OLEDB.4.0; Data Source=d:\dataBase/database_name.accdb"

What is the correct connection string to connect to a remote database?

Answer 1

You have to setup a 'shared folder' (by windows or samba server) at remote server. And use such 'Data Source=\\remote_server_ip\shared_folder_name\path_to_database_within_share\databace_name.accdb'.