Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

how to connect a access database remotely through vb6

Posted on 2011-02-16
11
1,459 Views
Last Modified: 2012-05-11

hi
i want to connect to a access database (.mdb) remotely through a vb6 application.

what changes/setup i need to make on computer on which database file is residing???
and what will be connection string for connection?????????????

regards.

raza
0
Comment
Question by:helloraza
  • 4
  • 4
  • 2
  • +1
11 Comments
 
LVL 15

Expert Comment

by:David L. Hansen
ID: 34909119
Remotely?  Is the database on a network?
0
 
LVL 16

Expert Comment

by:HooKooDooKu
ID: 34909445
If you want to use DAO and the database isn't password protected...

Dim WS as DAO.Workspace
Dim DB as DAO.Database
Dim RS as DAO.RecordSet

Set WS = CreateWorkspace("", "ADMIN", "", dbUseJet )   'If it's password protected, it goes here
Set DB = WS.OpenDatabase("X:\Folder\MyDB.MDB", False )   'Normal Access... or
Set DB = WS.OpenDatabase("X:\Folder\MyDB.MDB", True )    'Exclusive Access... or
Set DB = WS.OpenDatabase("X:\Folder\MyDB.MDB", True, True ) 'DB on ReadOnly media (CD or DVD)... or
Set DB = WS.OpenDatabase("X:\Folder\MyDB.MDB", False, True ) 'Read Only Access

Set RS = DB.OpenRecordSet( "Select * from MyTable", dbOpenDynaset )  'Lots of other options

or to run a command, like an update query

DB.Execute( "Update MyTable Set MyField = 1 where MyOtherField = 2" )
0
 

Author Comment

by:helloraza
ID: 34911466

hi
thanks for replies.

actually i want that my client can use software from home, even if .mdb file is on a computer in office, so he can connect from home to that database through internet.
in this situation what setup should be on office computer and what changes i need to make in my connection string which is given below, and i am using it to connect locally on a network.

connection string is
"Provider=Microsoft.ACE.OLEDB.12.0;Data Source= \\servercomp\CRME\mydatabase.mdb;Jet OLEDB:Database Password='mypassword';"

regards
0
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 
LVL 14

Expert Comment

by:VBClassicGuy
ID: 34911476
I just simply use:

Dim myDB As New ADODB.Connection

myDB.Provider ="Microsoft.Jet.OLEDB.4.0"
myDB.Open

The database is connected!

If you want a a recordset returned via query:

Dim rs As New ADODB.Recordset
Dim sql As String

sql = "SELECT * FROM [MyTable] WHERE [MyField] = 1"
rs.Open sql, myDB, adOpenForwardOnly, adLockReadOnly
If Not rs.EOF Then
   'process recordset
End If

To execute a statement:

sql = "DELETE * FROM [myTable] WHERE [MyField]=1"
myDB.Execute sql, , adExecuteNoRecords

Always end your DB access with:

myDB.Close
Set myDB = Nothing
rs.Close   'if a recordset was used
Set rs = Nothing   'if a recordset was used
0
 
LVL 14

Expert Comment

by:VBClassicGuy
ID: 34911496
Opps. Forgot

myDB.Open

should be

myDB.Open PathToDatabase   'MDB path/filename
0
 

Author Comment

by:helloraza
ID: 34911632
thanks

but i want to connect through INTERNET.?????????????????????????

where user is sitting at home and access database is in computer at office?

regards.
0
 
LVL 14

Expert Comment

by:VBClassicGuy
ID: 34911709
This blurb might be of some help:

Access cannot connect to the data source (file) over an HTTP
connection in this manner. If you think of the situation like your
needing to copy the file, you cannot copy from http://xxxxx to
anywhere without a web browser playing in the middle.

To get to the database, you will need either a mapped drive, or a UNC
path (\\machinename\sharename) to the file. It is possible that you
can get to it via the IP address. For example, if your web server is
ip address 192.168.1.100 and you create a share on the computer by the
name of "datashare", then a connection string like...

Data Source=\\192.168.1.100\datashare\Database.mdb

could work. Of course you will still need to allow read/write access
to the directory where that file is located.

0
 

Author Comment

by:helloraza
ID: 34911773
thanks
so i need to install a WEB SERVER on remote machine where .mdb is residing???
is there there any other solution in which i could make some IP configuration on remote machine having .mdb file on it???????????
0
 
LVL 14

Accepted Solution

by:
VBClassicGuy earned 250 total points
ID: 34915993
No, you don't necessarily need a web server, just a static IP address. Your broadband provider can accomodate you, but realize that a static IP address has a "surcharge" (more money) than a dynamic IP address. For instance, I use a web hosting service for my personal web site, which is accessible with a URL (like www.MyWebPage.com". But it uses a dynamic IP address. To get a static IP, my fee for web hosting would be greater.
0
 
LVL 15

Assisted Solution

by:David L. Hansen
David L. Hansen earned 250 total points
ID: 34922267
A VPN could be an answer.  Perhaps the company you work for can set one up (or maybe they already have one configured).  You'd have to ask, but that would not only connect you to your machine at work but also keep the connection secure.
0
 

Author Closing Comment

by:helloraza
ID: 34935358
thank you
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question