?
Solved

How to connect access database from Visual Basic

Posted on 2008-10-22
13
Medium Priority
?
2,554 Views
Last Modified: 2013-12-25
Dear Expert,

I would like to know how to make a connection with access database.

Here is the details:
Database location: F:\Program
Database Name: Project.mdb

SInce I am not family with VB but I make a VBA program from access so want to try convert it to VB.

Thanks.

Ricky.
0
Comment
Question by:Rickyyy
[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
  • 8
  • 5
13 Comments
 
LVL 75
ID: 22778448
You can use the OpenDatabase method:  Here is a generic example:

Dim rst As DAO.Recordset
Set rst = DBEngine.Workspaces(0).OpenDatabase(sPath).OpenRecordset("tblLabor", dbOpenDynaset)

' code to operate on recordset

rst.Close
Set rst = Nothing

mx


0
 

Author Comment

by:Rickyyy
ID: 22778530
Compile error:
User-defined type not defined...

Am I wrong??

Private Sub Form_Load()
Dim rst As DAO.Recordset
Dim sPath As String
Dim tblLabor As String
Set sPath = "F:\Program"
Set tblLabor = "project.mdb"
Set rst = DBEngine.Workspaces(0).OpenDatabase(sPath).OpenRecordset("tblLabor", dbOpenDynaset)
rst.Close
Set rst = Nothing
End Sub
0
 
LVL 75
ID: 22778595
Be *sure* you have a Reference set to Microsoft DAO 3.xx  ... which is *not* the default (ADO is) in a newly created MDB.

Tools>>References ... scroll down the list and select - if it's not there.

mx
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

Author Comment

by:Rickyyy
ID: 22778688
Done,  but why it say
Compile error:
Object required..
0
 
LVL 75
ID: 22778708
Done?

Was that Reference not there?  And you added it?  And now it works?

When References are missing ... all bets are off ... and all sorts of crazy errors can manifest from missing References.

mx
0
 

Author Comment

by:Rickyyy
ID: 22778738
Yes, I added the Reference..

But this time pop-out the other error message..

Compile error:
Object required..
0
 
LVL 75
ID: 22778802
What line of code does the error occur on?

mx
0
 

Author Comment

by:Rickyyy
ID: 22778811
Here

Set sPath = "D:\Program"
0
 
LVL 75
ID: 22778832
oh ... change

Set sPath = "D:\Program"

to

sPath = "D:\Program"

no Set

mx
0
 
LVL 75
ID: 22778840
Same here:

Set tblLabor = "project.mdb"

change to

tblLabor = "project.mdb"

0
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 1000 total points
ID: 22778932
Private Sub Form_Load()
Dim rst As DAO.Recordset
Dim sPath As String
Dim tblLabor As String
sPath = "F:\Program"
tblLabor = "project.mdb"
Set rst = DBEngine.Workspaces(0).OpenDatabase(sPath).OpenRecordset("tblLabor", dbOpenDynaset)
' ** code here to do whatever ***
rst.Close
Set rst = Nothing
End Sub
0
 

Author Comment

by:Rickyyy
ID: 22778954
now is this error....

Run-time error '3051':
The microsoft Jet database engine cannot open the file 'd:\program' it is already opened excludively by another user, or you need permission to view its data...

Thanks....
0
 
LVL 75
ID: 22779002
You need to have 100% FULL permissions on any Folder containing an Access MDB.

Also, I believe I answered the original question - how to connect.  This is a different issue.  Can you post a new Question  - referencing this Q.

mx
0

Featured Post

What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Suggested Courses
Course of the Month9 days, 15 hours left to enroll

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