Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

how to connect a access database remotely through vb6

Posted on 2011-02-16
11
Medium Priority
?
1,616 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
[X]
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
  • 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
Technology Partners: 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 1000 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 1000 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: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

721 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