[MOVED] VB6 code to connect ms access database

[djc]

hi,
i am working on a log in form in VB6. For that i have created a database in ms access. I made all the settings and it is saying that connection tested successfully. Still i am unable to insert the daata in the database. Constant error is there. I am not getting how to insert the data in data base. Here is the code

---

Code Visual Basic - [expand]
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
Option Explicit
 
Public CON As adodb.Connection
Public CMD As adodb.Command
Public rs As adodb.Recordset
Dim mstrSQL As String
 
Public Sub ConnectToDB()
    Dim conString As String
    
        conString = "Provider=Microsoft.Jet.OLEDB.4.0;" _
                    & "Data Source=D:\DH_VB6\db2.mdb" _
                    '& App.Path & "D:\DH_VB6\db2.mdb"
    Set CON = New adodb.Connection
    With CON
     .ConnectionString = conString
     .Open
    End With
'MsgBox "hw r u"
End Sub
 
Public Sub DisconnectFromDB()
 
    Set CMD = Nothing
    CON.Close
    Set CON = Nothing
 
End Sub
Private Sub Form_Load()
ConnectToDB
End Sub
 
Private Sub Command1_Click()
 
Set rs = New adodb.Recordset
mstrSQL = "Insert into Usr_Pwd (Username,password) Values ('" & Text1.Text & "', '" & Text2.Text & "')"
Set rs = CON.Execute(mstrSQL) /////////////Syntax error in Insert into command////////////
DisconnectFromDB
MsgBox "i m fine"
End Sub

---

Pleasse guide

 

[speedEC]

Re: VB6 code to connect ms access database

what error you are getting?

 

[djc]

Re: VB6 code to connect ms access database

I have mentioned an error in line number 37. Problem is solved. However now i want to check that whether entered username is already present or not. How to check that. How to make reference to ms access database.

 

[speedEC]

Re: VB6 code to connect ms access database

Before inserting username text and password in to the table, check whether the username already exists in the table. If exists, do not insert username text and return with user message (for example, username already exists and select another name). etc.

For example - sequence,

---

Code Visual Basic - [expand]
1
2
3
4
5
6
7
8
If exists(select username from dbo.tablename where username = "username text entered")
 begin
    Error Msg as "UserName Exists. Pl choose another name"
 end
else
 begin
    insert in to dbo.tablename.....
 end

---

 

[djc]

Re: VB6 code to connect ms access database

Thanx speedEC, is this syntax valid in VB6. I am using ADODB connection.

- - - Updated - - -

I am looking for the proper function or syntax. I found one 'DoLookUp()' but not getting how to use it.

 

[speedEC]

Re: VB6 code to connect ms access database

is this syntax valid in VB6

No. this is just sequnce

I am looking for the proper function or syntax

try this code:

Method 1:

---

Code Visual Basic - [expand]
1
2
3
4
5
6
7
8
9
10
mstrSQL = select userName from Usr_Pwd where  Username = Text1.Text
 
Set rs = CON.Execute(mstrSQL)
 
if (rs.RecordCount > 0) then{ ' means username already exists
  msgbox "duplicate record exists"
}
else{ // no duplicate username exists
      insert into Usr_Pwd......
}

---

Method 2:

---

Code Visual Basic - [expand]
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
mstrSQL = select userName from Usr_Pwd
Set rs = CON.Execute(mstrSQL)
int i = 0;
dim duplicateRecord as int
 
while Not rs.EOF
  if (rs(i) = Text1.Text) then
    duplicateRecord = 1
  end if
  i++
 move rs.MoveNext
Wend
 
i = 0;
 
if (duplicateRecord = 1) then
   duplicateRecord = 0
   msgbox "duplicate record exists"
 exit sub
else
  insert into Usr_Pwd......
end if

---

Note: there may be syntax error. Because, i am also using C, C++ codes. thats why mixing with C code in some places happened.

 

[djc]

I applied Method 2. I adjusted rest of the things, but "rs(i) " is giving an error.
I found one SQL query, rs.Open "select Username from Usr_Pwd where Username= '" & Text1.Text & "'", CON, adOpenForwardOnly, adLockReadOnly, adCmdText but not getting how to proceed further, i.e. where the searched data will be stored and with what we have to compare to get the result. Have you heard of this query.

 

[speedEC]

Only test the following code and see the result.

mstrSQL = select userName from Usr_Pwd
Set rs = CON.Execute(mstrSQL)

comment the following portion

'dim i as Integer
'dim duplicateRecord as Integer

'i = 0
'duplicateRecord = 0

'while Not rs.EOF
' if (rs(i) = Text1.Text) then
' duplicateRecord = 1
' end if
' i = i + 1
' rs.MoveNext
'Wend

- - - Updated - - -

what about the first method?

pmk

 

[djc]

what about the first method?

pmk

First method is giving an error in

Set rs = CON.Execute(mstrSQL)

 

[speedEC]

the given sql (ms db access) query is correct.

Method 1:

it should retrieve only matching records of your input username.

Method 2:

select userName from Usr_Pwd ---- may be within quote?!

for example: mstrSQL = "select userName from Usr_Pwd"

This statement should retrieve all the records from Usr_Pwd table, if record exists. If no record exists in the table, it will return 0 records.

If there is any error just to debug and find out the error. I don't have older ms access. therefore i cannot able to check the format.

try and update.

- - - Updated - - -

try this syntax;

mstrSQL = "select userName from Usr_Pwd where userName = '" & text1.text & "'"

Note: debug here on value of text1.text and ensure cursor shows the input userName text.

- - - Updated - - -

just now I downloaded MS access 2007 engine and tested.

Just try changing the syntax as follows:

Method 2:

mstrSQL = "select * from Usr_Pwd "

I can able to see all the records using msgbox cmd.

try this. time is too late. will clarify tomorrow.

 

[djc]

Hi speed, thanks for your valuable time. I am using following code and query. Its working now.

                If (IsNull(Text1.Text) Or Text1.Text = "" Or IsNull(Text2.Text) Or Text2.Text = "") Then
                MsgBox "Invalid data"
                Else
                Set rs = New ADODB.Recordset
                i = 0
                rs.Open "select Username from Usr_Pwd where Username=  '" & Text1.Text & "'", CON, adOpenForwardOnly,      
                            adLockReadOnly, adCmdText   ////////////////   This Query is working now//////////////////////
                While (rs.EOF = False)
                rs.MoveFirst
                If rs.Fields(0) = Text1.Text Then
                MsgBox "username already exists"
                i = 1
                    If (i = 1) Then
                    Exit Sub
                    End If
                Else
                rs.MoveNext
                End If
                Wend
                
                If (i = 0) Then
                CON.Execute mstrSQL
                End If
        End If

However sorry to trouble you again, now i am struggling with the code to change existing password and delete the existing user. Which SQL queries can be used for that. DO you have any idea.

 

[djc]

Private Sub Form_Load()

Dim conString As String
    
        conString = "Provider=Microsoft.Jet.OLEDB.4.0;" _
                    & "Data Source=D:\Dheeraj\VB6_DH\db5.mdb" _
                    & "Jet OLEDB:Database Password=dheeraj;" _

                    '& "App.Path & Persist Security Info=False;"
    Set CON = New ADODB.Connection
    With CON
     .ConnectionString = conString
     .Open
    End With

End Sub

Hi,
here is my code to connect ms access database with password protection. However it is giving an error 'Could not use ";file already in use' can you please tell me what could be the issue. Now it says 'Could not find installable ISAM' Error keeps on changing for different settings.