'Few questions on updating datas into Access' Thread RSS Feed

Few questions on updating datas into Access

Mon, 09 Jul 2007 03:48:28 -0700

Hi guys, i have a few question listed below:

I have username and password textbox in a form. When i click the login button on the same form, it has to connect to the database and retrieve the data, matching both username and password and at the end determine whether to display the next form.

1. How do i connect the database?
2. What coding must i type into the Login button, eg: connect to the database (mdb.open or what-so-ever)

Will be apprecate if could type me a example!

Thanks thanks!!

Re: Few questions on updating datas into Access

Mon, 09 Jul 2007 04:35:08 -0700

: Hi guys, i have a few question listed below:
:
: I have username and password textbox in a form. When i click the
: login button on the same form, it has to connect to the database and
: retrieve the data, matching both username and password and at the
: end determine whether to display the next form.
:
: 1. How do i connect the database?
: 2. What coding must i type into the Login button, eg: connect to the
: database (mdb.open or what-so-ever)
:
: Will be apprecate if could type me a example!
:
: Thanks thanks!!
:

can use ADO or DAO - DAO is a little faster for Access/Jet databases but ADO gives you more flexibility.

'ADO
'required reference to [microsoft activex data object 2.x library]
Public Sub AdoConnectSecureDB()
Dim cn As ADODB.Connection, rs As ADODB.Recordset
Dim sCon As String
Dim ErrCount As Byte

On Error GoTo erh
ErrCount = 0

'CONNECTION STRING
'9/12/02 - ADO CONNECTION TO A SECURE ACCESS DB w/ WORKGROUP
'sCon = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
' "User ID=ACCOUNT;Password=PASSWORD;" & _
' "Data Source=C:\MyMDB.mdb;" & _
' "Persist Security Info=False;" & _
' "Jet OLEDB:System database=c:\system.mdw"

'CONNECTION STRING
'9/12/02 - ADO CONNECTION TO AN ACCESS DB
sCon = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"User ID=Admin;Password=;" & _
"Data Source=C:\db1.mdb;" & _
"Persist Security Info=False;"

'CREATE NEW CONNECTION
Set cn = New ADODB.Connection
'OPEN CONNECTION WITH CONNECTION STRING
cn.Open sCon
'CREATE NEW RECORDSET
Set rs = New ADODB.Recordset
'OPEN RECORDSET
rs.Open "table1", cn, adOpenDynamic, adLockOptimistic, adCmdTable
rs.MoveFirst

'TRAVERSE RECORDS
Do While Not rs.EOF
msgbox rs!Field1
rs.MoveNext
Loop

'CLOSE/RELEASE RESOURCES
xit:
If rs.State <> adStateClosed Then rs.Close
If cn.State <> adStateClosed Then cn.Close
xit2:
Set rs = Nothing
Set cn = Nothing
Exit Sub

erh:
ErrCount = ErrCount + 1
MsgBox Err.Description, vbCritical, Err.Number
If ErrCount > 1 Then Resume xit2 Else Resume xit
End Sub

'DAO
'Need to set a reference to-
'Microsoft DAO 3.6 Object Library
Sub ConnectDAO()
Dim wk As DAO.Workspace
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set wk = DAO.DBEngine(0)
Set db = wk.OpenDatabase("d:\db2.mdb") 'OPEN DB
'Set rs = db.OpenRecordset("table1", dbOpenSnapshot) 'READONLY
Set rs = db.OpenRecordset("table1", dbOpenDynaset) 'WRITE

rs.AddNew 'ADD A NEW RECORD
rs!FIELD1 = "new rec" 'ASSIGN VALUE TO A FIELD
rs.Update 'UPDATE/SAVE RECORD TO TABLE

'rs.Edit 'EDIT RECORD
'rs!FIELD1 = "changed rec" 'ASSIGN NEW VALUE
'rs.Update 'UPDATE/SAVE

'TRAVERSE AND OUTPUT RECORDS
'Do While Not rs.EOF
' MsgBox rs!FIELD1
' rs.MoveNext
'Loop

rs.Close
db.Close
wk.Close
Set rs = Nothing
Set db = Nothing
Set wk = Nothing
End Sub

Re: Few questions on updating datas into Access

Mon, 09 Jul 2007 17:28:27 -0700

Hihi, in this code, where should i type in the code for my login button for the function to check the username and password?
let say my login button is

btn Login,
Username textfield = TBusername
Password textfield = TBPassword

thanks!!

