Solved

how to connect a access database remotely through vb6

Posted on 2011-02-16
11
1,478 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
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…

756 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