How to access remote MS Access database-VBForums
Results 1 to 26 of 26

Thread: How to access remote MS Access database

  1. #1

    Thread Starter
    PowerPoster RhinoBull's Avatar
    Join Date
    Mar 2004
    Location
    New Amsterdam
    Posts
    24,135

    How to access remote MS Access database

    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:
    VB Code:
    1. Option Explicit
    2.  
    3. Dim adoConn As ADODB.Connection
    4. Dim adoRst As ADODB.Recordset
    5.  
    6. Private Sub Command1_Click()
    7. '============================
    8. Dim strConString As String
    9. Dim strSQL As String
    10.  
    11.     'assign connection string
    12.     strConString = "Provider=MS Remote;" & _
    13.                    "Remote Server=http://192.168.1.1;" & _
    14.                    "Remote Provider=Microsoft.Jet.OLEDB.4.0;" & _
    15.                    "Data Source=MyRemoteDB;Persist Security Info=False"
    16.    
    17.     'initialize connection object variable
    18.     Set adoConn = New ADODB.Connection
    19.     'open connection
    20.     adoConn.Open strConString, "admin", ""
    21.    
    22.     strSQL = "Select * from Orders"
    23.    
    24.     'initialize recordset object variable
    25.     Set adoRst = New ADODB.Recordset
    26.     With adoRst
    27.         .Open strSQL, adoConn, , , adCmdText
    28.         If Not .EOF Then
    29.             Do While Not .EOF
    30.                 'read each record here
    31.                 '...
    32.                 .MoveNext
    33.             Loop
    34.             .Close
    35.         End If
    36.     End With
    37.    
    38.     'destroy recordset object if necessary (or do it when you unload the form)
    39.     'Set adoRst = Nothing
    40.    
    41.     'destroy connection object if necessary (or do it when you unload the form)
    42.     'Set adoConn = Nothing
    43.  
    44. 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.

  2. #2
    New Member
    Join Date
    Oct 2005
    Posts
    1

    Re: How to access remote MS Access database

    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.

  3. #3

  4. #4
    Lively Member fifo's Avatar
    Join Date
    Dec 2005
    Location
    HaiPhong, Vietnam
    Posts
    77

    Re: How to access remote MS Access database

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

  5. #5
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    37,141

    Re: How to access remote MS Access database

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

  6. #6
    Lively Member
    Join Date
    Sep 2004
    Posts
    86

    Re: How to access remote MS Access database

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

    VB Code:
    1. .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.

  7. #7
    New Member
    Join Date
    Aug 2006
    Location
    india
    Posts
    5

    Re: How to access remote MS Access database

    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

  8. #8
    New Member
    Join Date
    Aug 2006
    Location
    india
    Posts
    5

    Re: How to access remote MS Access database

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



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

  9. #9
    New Member
    Join Date
    Jan 2006
    Posts
    1

    Re: How to access remote MS Access database

    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"

  10. #10
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    37,141

    Re: How to access remote MS Access database

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

  11. #11
    Junior Member
    Join Date
    Jan 2005
    Posts
    24

    Re: How to access remote MS Access database

    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

  12. #12
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    37,141

    Re: How to access remote MS Access database

    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.

  13. #13
    New Member Laforge's Avatar
    Join Date
    Jun 2007
    Posts
    10

    Re: How to access remote MS Access database

    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.

  14. #14
    New Member
    Join Date
    Mar 2008
    Posts
    3

    Resolved Re: How to access remote MS Access database

    Quote Originally Posted by 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:
    VB Code:
    1. Option Explicit
    2.  
    3. Dim adoConn As ADODB.Connection
    4. Dim adoRst As ADODB.Recordset
    5.  
    6. Private Sub Command1_Click()
    7. '============================
    8. Dim strConString As String
    9. Dim strSQL As String
    10.  
    11.     'assign connection string
    12.     strConString = "Provider=MS Remote;" & _
    13.                    "Remote Server=http://192.168.1.1;" & _
    14.                    "Remote Provider=Microsoft.Jet.OLEDB.4.0;" & _
    15.                    "Data Source=MyRemoteDB;Persist Security Info=False"
    16.    
    17.     'initialize connection object variable
    18.     Set adoConn = New ADODB.Connection
    19.     'open connection
    20.     adoConn.Open strConString, "admin", ""
    21.    
    22.     strSQL = "Select * from Orders"
    23.    
    24.     'initialize recordset object variable
    25.     Set adoRst = New ADODB.Recordset
    26.     With adoRst
    27.         .Open strSQL, adoConn, , , adCmdText
    28.         If Not .EOF Then
    29.             Do While Not .EOF
    30.                 'read each record here
    31.                 '...
    32.                 .MoveNext
    33.             Loop
    34.             .Close
    35.         End If
    36.     End With
    37.    
    38.     'destroy recordset object if necessary (or do it when you unload the form)
    39.     'Set adoRst = Nothing
    40.    
    41.     'destroy connection object if necessary (or do it when you unload the form)
    42.     'Set adoConn = Nothing
    43.  
    44. 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

  15. #15
    New Member
    Join Date
    Nov 2008
    Posts
    1

    Re: How to access remote MS Access database

    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.


  16. #16
    Frenzied Member
    Join Date
    Mar 2005
    Posts
    1,263

    Re: How to access remote MS Access database

    Quote Originally Posted by 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:
    VB Code:
    1. Option Explicit
    2.  
    3. Dim adoConn As ADODB.Connection
    4. Dim adoRst As ADODB.Recordset
    5.  
    6. Private Sub Command1_Click()
    7. '============================
    8. Dim strConString As String
    9. Dim strSQL As String
    10.  
    11.     'assign connection string
    12.     strConString = "Provider=MS Remote;" & _
    13.                    "Remote Server=http://192.168.1.1;" & _
    14.                    "Remote Provider=Microsoft.Jet.OLEDB.4.0;" & _
    15.                    "Data Source=MyRemoteDB;Persist Security Info=False"
    16.    
    17.     'initialize connection object variable
    18.     Set adoConn = New ADODB.Connection
    19.     'open connection
    20.     adoConn.Open strConString, "admin", ""
    21.    
    22.     strSQL = "Select * from Orders"
    23.    
    24.     'initialize recordset object variable
    25.     Set adoRst = New ADODB.Recordset
    26.     With adoRst
    27.         .Open strSQL, adoConn, , , adCmdText
    28.         If Not .EOF Then
    29.             Do While Not .EOF
    30.                 'read each record here
    31.                 '...
    32.                 .MoveNext
    33.             Loop
    34.             .Close
    35.         End If
    36.     End With
    37.    
    38.     'destroy recordset object if necessary (or do it when you unload the form)
    39.     'Set adoRst = Nothing
    40.    
    41.     'destroy connection object if necessary (or do it when you unload the form)
    42.     'Set adoConn = Nothing
    43.  
    44. 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:
    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
    Last edited by luca90; Aug 21st, 2010 at 10:55 AM.

  17. #17
    Member
    Join Date
    May 2009
    Location
    where the wind breeze
    Posts
    45

    Re: How to access remote MS Access database

    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 ?

  18. #18
    Hyperactive Member
    Join Date
    Aug 2008
    Posts
    382

    Re: How to access remote MS Access database

    How i can Import My MsAccess Database into My hosting ?

  19. #19
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    37,141

    Re: How to access remote MS Access database

    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.

  20. #20
    Hyperactive Member
    Join Date
    Aug 2008
    Posts
    382

    Re: How to access remote MS Access database

    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

  21. #21
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    37,141

    Re: How to access remote MS Access database

    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.

  22. #22
    Hyperactive Member Condomx's Avatar
    Join Date
    Dec 2009
    Location
    Iligan City,Philippines
    Posts
    327

    Re: How to access remote MS Access database

    i got a error "Internet Client Error:Cannot Connect to Server" anyone help please?..
    ~[L!f3 !s @ll @ab0ut l3@rn!ng]~

    ~*D0nt Give up, h0pe is always present*~

  23. #23
    Lively Member
    Join Date
    Apr 2009
    Posts
    94

    Re: How to access remote MS Access database

    The locks are not defined, cursor location is not defined. which locks at client side which cursor location at client side.
    I belive in pray. I pray for you.
    You may have happy, healthy, wealthy life.

  24. #24
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    37,141

    Re: How to access remote MS Access database

    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.

  25. #25
    New Member
    Join Date
    Jan 2012
    Posts
    8

    Re: How to access remote MS Access database

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

  26. #26
    New Member
    Join Date
    Jan 2012
    Posts
    8

    Re: How to access remote MS Access database

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.