Beginner needs Help: SQL Script in Visual Basic

[nicklad]

Hi
I dont know if anyone can help me with this, but i am in desperate need of some assistance with SQL
I am trying to do a "user entry screen" in visual basic that is linked to info in an Access database, and i was told to give SQL a go.
Basically i have three fields, "StationID", "ProgramID" and "Password". I was wondering if someone could tell me, or tell me where I could find how to, write a SQL string that can be used in VB that looks at the "StationID" the user has entered in a VB text field and then looks up that "StationID" in the database table "UserAccess" and checks that the "ProgramID" and "Password" matches that user's info is correct before it lets the user in to the program.
Im hopeing that it wont be that difficult, and I apoligise if this is quite a time consuming task, but I would appreciate it SOO much.

Thanking you in advance
~ nick

 

[PruSQLer]

This should work:

Dim db as database
dim rs as recordset

db = open database ("c:\YourDatabase.mdb&quot
rs = db.openrecordset("select password, programID
from YourTable
Where stationID = " & txtStation.text
If rs.fields("password&quot = txtPW.text and
rsfields.programID = txtProdID.text
Then "let 'em in"

 

[nicklad]

i get "Compile Error: Invalid use of property" error when i run
Private Sub cmdEnter_Click()
Dim db As Database
Dim rs As Recordset

db = OpenDatabase("C:\Nick\School Work\Software\Radio\iW\RadioiW.mdb&quot
rs = db.OpenRecordset("Select Password, ProgramID From UserAccess
Where StationID = " & txtStat.text"
If rs.Fields("Password&quot = txtPass.text And
rs.Fields("ProgramID&quot = txtProdID.Text Then
frmEntryScreen.Visible = True 'Loads frmEntryScreen
Unload frmStartup 'Unloads frmStartup

End If

End Sub

 

[PruSQLer]

Do you know which line of code you're getting the error on?

 

[nicklad]

yeh, the "db = " is highlighted, so i guess thats the line of code.
also:
the following line has an error in it (ie it's red)

rs = db.OpenRecordset("Select Password, ProgramID From UserAccess
which i changed to
rs = db.OpenRecordset("Select Password, ProgramID From UserAccess&quot, and now it seems error-free (but am i right this time?)


feel free to email me at tranceinyourpantz@hotmail.com
thank u very much for all ur help

 

[mostevil]

As databases and recordsets are objects you need to use 'set'. Off the top of my head:

set rs = db.openrecordset(.....

you probably need to create them as new objects too.

ie.
Dim db As New Database

But this is more of a VB issue than SQL. You should really be using ADO objects for these things (connection object / DataEnvironment) See the MSDN help files.