Solved

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

Posted on 2000-04-05
7
172 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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 

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

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…

734 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