Solved

How to connect access database from Visual Basic

Posted on 2008-10-22
13
2,535 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
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!

 

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 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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
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.
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…

717 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