: : Hi guys, i have a few question listed below:
: :
: : I have username and password textbox in a form. When i click the
: : login button on the same form, it has to connect to the database and
: : retrieve the data, matching both username and password and at the
: : end determine whether to display the next form.
: :
: : 1. How do i connect the database?
: : 2. What coding must i type into the Login button, eg: connect to the
: : database (mdb.open or what-so-ever)
: :
: : Will be apprecate if could type me a example!
: :
: : Thanks thanks!!
: :
:
: can use ADO or DAO - DAO is a little faster for Access/Jet databases
: but ADO gives you more flexibility.
:
:
: 'ADO
: 'required reference to [microsoft activex data object 2.x library]
: Public Sub AdoConnectSecureDB()
: Dim cn As ADODB.Connection, rs As ADODB.Recordset
: Dim sCon As String
: Dim ErrCount As Byte
:
: On Error GoTo erh
: ErrCount = 0
:
: 'CONNECTION STRING
: '9/12/02 - ADO CONNECTION TO A SECURE ACCESS DB w/ WORKGROUP
: 'sCon = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
: ' "User ID=ACCOUNT;Password=PASSWORD;" & _
: ' "Data Source=C:\MyMDB.mdb;" & _
: ' "Persist Security Info=False;" & _
: ' "Jet OLEDB:System database=c:\system.mdw"
:
: 'CONNECTION STRING
: '9/12/02 - ADO CONNECTION TO AN ACCESS DB
: sCon = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
: "User ID=Admin;Password=;" & _
: "Data Source=C:\db1.mdb;" & _
: "Persist Security Info=False;"
:
: 'CREATE NEW CONNECTION
: Set cn = New ADODB.Connection
: 'OPEN CONNECTION WITH CONNECTION STRING
: cn.Open sCon
: 'CREATE NEW RECORDSET
: Set rs = New ADODB.Recordset
: 'OPEN RECORDSET
: rs.Open "table1", cn, adOpenDynamic, adLockOptimistic, adCmdTable
: rs.MoveFirst
:
: 'TRAVERSE RECORDS
: Do While Not rs.EOF
: msgbox rs!Field1
: rs.MoveNext
: Loop
:
: 'CLOSE/RELEASE RESOURCES
: xit:
: If rs.State <> adStateClosed Then rs.Close
: If cn.State <> adStateClosed Then cn.Close
: xit2:
: Set rs = Nothing
: Set cn = Nothing
: Exit Sub
:
: erh:
: ErrCount = ErrCount + 1
: MsgBox Err.Description, vbCritical, Err.Number
: If ErrCount > 1 Then Resume xit2 Else Resume xit
: End Sub
:
:
:
: 'DAO
: 'Need to set a reference to-
: 'Microsoft DAO 3.6 Object Library
: Sub ConnectDAO()
: Dim wk As DAO.Workspace
: Dim db As DAO.Database
: Dim rs As DAO.Recordset
:
: Set wk = DAO.DBEngine(0)
: Set db = wk.OpenDatabase("d:\db2.mdb") 'OPEN DB
: 'Set rs = db.OpenRecordset("table1", dbOpenSnapshot) 'READONLY
: Set rs = db.OpenRecordset("table1", dbOpenDynaset) 'WRITE
:
: rs.AddNew 'ADD A NEW RECORD
: rs!FIELD1 = "new rec" 'ASSIGN VALUE TO A FIELD
: rs.Update 'UPDATE/SAVE RECORD TO TABLE
:
: 'rs.Edit 'EDIT RECORD
: 'rs!FIELD1 = "changed rec" 'ASSIGN NEW VALUE
: 'rs.Update 'UPDATE/SAVE
:
: 'TRAVERSE AND OUTPUT RECORDS
: 'Do While Not rs.EOF
: ' MsgBox rs!FIELD1
: ' rs.MoveNext
: 'Loop
:
: rs.Close
: db.Close
: wk.Close
: Set rs = Nothing
: Set db = Nothing
: Set wk = Nothing
: End Sub
:

Re: Few questions on updating datas into Access

Tue, 10 Jul 2007 04:01:07 -0700

how about using a oledb connection?
: Hi guys, i have a few question listed below:
:
: I have username and password textbox in a form. When i click the
: login button on the same form, it has to connect to the database and
: retrieve the data, matching both username and password and at the
: end determine whether to display the next form.
:
: 1. How do i connect the database?
: 2. What coding must i type into the Login button, eg: connect to the
: database (mdb.open or what-so-ever)
:
: Will be apprecate if could type me a example!
:
: Thanks thanks!!
: