?
Solved

Using VB To read an Access (*MDB) file...how?

Posted on 2000-04-05
7
Medium Priority
?
174 Views
Last Modified: 2010-05-02
Can some one write me up a simple demo of how you can access an MS Acces database file from within VB.. i want to be able to read and write to and from it.  is there a way to find the field names too? i want to be able to do this without having MS Access installed..
0
Comment
Question by:stewartbrown
[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
7 Comments
 

Expert Comment

by:c4mar
ID: 2688361
u have to create a workplace forVB to run the database....a simple code could looks like following

Dim wa as workplace
Dim db as database
dim rs as recordset
..
..
..
..
..
set ws = workspace(0)
set de ws.openDatabase("database name")
set rs = db.openrecordset("SQL code in here")


once you open a recordset , u can use rs.addnew, rs.update such command to update or change the database information. However, if u want to read specifier field, you would need to use
such thing like
rs.field("client_ID") to read the data.
0
 
LVL 14

Accepted Solution

by:
wsh2 earned 600 total points
ID: 2688728
1. Create a Standard.Exe project.
2. Add 3 List boxes (List1, List2, List3).
3. Set a Project Reference to "Microsoft ActiveX Data Objects.
4. Set a Project Reference to "Microsoft ADO For DDL and Security"
5. Copy and Paste the following into the Form1 Code Window.
6. Edit and Replace "c:\MyPath\MyFile.MDB" to whatever is appropriate for your environment.
7. Press F5 for a demonstration.

<----- Code Begin ----->

Option Explicit

Private Sub Form_Load()
   
   On Error Resume Next
   Kill "c:\MyPath\MyFile.mdb"
   On Error GoTo 0
   
   Call xCreateDatabase
   Call xCreateTable
   Call xMakeData
   Call xReadData

End Sub
Private Sub xCreateDatabase()

   Dim cat As New ADOX.Catalog
   Dim strConnect As String
   strConnect = "Provider=Microsoft.Jet.OLEDB.3.51;" _
      & "Data Source=c:\MyPath\MyFile.mdb"
   cat.Create strConnect
   Set cat = Nothing
   
End Sub

Private Sub xCreateTable()

   Dim cat As New ADOX.Catalog
   Dim strConnect As String
   strConnect = "Provider=Microsoft.Jet.OLEDB.3.51;" _
      & "Data Source=c:\MyPath\MyFile.mdb"
   cat.ActiveConnection = strConnect
   
   Dim tbl As New Table
   tbl.Name = "MyTable"
   tbl.Columns.Append "MyColumn1", adInteger
   tbl.Columns.Append "MyColumn2", adInteger
   tbl.Columns.Append "MyColumn3", adChar, 50
   cat.Tables.Append tbl
   
   cat.ActiveConnection = Nothing

End Sub

Private Sub xMakeData()

   Dim db As New ADODB.Connection
   Dim strConnect As String
   strConnect = "Provider=Microsoft.Jet.OLEDB.3.51;" _
      & "Data Source=c:\MyPath\MyFile.mdb"
   db.ConnectionString = strConnect
   db.Open
   
   Dim rs As New ADODB.Recordset
   rs.ActiveConnection = db.ConnectionString
   rs.CursorType = adOpenDynamic
   rs.LockType = adLockOptimistic
   rs.Source = "SELECT * FROM MyTable"
   rs.Open
   
   Dim intIndex As Integer
   For intIndex = 1 To 10
      rs.AddNew
      rs!MyColumn1 = intIndex
      rs!MyColumn2 = intIndex * 10
      rs!MyColumn3 = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
      rs.Update
   Next intIndex
   
   rs.Close
   Set rs = Nothing
   Set db = Nothing

End Sub

Private Sub xReadData()

   Dim strDataFile As String
   strDataFile = "c:\MyPath\MyFile.mdb"
   
   Dim db As New ADODB.Connection
   db.Provider = "Microsoft.Jet.OLEDB.3.51"
   db.ConnectionString = strDataFile
   db.Open

   Dim rs As New ADODB.Recordset
   rs.ActiveConnection = db.ConnectionString
   rs.CursorType = adOpenStatic
   rs.Source = "SELECT * FROM MyTable"
   rs.Open
   
   Do Until rs.EOF
      List1.AddItem rs!MyColumn1
      List2.AddItem rs!MyColumn2
      List3.AddItem rs!MyColumn3
      rs.MoveNext
   Loop
     
   rs.Close
   Set rs = Nothing
   Set db = Nothing

End Sub

<----- Code End ----->

0
 
LVL 14

Expert Comment

by:wsh2
ID: 2688732
c4Mar:
You may want to review your "Answer" as there are several (typing?) errors there.. <smile>.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:stewartbrown
ID: 2688817
Adjusted points from 50 to 150 (excellent Answer!)
0
 

Author Comment

by:stewartbrown
ID: 2688826
Excellent! :)  i'll award you the points soon, i'm just going to keep the question open.. so that i don't "lose"  you(r help)... i plan on asking a few more questions.. :) heh heh.. I'll up the points as i ask.. then you can have them all.. unless you wish to have them as separate questions...
0
 
LVL 43

Expert Comment

by:TimCottee
ID: 2689050
Stewartbrown, I think it would perhaps be better if you were to post seperate questions. You may have a question the answer to which may benefit other people, they would not then be able to find it as it will be buried under your original title.
0
 

Author Comment

by:stewartbrown
ID: 2700214
Thank you for the wonderful example code! :) i'm going to post another question.. dealing with your code after i accept this comment as an answer..  so please look for it wsh2, and see what you think... Thanks again..
0

Featured Post

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!

Question has a verified solution.

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

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses
Course of the Month14 days, 17 hours left to enroll

771 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