accessing ms access from VB

[anud18]

hi....i m actually developing a tool which uses access as the database source...i hv created 2 coloumns in the database....username and password....now in my tool i need to ask the user fr his username and password....if it matches with any of the combinations stored in the database then the user can login ....cn anyone help me wiith dis ....

[DenisOxon]

You need to open the Access table, and then step through each record testing the value the user has entered with the value in each record. If user name is same then check password to be same.

How far have you got with writing VB to open tables and read contents ?

[anud18]

actually i know the steps to do it......but m nt being able to figure out how to connect to the database by using ADO......wen i try to open the database using the following code it opens up a SELECT DATA SOURCE DIALOGUE BOX.....plz help me wid this

Dim dbUserName As Database
Dim rsUserName As Recordset

Set dbUserName = OpenDatabase(db1)

dbi is the database name stored on the desktop....
the third line of code which is posting a problem...

[DenisOxon]

actually i know the steps to do it......but m nt being able to figure out how to connect to the database by using ADO......wen i try to open the database using the following code it opens up a SELECT DATA SOURCE DIALOGUE BOX.....plz help me wid this

Dim dbUserName As Database
Dim rsUserName As Recordset

Set dbUserName = OpenDatabase(db1)

dbi is the database name stored on the desktop....
the third line of code which is posting a problem...

For the momemnt where is db1 defined in your code ?

I have an application which does what you want. I will look it up tonight and post.

[anud18]

thanx.... i hv been able to develope the tool......now i further want to modify it...

i.e now i want to connect/open up the database which is password protected....how do i do that through VB

[DenisOxon]

thanx.... i hv been able to develope the tool......now i further want to modify it...

i.e now i want to connect/open up the database which is password protected....how do i do that through VB

Thanks for letting us know.

I struggled to get a password protected database to open, so I won't guess but I'll look it up to night and post.

[anud18]

i wud be grateful 2 u....plz post it 2day

[rumi]

hi,im a newbie in vb.im developing a small system for practical project.i've used adodc data control to connect from vb to the database.i want to send datas from the form ive created to my database.however,i kept facing the same error that says :
run-time error 91
Object variable or with block variable not set

here are my codings:
Private Sub Cmd_Submit_Click()

Adodc1.Recordset.AddNew

Adodc1.Recordset.Fields("Contact_No").Value = Txt_Contact.Text
Adodc1.Recordset.Fields("Reserv_No").Value = Txt_Reserv_No.Text
Adodc1.Recordset.Fields("Reserv_Date").Value = Txt_Date.Text
Adodc1.Recordset.Fields("Booking_Time").Value = Txt_Time.Text
Adodc1.Recordset.Fields("Type_Code") = Cmb_Type.ListIndex
Adodc1.Recordset.Fields("Payment") = Txt_Payment.Text
Adodc1.Recordset.Fields("Receipt_No") = Txt_Receipt.Text

Adodc1.Recordset.Update

MsgBox "Record has been saved", vbOKOnly + vbInformation

End Sub

can anyone please help me?im extremely new with this..thanx..

[AV Manoharan]

Anud,

Please look in the thread Read Me. Access to any type of DB is possible
started by me.

Happy Programming
AV Manoharan

[jireh]

Private Sub Cmd_Submit_Click()

Adodc1.Recordset.AddNew

Adodc1.Recordset.Fields("Contact_No").Value = Txt_Contact.Text
Adodc1.Recordset.Fields("Reserv_No").Value = Txt_Reserv_No.Text
Adodc1.Recordset.Fields("Reserv_Date").Value = Txt_Date.Text
Adodc1.Recordset.Fields("Booking_Time").Value = Txt_Time.Text
Adodc1.Recordset.Fields("Type_Code") = Cmb_Type.ListIndex
Adodc1.Recordset.Fields("Payment") = Txt_Payment.Text
Adodc1.Recordset.Fields("Receipt_No") = Txt_Receipt.Text

Adodc1.Recordset.Update

MsgBox "Record has been saved", vbOKOnly + vbInformation

End Sub

yoiu'll probably get an error if you do not set the datasource of that adodc!

[rumi]

yes. infact that was my main mistake!ive tried other methods as well..thanx for reminding me bout that,jireh..

[jireh]

yes. infact that was my main mistake!ive tried other methods as well..thanx for reminding me bout that,jireh..

So does your problem is solved?

[rumi]

yes,quite but now im facing another problem.. i tried using loops to check whether Reservation made by customer have been occupied before saving the record,so i used these..

