How to access remote MS Access database

[RhinoBull]

Many people are asking if it is possible to connect to remote MS Access database. Well, I can say it is possible but a bit tricky - you must have IIS running on the server, plus some configs for ado library on the server are also in order.
Here is the general idea on coding:

Option Explicit
 
Dim adoConn As ADODB.Connection
Dim adoRst As ADODB.Recordset
 
Private Sub Command1_Click()
'============================
Dim strConString As String
Dim strSQL As String
 
    'assign connection string
    strConString = "Provider=MS Remote;" & _
                   "Remote Server=http://192.168.1.1;" & _
                   "Remote Provider=Microsoft.Jet.OLEDB.4.0;" & _
                   "Data Source=MyRemoteDB;Persist Security Info=False"
    
    'initialize connection object variable
    Set adoConn = New ADODB.Connection
    'open connection
    adoConn.Open strConString, "admin", ""
    
    strSQL = "Select * from Orders"
    
    'initialize recordset object variable
    Set adoRst = New ADODB.Recordset
    With adoRst
        .Open strSQL, adoConn, , , adCmdText
        If Not .EOF Then
            Do While Not .EOF
                'read each record here
                '...
                .MoveNext
            Loop
            .Close
        End If
    End With
    
    'destroy recordset object if necessary (or do it when you unload the form)
    'Set adoRst = Nothing
    
    'destroy connection object if necessary (or do it when you unload the form)
    'Set adoConn = Nothing
 
End Sub

For information on how to cofigure IIS refer to the following MSDN article:
http://support.microsoft.com/kb/q253580/

I hope this sample is going to be usefull.

[Hiks]

Thanks for the help RhinoBull.

Just one question, what is the limitation in terms of concurrent connections. In our situation, we would be OK with at most 3 - 5 connections until we get the go ahead to upgrade the DBMS.

[RhinoBull]

Welcome to Forums, Hiks!

I think that could be configured @ IIS level not to mention that your application must be able to handle multiusers.

[fifo]

sorry, i try your code but it doesnt work. Some error occurred. Please show me your code more detail .

[si_the_geek]

I suggest you expand on "Some error occurred.", as we have no idea what error you got - and hence no idea of a solution.

[icongroup2003]

I had a problem trying to get the connection to work. When my code run into :

.Open strSQL, adoConn, , , adCmdText

It pop out a runtime error :
Run-time error '-2147024664 (800700e8)'
Unexpected error. ()x800700e8)

Can anyone help me out on this ???


Thanks.

[lramshankarfree]

Dim strConString As String
Dim strSQL As String

'assign connection string
strConString = "Provider=MS Remote;" & _
"Remote Server=http://192.168.1.1;" & _
"Remote Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=MyRemoteDB;Persist Security Info=False"

'initialize connection object variable
Set adoConn = New ADODB.Connection
'open connection
adoConn.Open strConString, "admin", ""

by eht eabove code provided by your now the connection strign wise it si working .
while opening the tanble is is showing as runtime error 8309.internet server error object/module not found error i ma getting
once again thanks in advance

[lramshankarfree]

Set adoRst = New ADODB.Recordset
With adoRst
.Open strSQL, adoConn, , , adCmdText



Run time error -8209
internet server object/module not found.

[mandopepo]

Hi i tried this code and i got an internet server error error code='8447'. What would be wrong?

My conection string is:

"Provider=MS Remote;Remote Server=http://localhost;Remote Provider=Microsoft.Jet.OLEDB.4.0;Data Source=db.mdb;Jet OLEDB:Engine Type = 4;Jet OLEDBatabase Password = xxxx"

[si_the_geek]

Try changing 'localhost' to the IP address (127.0.0.1).

If that doesn't solve it, try also specifying the full path to the database (eg: "Data Source=c:\App1\db.mdb").

[shrekho]

Hi

I have the current situation like this.

As a SME, our resources are limited.
There isn't any system that is ruuning on IIS,
and all I am having right now is a Lan Server.
The lan server is those hardware boxes with firmware, and you need
to do is plug in a harddisk.

