Solved

How to connect access database from Visual Basic

Posted on 2008-10-22
13
2,495 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
  • 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

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 - Access MVP) earned 250 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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
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…

813 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

11 Experts available now in Live!

Get 1:1 Help Now