MS access vb6 odbc and dsn

[dipankar_pr]

Hi,All,
I am try to develop a project where the back en database is MS access, front end is vb6 and connectivity establish by odbc.
the database content multiple table.
Frost table student contain roll, name
second table marks contain roll, math, English
there is four text box namely txtRoll, taxonomy, txtMath, txtEnglish the code for is given below

Dim Cn As Connection
Dim Rs As Recordset
Dim cString As String

Private Sub Form_Load()
Set Cn = New Connection
cString = "DSN=kaka;Uid=admin;Pwd="
With Cn
.ConnectionString = cString
.CursorLocation = adUseServer
.Open
End With

Set Rs = New Recordset
cString = "SELECT rollp, namep, matp, Inglis FROM student, marks WHERE student.rollP=marks.rollP"
Rs.Open cString, Cn, adOpenStatic, adLockOptimistic, adCmdTable

txtRoll.Text = Rs.Fields("Roll")
txtName.Text = Rs.Fields("Namep")
txtMath.Text = Rs.Fields("Mathp")
txtEnglish.Text = Rs.Fields("Englishp")

Rs.Close
Cn.Close

End Sub

When I run this program I got an Error massage -
Run-time error '-2147217900(80040e14)':
[Microsoft][ODBC Micro Access Driver] Syntax error in FROM clause.
Please Help me.

[vb5prgrmr]

You need an inner join statement in your from clause... The easiest way to get this correct is to use Access's query design window...

So go to the query tab/window of access and double click on create query in design view. Add the two tables when the window pops up. Now, from here you can either select all the fields or just that * symbol. From here you can check the result by selecting datasheet view, but the view you are after is the SQL view.

What you should see is something like this (but it may have more parens)

SELECT student.*, marks.* FROM student INNER JOIN marks on student.roll=marks.roll

Now copy this from access to vb and you are done! And just so you know, access's query design tools are great to design some of your more complex queries and as you can tell, it allows you to see instantly if you are getting the results you are expecting.

Also, another note. When it comes to the field that your tables are joined on, you will need to prefix the field with the table name...

txtRoll.Text = Rs.Fields("student.Roll").Value

Good Luck

[dipankar_pr]

vb5prgrmr Thank you.
Your sugession working fine about SQL statement preparation in access.
Again thank you.