My boss wants me to develope a service/database system
to connect to the database, without any IIS services installed.

Also, he wants to be able to remote access it from overseas
(i think it is impossible) .

Is it possible to connect to the database from LAN on the file server?
How feasible is it to connect the database from over seas?

Thanks in advance

[si_the_geek]

For connecting to Access from outside the network you would need to follow the method above, so IIS needs to be installed (within the network it is much easier, see this FAQ thread).

For other DBMS's (such as SQL Server) connections like this are much easier, as they are designed to be used over networks. There are potential cost issues, but SQL Server Express (which is free) may be enough for you.


The above method is designed to connect via the internet, so working from overseas is certainly possible.

[Laforge]

I have a virtual directory created with all permissions (allow read, write, list, ...) and still receive the error:

Microsoft ADO/RDS (0x800A2011)
Internet Server Error: object/module not found

I'm usign this connection string:
"PROVIDER=MS Remote;REMOTE SERVER=http://2X2.3X.X3.XX6;REMOTE PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=VirtualFolder/Database.mdb"

What's wrong with that?

Thank you.

[soumitra1]

Many people are asking if it is possible to connect to remote MS Access database. Well, I can say it is possible but a bit tricky - you must have IIS running on the server, plus some configs for ado library on the server are also in order.
Here is the general idea on coding:

Option Explicit
 
Dim adoConn As ADODB.Connection
Dim adoRst As ADODB.Recordset
 
Private Sub Command1_Click()
'============================
Dim strConString As String
Dim strSQL As String
 
    'assign connection string
    strConString = "Provider=MS Remote;" & _
                   "Remote Server=http://192.168.1.1;" & _
                   "Remote Provider=Microsoft.Jet.OLEDB.4.0;" & _
                   "Data Source=MyRemoteDB;Persist Security Info=False"
    
    'initialize connection object variable
    Set adoConn = New ADODB.Connection
    'open connection
    adoConn.Open strConString, "admin", ""
    
    strSQL = "Select * from Orders"
    
    'initialize recordset object variable
    Set adoRst = New ADODB.Recordset
    With adoRst
        .Open strSQL, adoConn, , , adCmdText
        If Not .EOF Then
            Do While Not .EOF
                'read each record here
                '...
                .MoveNext
            Loop
            .Close
        End If
    End With
    
    'destroy recordset object if necessary (or do it when you unload the form)
    'Set adoRst = Nothing
    
    'destroy connection object if necessary (or do it when you unload the form)
    'Set adoConn = Nothing
 
End Sub

For information on how to cofigure IIS refer to the following MSDN article:
http://support.microsoft.com/kb/q253580/

I hope this sample is going to be usefull.

Thanks RhinoBull,

I am going to try this. If any problem face i shall write you back. Thanks again

[JoshuaAnger]

hi,
Many people are asking if it is possible to connect to remote MS Access database. Well, I can say it is possible but a bit tricky - you must have IIS running on the server, plus some configs for ado library on the server are also in order.

[luca90]

Many people are asking if it is possible to connect to remote MS Access database. Well, I can say it is possible but a bit tricky - you must have IIS running on the server, plus some configs for ado library on the server are also in order.
Here is the general idea on coding:

Option Explicit
 
Dim adoConn As ADODB.Connection
Dim adoRst As ADODB.Recordset
 
Private Sub Command1_Click()
'============================
Dim strConString As String
Dim strSQL As String
 
    'assign connection string
    strConString = "Provider=MS Remote;" & _
                   "Remote Server=http://192.168.1.1;" & _
                   "Remote Provider=Microsoft.Jet.OLEDB.4.0;" & _
                   "Data Source=MyRemoteDB;Persist Security Info=False"
    
    'initialize connection object variable
    Set adoConn = New ADODB.Connection
    'open connection
    adoConn.Open strConString, "admin", ""
    
    strSQL = "Select * from Orders"
    
    'initialize recordset object variable
    Set adoRst = New ADODB.Recordset
    With adoRst
        .Open strSQL, adoConn, , , adCmdText
        If Not .EOF Then
            Do While Not .EOF
                'read each record here
                '...
                .MoveNext
            Loop
            .Close
        End If
    End With
    
    'destroy recordset object if necessary (or do it when you unload the form)
    'Set adoRst = Nothing
    
    'destroy connection object if necessary (or do it when you unload the form)
    'Set adoConn = Nothing
 
