Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2585
  • Last Modified:

How to connect access database from Visual Basic

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
Rickyyy
Asked:
Rickyyy
  • 8
  • 5
1 Solution
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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
 
RickyyyAuthor Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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!

 
RickyyyAuthor Commented:
Done,  but why it say
Compile error:
Object required..
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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
 
RickyyyAuthor Commented:
Yes, I added the Reference..

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

Compile error:
Object required..
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
What line of code does the error occur on?

mx
0
 
RickyyyAuthor Commented:
Here

Set sPath = "D:\Program"
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
oh ... change

Set sPath = "D:\Program"

to

sPath = "D:\Program"

no Set

mx
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Same here:

Set tblLabor = "project.mdb"

change to

tblLabor = "project.mdb"

0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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
 
RickyyyAuthor Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 8
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now