Solved

VB to Access

Posted on 2003-11-06
17
1,153 Views
Last Modified: 2013-12-25
hey,

I am creating a vb application and wish to store data in a ms access database. However while i am ptretty proficient with VB (for a beginer) i am 100% ignorant of how to programme to a data base. I was wondering if some one could post the SIMPLEST code for, how to send details (eg name address, phnone etc) from my apllication to a ms access database and then how to later retrieve this info back into the applcation from the database file.

I am also will to increase the points to 100 (+) if someone could include the code for how to: search the data base & display persons details if their name was entered in a text box and a search button was clicked.

If some one has a similar SIMPLE application and wishes to email it they can email me at bowemc@eircom.net

As i have said already I would like this code to be kept as SIMPLE as possible as i will need to understand it, to be able to incorporate it into my vb application

All input is greatly appreciated.

Thanks as ever

SiteCorp
0
Comment
Question by:sitecorp
  • 5
  • 3
  • 2
  • +4
17 Comments
 
LVL 44

Expert Comment

by:Arthur_Wood
Comment Utility
I would suggest that you should get a good book, on programming VB for Database usage.

 Learn to Program Visual Basic Databases
John Smiley, Robert Guerin
Publisher: Muska & Lipman Publishing
ISBN: 1929685173

or

Mastering Database Programming with Visual Basic 6
Evangelos Petroutsos
Publisher: Sybex, Incorporated
ISBN: 0782125980

or:

Sams Teach Yourself Database Programming with Visual Basic 6 in 21 Days
Curtis Smith, M. Amundsen, Michael C. Amundsen
Publisher: Pearson Education
ISBN: 0672313081

any one of which will get you started.

AW


 
 
0
 
LVL 29

Expert Comment

by:leonstryker
Comment Utility
0
 

Author Comment

by:sitecorp
Comment Utility
i don't have the time to start reading a big book and its a pc based application - not web stuff involved
0
 
LVL 29

Expert Comment

by:leonstryker
Comment Utility
Ok here is the short version:

Public strSQL As String            
Public cnConn As ADODB.Connection  

Public Function OpenConn() As Boolean
Dim strConn As String       '/ Connection string to open database
    OpenConn = True
    On Error GoTo ERROR_FUNCTION
    Select Case True
        Case cnConn Is Nothing
            Set cnConn = New ADODB.Connection
            GoTo OPEN_CONN
        Case cnConn.State = adStateClosed
            GoTo OPEN_CONN
    End Select
EXIT_FUNCTION:
    Exit Function
OPEN_CONN:
    strConn = ="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\db1.mdb;Persist Security Info=False
    With cnConn
        .CursorLocation = adUseClient
        .Open strConn
    End With
    GoTo EXIT_FUNCTION
ERROR_FUNCTION:
    OpenConnOracle = False
    MsgBox "OpenConn error: " & Err.Number & " " & Err.Description
    Err.Clear
    GoTo EXIT_FUNCTION
End Function


Public Function OpenRecSet(ByVal strSQL As String, _
                            ByRef rsSet As ADODB.Recordset, Optional intRows As Integer = 0) As Boolean
    OpenRecSet = True
    On Error Resume Next
    rsSet.Close
    On Error GoTo ERROR_FUNCTION
    If Not OpenConn() Then GoTo EXIT_FUNCTION
    Set rsSet = New ADODB.Recordset
    rsSet.Open strSQL, cnConn, adOpenStatic, adLockOptimistic
   
    intRows = rsSet.RecordCount
   
EXIT_FUNCTION:
    Exit Function
ERROR_FUNCTION:
    Select Case Err.Number
        Case -2147217865
            MsgBox "Error - Could not connect to database.", vbCritical
        Case 3709
            Err.Number = 0
    End Select
    Err.Clear
    OpenRecSet = False
    GoTo EXIT_FUNCTION
End Function

To call these functions with the value in your textbox as a variable  and display the name in a list use:

Private Sub Button1_Click()
Dim intCount as integer, intRows as integer
    strSQL = "SELECT* FROM table WHERE name = '" & TextBox1.Text & "'"
    If Not OpenRecSet(strSQL, rsRec, intRows) Then GoTo Exit Sub
    If intRows > 0 Then
        rsRec.MoveFirst
        For intCount = 1 To intRows
            lstNames.AddItem Trim(rsRec!name & "")
            rsRec.MoveNext
        Next
    End If
End Sub
0
 
LVL 8

Expert Comment

by:MYLim
Comment Utility
Do you know 'MSDN library' ?
q(^ _ ^)p
0
 
LVL 8

