pohleen
asked on
How do i code in DAO and ADO?
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
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
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
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
ASKER
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?
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?
ASKER
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.
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:\No
Set rstTemp = dbsNorthwind.OpenRecordset
"select * from tableA", dbOpenForwardOnly)
rstTemp.Close
dbsNorthwind.Close
wrkJet.Close
End Sub
Sub OpenRecordsetX()
Dim wrkJet As Workspace
Dim dbsNorthwind As Database
Dim rstTemp As Recordset
Set wrkJet = CreateWorkspace("", "admin", "", dbUseJet)
Set dbsNorthwind = wrkJet.OpenDatabase("c:\No
Set rstTemp = dbsNorthwind.OpenRecordset
"select * from tableA", dbOpenForwardOnly)
rstTemp.Close
dbsNorthwind.Close
wrkJet.Close
End Sub
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.OL
'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").V
oRs.MoveNext
Loop
'close recordset
oRs.Close
'close connection
oConn.Close
'destroy object
Set oRs = Nothing
Set oConn = Nothing
End Sub
D'Mzzl!
RoverM
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.OL
'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").V
oRs.MoveNext
Loop
'close recordset
oRs.Close
'close connection
oConn.Close
'destroy object
Set oRs = Nothing
Set oConn = Nothing
End Sub
D'Mzzl!
RoverM
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
pohleen
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)