Solved

how to connect a access database remotely through vb6

Posted on 2011-02-16
11
1,353 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
 
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
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 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…

762 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now