Accepted Solution

by:
MYLim earned 75 total points
Comment Utility
Dim Cnn As ADODB.Connection
Dim Rs As ADODB.Recordset
 
Private sub form_load()
On error goto ErrFound
Set Cnn = New ADODB.Connection
Cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyProgram\MyAccessDB.mdb;User Id=admin;Password=;"

Set Rs = New ADODB.Recordset
Rs.CursorLocation = adUseClient
Rs.Open "Select * from yourTable", Cnn, adOpenStatic, adLockReadOnly

if Rs.eof = true then
 msgbox "Empty Recordset,Connection will be Close"
 rs.close
 set rs=  nothing

  Cnn.close
 set Cnn = nothing
 
 exit sub
end if

debug.print rs.fields(0).value & " "

 rs.close
 set rs=  nothing

  Cnn.close
 set Cnn = nothing
 
 exit sub
ErrFound:
msgbox "Err description : " & Err.Description
if rs.state=adstateOpen Then
  rs.close
  set rs = nothing
end if
 
if Cnn.state = adstateOpen then
  Cnn.close
  set Cnn = nothing
end if
end sub
0
 
LVL 1

Expert Comment

by:KeyPakt
Comment Utility
You are using VB and you want to use a Access database. Best and the most simple way of doing this is to use DAO, not ADO.

Go to Project->References and select the Microsoft DAO 3.6 Object library (or earlier version). Now you can use DAO.

----------------- Code to add data -----------------------------
Dim myDB as DAO.Database
Dim myRS as DAO.Recordset

Set myDB = DAO.OpenDatabase("C:\Database Path\MyDatabase.mdb")
Set myRS = myDB.OpenRecordset("Person info")                               'Person info is here my name of the table

myRS.AddNew
myRS!Name = "Some name"
myRS!Address = "Some street"
myRS!Phone = "555-LA"
myRS.Update

myRS.Close
myDB.Close
Set myRS = nothing
Set myDB = nothing

------------- Code to retreive data ----------------------
Dim myDB as DAO.Database
Dim myRS as DAO.Recordset
Dim tName as string, tAddress as string, tPhone as string

Set myDB = DAO.OpenDatabase("C:\Database Path\MyDatabase.mdb")
Set myRS = myDB.OpenRecordset("Person info")                               'Person info is here my name of the table

If (myRS.Bof = false or myRS.Eof = false) then
    myRS.MoveFirst
   
    do until myRS.Eof = true
        tName = myRS!Name
        tAddress = myRS!Address
        tPhone = myRS!Phone
       
        'Do whatever you like with the data
       
        myRS.MoveNext
    loop
end if

myRS.Close
myDB.Close
Set myRS = nothing
Set myDB = nothing

----------------- Code to search the table ---------------------------------
Dim myDB as DAO.Database
Dim myRS as DAO.Recordset
Dim tSearch as string

tSearch = txtName.Text

Set myDB = DAO.OpenDatabase("C:\Database Path\MyDatabase.mdb")
Set myRS = myDB.OpenRecordset("Person info")                               'Person info is here my name of the table

'If your recordset contain a key you can use this to search, if not you must search in another way
------ Ex1 with key
myRS.Index = "PrimaryKey"                        'Or the name of the key if it's another name

if (myRS.Bof = false or myRS.Eof = false) then
    myRS.Seek "=", tSearch
    if (myRS.NoMatch = false) then              'If the record were found
          txtAddress.Text = myRS.Address
          ...........
    end if
end if

-------- Ex2 with no key
if (myRS.Bof = false or myRS.Eof = false) then
    myRS.FindFirst "Name = '" & tSearch & "'"
    if (myRS.NoMatch = false) then
        txtAddress = myRS!Address
        ...............
    end if
endif

myRS.Close
myDB.Close
Set myRS = nothing
Set myDB = nothing


I don't think it can be more simple than this.
0
 
LVL 29

Expert Comment

by:leonstryker
Comment Utility
KeyPakt,

Although your solution will work, you must be aware that DAO is no longer a supported technology by Microsoft.  It is not portable to other databases and not upgradable to .NET.  In other words, I would never use it in any NEW application and would most likely recode any old applications which do use it.

Leon
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 1

Expert Comment

by:KeyPakt
Comment Utility
Leon,

Of course you are correct about that but since the question was VB to Access and that the sollution should be as simple as possible I could not think of anything easier than to use DAO for Access databases.
0
 
LVL 29

Expert Comment

by:leonstryker
Comment Utility
KeyPakt,

I would disagree that DAO is simpler than ADO as well.

Leon
0
 
LVL 5

