Solved

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

Posted on 2000-04-05
7
170 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
Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

 

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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Specify ip address in my connection string for sas 2 59
vb6 connector to SQL Server 2 37
Visual Studio 2005 text editor 10 38
Help me. 3 54
Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
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…
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…

776 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