Solved

How to connect access database from Visual Basic

Posted on 2008-10-22
13
2,507 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

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.
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

820 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