Solved

how to connect a access database remotely through vb6

Posted on 2011-02-16
11
1,499 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
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

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.

Question has a verified solution.

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

Suggested Solutions

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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…

739 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