End Sub

For information on how to cofigure IIS refer to the following MSDN article:
http://support.microsoft.com/kb/q253580/

I hope this sample is going to be usefull.

Hi friend...
I have an IIS server installed and run correctly (i us asp page to connet the and mdb) but interesting to connect on this server via VB 6.0.

Question:

1) where is the dir to copy the mdb? (...\\inetpub\wwwroot\test.mdb)
2) you have insert in ope rset "admin" wath is refred this psword?

Note:
with your example uin other case have error run time 8447 and use i this code:

Option Explicit
Dim adoConn As ADODB.Connection
Dim adoRst As ADODB.Recordset
Private Sub Command1_Click()
'============================
Dim strConString As String
Dim strSQL As String
'assign connection string
strConString = "Provider=MS Remote;" & _
"Remote Server=http://10.220.238.25;" & _
"Remote Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=DUE.mdb;Persist Security Info=False"
'initialize connection object variable
Set adoConn = New ADODB.Connection
'open connection
adoConn.Open strConString, "sal", ""
strSQL = "Select * from scheda"
'initialize recordset object variable
Set adoRst = New ADODB.Recordset
With adoRst
.Open strSQL, adoConn, , , adCmdText
If Not .EOF Then
Do While Not .EOF
'read each record here
'...
.MoveNext
Loop
.Close
End If
End With
'destroy recordset object if necessary (or do it when you unload the form)
'Set adoRst = Nothing
'destroy connection object if necessary (or do it when you unload the form)
'Set adoConn = Nothing
End Sub

[malequen]

sorry for being out of topic. but can i try this with just 1 comp ? i change the ip address so it will same for both client and server. i already try sending with winsock and it turn out no problem at all. but when i try for this code, it came out error - '8447': internet server error.
what happen ?

[CatchItBaby]

How i can Import My MsAccess Database into My hosting ?

[si_the_geek]

Please do not reply to CodeBank threads unless you have a question about the code which is shown - other questions belong in your own thread(s), which in this case you have already got.

[CatchItBaby]

same error happen to me also

Runtime Error :- '8209'

Internal Server Error : Object/Module not found

I gave the Full path of MsAccess Database but problem not solved

[si_the_geek]

As explicitly stated in the first post, you must have IIS running on the server, and it must also be configured correctly.

As your web site is hosted on what seems to be a Linux based host, it almost certainly does not have IIS. Even if it does, there is almost no chance that you have the permissions to configure it.

[Condomx]

i got a error "Internet Client Error:Cannot Connect to Server" anyone help please?..

[Eliminator2009]

The locks are not defined, cursor location is not defined. which locks at client side which cursor location at client side.

[si_the_geek]

The locks/options/cursor location are not specific to this issue, and you should be able to use whichever suits your situation.

Due to the fact that those properties all have default values, the example code does actually set them all (lock=read only / options=forward only / cursor location=server), and in terms of speed those are the best values.

[rahulcjain]

Dim db As New ADODB.connection

Private Sub Form_Load()
Dim Rs As New ADODB.Recordset
Set db = New ADODB.connection
db.CommandTimeout = 240
db.CursorLocation = adUseServer
db.Open "Provider=MS Remote;Remote Server=http://204.27.57.73;Remote Provider=Microsoft.Jet.OLEDB.4.0;Data Source=mbs.mdb;Persist Security Info=False;Jet OLEDBatabase Password = 'SUNSHINEINDIA'"
Set Rs = New ADODB.Recordset
Rs.CursorLocation = adUseServer
Rs.Open "Select * from accountmaster", db, adOpenForwardOnly, adLockReadOnly
End Sub

I too getting the same error called Run Time Error 8209
Internet Server Error : Object/Module not found..

[rahulcjain]

Hi pls help me how to connect to web server..