Solved

How do i code in DAO and ADO?

Posted on 2002-03-16
8
187 Views
Last Modified: 2010-05-02
Hi, i'm a student doing my project and would really appreciate any of you experts out there to help
me with my Visual Basic Coding. Include below is my coding page for a form name add new user. Can somebody
tell me what is wrong with my coding (assuming that i have tick in the relevant references for DAO method.)

Dim mydb As Object, myset As Object

Private Sub cmdCancel_Click()

frmWelcome.Visible = True   'This will enable the user to return to the welcome page
Unload frmNewUser

End Sub

Private Sub cmdNext_Click()

frmNewPrint.Visible = True
Unload frmNewUser

Set mydb = openDatabase("C:/My Documents/Double Module/dbSys.mdb") 'This sets the program to open the
available database
Set myset = setmydb

On Error Resume Next  'If any error occurs, the program will automatically skip the current, and move
on to the next data

myset.Next
myset!Name = txtName.Text
myset![FingerID No] = txtFingId.Text
myset!Position = txtposition.Text
myset!Department = txtDept.Text
myset![Date joined] = txtdate.Text
myset.Update

   If txtName.Text = "" Then
       MsgBox ("Please fill in your name")
   ElseIf txtFingId.Text = "" Then
       MsgBox ("Please fill in your assigned FingerID Number")
   ElseIf txtposition.Text = "" Then
       MsgBox ("Please fill in your position in this firm")
   ElseIf txtDept.Text = "" Then
       MsgBox ("Please fill in the department you work in")
   ElseIf txtdate.Text = "" Then
       MsgBox ("Please fill in the date you started, start with the day,month, then year")
   End If


txtName.Text = ""       'This command will set the fields to be empty for the next data transaction
txtFingId.Text = ""
txtposition.Text = ""
txtDept.Text = ""
txtdate.Text = ""

End Sub


i have problems running this. CAn anyone tell me what's wrong. And if i want to re write it using ADO,
how would it look like? i'm very new to ADO concept but i heard that it supports microsoft Access 2000.
Plz help.. urgently needed
0
Comment
Question by:pohleen
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 6871714
Where is the problem. This would help us alot.

myset.Next does not exist. I think you should have myset.Edit instead.

it is common practice not to use "on error resume next" while debugging (because you don't see your source of errors.

also, you are using late binding (Dim mydb As Object, myset As Object). it is better to use early binding (Dim mydb As database, myset As recordset)
0
 
LVL 12

Expert Comment

by:roverm
ID: 6871851
emoreau, I don't agree with you that early binding is 'better'. Both have pro's and cons.
For one: With early binding you need to install (or have installed) the same DLL (or other library) as you are using when developing, with late binding you don't have to as long as the library is installed.
However, when developing, early binding is the way to go!

pohleen:
ADO can indeed access Access 97/2000, but so can DAO. No need for a change there. But, ADO has more advantages over DAO. Faster for one.

What emoreau said about myset.Edit and the On error resume next seems some good advice to me.

If you still can't get this working I can build an example using ADO. Just let me know.

D'Mzzl!
RoverM
0
 

Author Comment

by:pohleen
ID: 6874076
the problem is that when i run this project,it highlights

Private Sub cmdNext_Click()
and openDatabase from
Set mydb = openDatabase("C:/My Documents/Double Module/dbSys.mdb") as sub or function not defined. There fore, i am getting confuse.
also... how would i write this coding if i'm using ADO functions?
0
 

Author Comment

by:pohleen
ID: 6874078
btw emoreau n RoverM, i tried using Myset.Edit n it still didn't work. I've used meset.Next before for a school project of mine and it worked fine. I don't quite get early biding and late binding.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 69

Expert Comment

by:Éric Moreau
ID: 6874145
see this sample:

Sub OpenRecordsetX()

    Dim wrkJet As Workspace
    Dim dbsNorthwind As Database
    Dim rstTemp As Recordset

    Set wrkJet = CreateWorkspace("", "admin", "", dbUseJet)
    Set dbsNorthwind = wrkJet.OpenDatabase("c:\Northwind.mdb")
 

    Set rstTemp = dbsNorthwind.OpenRecordset( _
        "select * from tableA", dbOpenForwardOnly)

    rstTemp.Close
    dbsNorthwind.Close
    wrkJet.Close

End Sub
0
 
LVL 12

Expert Comment

by:roverm
ID: 6874891
Here's an example using ADO. Set a reference to Microsoft ActiveX Data Objects 2.x.

Sub Test()
Dim oConn As ADODB.Connection
Dim oRs As ADODB.Recordset

    'create new ado connectio
    Set oConn = New Connection
   
    'set the connection string
    oConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.3.51;Persist Security Info=False;Data Source=C:\Program Files\Microsoft Visual Studio\VB98\NWIND.MDB"
   
    'open the connection to the db
    oConn.Open
   
    'fill recordset
    Set oRs = oConn.Execute("select * from customers")
   
    'do your stuff with do db, like loop through it
    Do While Not oRs.EOF
        Debug.Print oRs.Fields("CustomerID").Value
        oRs.MoveNext
    Loop
   
    'close recordset
    oRs.Close
   
    'close connection
    oConn.Close
   
    'destroy object
    Set oRs = Nothing
    Set oConn = Nothing

End Sub


D'Mzzl!
RoverM
       
0
 

Accepted Solution

by:
stevengann earned 100 total points
ID: 6875932
I would take the time to learn ADO. It is usable for seems like everything. I use it from ASP to query SQL2000 databases as well as most versions of Access from web pages. The same code with a few exceptions can be dropped into a VB project with little changes. Also vb script.

‘Step one. Define objects.
Dim MyConn As New ADODB.Connection
Dim rs As New ADODB.Recordset

‘Step Two - Open the database connection.

'SQL Connection
MyConn.Open "Provider=SQLOLEDB.1;Persist Security Info=True;User ID=sa;pwd=;Initial Catalog=DataBaseName;Data Source=ServerName"

'Access Conection
‘MyConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\DBaseName.mdb;Persist Security Info=False"

‘Step Three - Define query

Query = "select * from TableName

‘Step four - Fill RecordSet and do something with it. Fill a list box.

rs.Open Query, MyConn, 2, 3

rs.MoveFirst
Do While Not rs.EOF
TempNAME = "" & rs("dName")
TempADDRESS = "" & rs("dAddress")
'The = "" is to make sure that you don’t get the dreaded “Invalid use of NULL”
'by assigning it to "" a null becomes a blank string first so if it IS NULL no problem.

temp = " " & TempNAME & "  -  " & TempADDRESS

List1.AddItem temp
rs.MoveNext
Loop
Rs.close
MyConn.Close


This will read all of the names and address from a database and place them in a list box.

To edit the address on one of the fields…
 VBString = “Smith”
Query = "select * from TableName where dName = '" & VBString & "'"
rs.Open Query, MyConn, 2, 3

Check if found and add record if no record found.

If rs.BOF And rs.EOF Then
       rs.AddNew
End If

Assign data to fields
rs("dName") = “Smith” or VBString
rs("dAddress") = srtAddress

‘Commit Change
 rs.Update
 rs.Close
MyConn.Close
0
 

Author Comment

by:pohleen
ID: 6894276
anyway.....to roverM and emoreau..... thank u so much for the time taken....... it was a tough decision but i need sumthing da closest. u guyz have been very help ful. thank u so much.

pohleen
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
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…
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…

914 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