Solved

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

Posted on 2000-04-05
7
173 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 150 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
Industry Leaders: 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: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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

707 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