Expert Comment

by:g0rath
Comment Utility
Performance anaysis shows that DAO access is better optimized for speed over ADO. While ADO may allow more flexibility it doesn't have the performance edge.

This only applies to MS Access as I write DAO only for MS Access, and ADO for anything to do with SQL Server
0
 
LVL 29

Expert Comment

by:leonstryker
Comment Utility
g0rath,

Speedwise DAO will perform faster on Access 97 and above.  In most cases this difference is very minor.

I will still stand by my earlier points, that it is not simpler.

Leon
0
 
LVL 8

Expert Comment

by:MYLim
Comment Utility
0
 
LVL 3

Expert Comment

by:WRNewman
Comment Utility
Oh dear experts

Facts

DAO on a direct connection is far faster than ADO, not as scaleable though but fine for up to 50 users dependent upon the app.

ADO has heaps of connectors so can make things simpler for new users who need connections to e-mail, csv etc.
I have to agree that DAO is probably simpler to get started for the user with an access db, but we can prevaricate on this issues for ever, lets just try to help the guy.

In declarations
dim db as database
dim tb as recordset

You can then open the connection on form load and close it on unload but use it anywhere in the form.
You can declare many tables tb1,tb2,tb3 and use them on different tables having them open all the time.

set db=opendatabase("mydb.mdb")    works just great if the db is in the same folder as the app
set tb=db.openrecordset("users",dbopendynaset)    don't bother with snapshots to start with

Add a record

tb.addnew
tb("name")="fred"
tb("id")=21
tb.update

To find a record by numeric field and edit it

search=" id = "+text1.text                    user type 21 into the text box
tb.findfirst search
if not tb.nomatch then                                 found it cursor is now pointing at it in the DB
  tb.edit
  tb("name")=text2.text
  tb.update
endif

To find a record by text field

search = " Name = 'Fred'"
tb.findfirst search

The ' limiters are necessary around the text fields but not the numerics. The Jet engine generates the following sql from the findfirst statement

Select * from tablename where name = 'Fred'
Then does a movefirst on it.

This is just basic stuff, we can go on about how to use SQL until the cows come home but this will get you simple input and output from your db.

Crack this then post more questions.

You will have to go to project then references and select Microsoft DAO 3.6 object library to support Access 2000 upwards or 3.51 for Access 97
0
 
LVL 44

Expert Comment

by:Arthur_Wood
Comment Utility
Actually, DAO 3.6 can address Access 97 files, so there is no need to support both versions of DAO.

It is however te case, that DAO 3.61 CANNOT addess Access 2000+ databases.

AW
0
 
LVL 3

Expert Comment

by:WRNewman
Comment Utility
Hi Guys

Not really the place for this but since it is being discussed lets put the record straight.

Using a Live MRP package, full live and loaded, using a routine which rationilsed the entire factory demand schedule and stock availability, so many SQL calls you would not believe. I ran tests comparing DAO & ADO

In a LAN situation, with a powerful server and only 20 or so terminals DAO is by a very long way faster, and we are talking 5 to 10 times. This is because the entire NETBIOS is available, the only difference between running local is the network load.

In the same situation, SQL Server is much faster, but a about a quarter of the speed of access. This is because it is accessed by a port, through an ODBC driver which accesses the port 1433. Add to this that SQL Server limits the use of the processors and will not give more than around 40% of processor time to the operation.

This is all very fine but ADO was borne out of scaleability. The power that DAO sucks out of the system only makes it suitable for relatively small networks. I have had 50 terminals on one MRP system but you are already then looking at the workloads on those terminals because you are approaching overload.

ADO uses the threading model, which makes it absolutely excellent for intranet or large LANS, simply the threads cost far less in server power and youdo not have to offer all server services (NETBIOS) to the user. Using Access with ADO means you have to use an OleDB connector which slows it all down. Access was never developed for threaded access whereas SQL Server was. Once you are into Internet, Access is only really suited for read only data, you really need SQL Server or Oracle with triggers and stored procedures.

We do have one small web app on one our servers which has a background process running recieving data back across the web and updating the access DB using DAO. There is always room for an oddbal application.

My conclusions from hard testing is that DAO is far from dead, but that ADO is also excellent. You just have to choose which one to suit the application.

I hope this helps

Will Newmanl
0
 

Author Comment

by:sitecorp
Comment Utility
 M&Y  sent me on some good examples via email, so its not for the above code, but for those projects that i awarded the points. thanks to everyone for their comments.

regards

site corp
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

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…
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…
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 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…

762 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

8 Experts available now in Live!

Get 1:1 Help Now