Private Sub CmdSave_Click()
Adodc1.Recordset.MoveFirst
Do While Not Adodc1.Recordset.EOF
If Adodc1.Recordset.Fields("Reserv_Date").Value = Txt_Date.Text And Adodc1.Recordset.Fields("Booking_Time").Value = Txt_Time.Text And Adodc1.Recordset.Fields("Fac_Type").Value = Cmb_Type.Text Then
Txt_Date.Text = ""
Txt_Time.Text = ""
Txt_Date.SetFocus
DisableButtons
CmdSave.Enabled = True
CmdAddNew.Caption = "&Cancel"
MsgBox "Reservation occupied! Please choose another date and time", vbOKOnly
Exit Sub
End If
Adodc1.Recordset.MoveNext
Loop
Adodc1.Recordset.Requery
If Adodc1.Recordset.Fields("Reserv_Date").Value <> Txt_Date.Text And Adodc1.Recordset.Fields("Booking_Time").Value <> Txt_Time.Text And Adodc1.Recordset.Fields("Fac_Type").Value = Cmb_Type.Text Then
Adodc1.Recordset.Update
EnableButtons
CmdSave.Enabled = False
CmdAddNew.Caption = "&Add New"
MsgBox "Records are succesfully saved!", vbOKOnly
End If
End Sub

but it never work..do u have any idea how?

[jireh]

try this one hope it would work because I've never use adodc...

Function savecheck() As Boolean
   Dim rs as [I]recordset for adodb sorry I don't know how[/I] 
   savecheck = True
Set rs= [I]open the recordset for that table ...[/I]
the query in your rs must be like this "select * from [tablename] where Reserv_Date+Booking_Time+Fac_Type = '" & Txt_Date.Text + Txt_Time.Text  + Cmb_Type.Text & "'"
   if Not rs.Eof then
      savecheck=False
      Msgbox "Already occupied!" 
      Exit Function
   End if
End If

Private Sub CmdSave_Click()
    If savecheck Then
       put your code here that will save your data entry
   End if
Ens Sub

[rumi]

hi there jireh..
i tried blending in your codes with the use of adodc but it nvr seem to work..i found the logic of your codes appropriate for my program but i just dont know how to make a query with the use of adodc..it seems that adodc simplifies the steps in connecting from vb to database(everything is almost auto)but i still cant figure out a way.i know you never use adodc before but if you happen to have any idea on resolving my problem please send feedbacks k..thanx so much

[elleinad]

How do i access only a column from MS access to VB?
I am also newbie on VB.

[roshani285]

hi....i m actually developing a tool which uses access as the database source...i hv created 2 coloumns in the database....username and password....now in my tool i need to ask the user fr his username and password....if it matches with any of the combinations stored in the database then the user can login ....cn anyone help me wiith dis ....

First create Connectivity with your database with DAta control or ADODC control or by ADODB . If you are useing ADODB connection then create recordset to access your table and then compare all values of ur table with username and password given by user.
like this way: if rs is recordset then code will be like---------
rs.open("select * from tbluser where loginname = '" & txtlogin.text & "' and password = '" & txtpassword.text & "'"),cn
if rs.recordcount > 0 then
''' login successfully
else
''' cant login
end if
----- try this code with your table name and column name....

[sackymatt]

option Explicit

Private Sub cmdAccept_Click()
Dim cn As New ADODB.Connection
Dim strCNString As String
Dim rs As New ADODB.Recordset
Dim Txt As String

   On Error GoTo ErrHandler
   'Connect to database
   strCNString = "Data Source=" & App.Path & "\databasename.mdb"
   cn.Provider = "Microsoft Jet 4.0 OLE DB Provider"
   cn.ConnectionString = strCNString
   cn.Properties("Jet OLEDB:Database Password") = ""
   cn.Open
   'Open recordsource
   With rs
       .Open "Select * from tablename where name='" & txtName.Text & "' and Password='" & txtPassword.Text & "'", cn, adOpenDynamic, adLockOptimistic
       'Check username and password
       If .EOF Then
           MsgBox "Access Denied...Password and name do not match,Please enter correct Details!", vbOKOnly + vbCritical, "Security Login"
           txtName.Text = ""
           txtPassword.Text = ""
           txtName.SetFocus
           cn.Close
       Else
           Txt = "" & " " & UCase$(txtName.Text) & ""
           MsgBox "Welcome!!!" & Txt, vbOKOnly + vbExclamation, "Security Login"
           
           cn.Close
           Unload Me
           frmform2.Show
           
       End If
   End With

ExitHere:
   Exit Sub

ErrHandler:
   MsgBox Err.Number & " " & Err.Description, vbCritical, "Login ..."
   cn.Close
End Sub

Private Sub Form_Load()
txtPassword.PasswordChar = "*"

End Sub

[sackymatt]

hope that code helps u anud