Solved

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

Posted on 2000-04-05
7
166 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
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
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.
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

743 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now