Database, inserts, etc

[Adrian Parker]

Hi. I'm using VB 6.0, and I'm connecting to an Access database. I open two
tables which are joined.

I'm trying to insert a new record, and only have one of the tables affected.
I'm not having much success. I can open the records, surf through them,
etc. I find the .ADDNEW ado function doesn't work properly when you have
multiple tables open.

Each table has an auto numbered field which serves as the primary key
(students.studentCode, and instructors.instructorCode)
Dim db As New Connection
Dim adoRecordSet As New Recordset

db.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & _
"\ddt.mdb;Mode=ReadWrite|Share Deny None;Persist Security
Info=False"

adoRecordSet.Open "select * from students, instructors " _
& "WHERE students.instructorCode = 1 AND students.instructorCode =
instructors.instructorCode", _
db, adOpenStatic, adLockPessimistic

db.Execute ("INSERT INTO students (students.firstName,
students.lastName, students.instructorCode) " _
& "VALUES ('First', 'Last', '7')")
Why does this insert not work? The syntax looks right to me. When I use it
I get error -2147217900 (that's a pretty big negative number)

Is there a better way to add a new record? How do I make sure that my
record set can see the new record, and hence have my controls be able to
display the new record? IE: I press the Next or Previous controls to
traverse the records, will my new record show?
Also, for deleting records is this my best bet:
db.Execute ("DELETE FROM students WHERE studentCode = " & txtStudentCode)

Adrian

[Adrian Parker]

"Adrian Parker" <ad***********@NOSPAMsympatico.ca> wrote in message
news:3d*******************@news20.bellglobal.com.. .

Ok, I got a new record inserted, db.Execute ("INSERT INTO students
(firstName, lastName, instructorCode) " _
& "VALUES ('New 2', 'Addition 2', '7')")

But using adorecordset.movenext or adorecordset.movelast cannot traverse to
the new entry.

I tried adorecordset.requery. This should show the new record, but does
not. If I add a new record, close the program, then reopen it, the new
record is loaded with the query.

After I do an insert, how do I get the adorecordset (Recordset object) to
include this new row?

Dim db As New Connection
Dim adoRecordSet As New Recordset

db.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & _
"\ddt.mdb;Mode=ReadWrite|Share Deny None;Persist Security
Info=False"

adoRecordSet.Open "select * from students, instructors " _
& "WHERE students.instructorCode = 1 AND students.instructorCode =
instructors.instructorCode", _
db, adOpenStatic, adLockPessimistic

db.Execute ("INSERT INTO students (students.firstName,
students.lastName, students.instructorCode) " _
& "VALUES ('First', 'Last', '7')")

Also, I'm loading the data into forms using:

Set txtFirstName.DataSource = adoRecordSet
txtFirstName.DataField = "students.firstName"

Set txtLastName.DataSource = adoRecordSet
txtLastName.DataField = "students.lastName"

And I'm traversing the records with statements like adoRecordSet.MoveFirst,
adoRecordSet.MoveLast etc.

Adrian

[John Lauwers]

When you do a requery than you do a movelast to see the newest record

greets John
"Adrian Parker" <ad***********@NOSPAMsympatico.ca> schreef in bericht
news:G6********************@news20.bellglobal.com. ..

"Adrian Parker" <ad***********@NOSPAMsympatico.ca> wrote in message
news:3d*******************@news20.bellglobal.com.. .

Ok, I got a new record inserted, db.Execute ("INSERT INTO students
(firstName, lastName, instructorCode) " _
& "VALUES ('New 2', 'Addition 2', '7')")

But using adorecordset.movenext or adorecordset.movelast cannot traverse to the new entry.

I tried adorecordset.requery. This should show the new record, but does
not. If I add a new record, close the program, then reopen it, the new
record is loaded with the query.

After I do an insert, how do I get the adorecordset (Recordset object) to
include this new row?

Dim db As New Connection
Dim adoRecordSet As New Recordset

db.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & _ "\ddt.mdb;Mode=ReadWrite|Share Deny None;Persist Security
Info=False"

adoRecordSet.Open "select * from students, instructors " _
& "WHERE students.instructorCode = 1 AND students.instructorCode = instructors.instructorCode", _
db, adOpenStatic, adLockPessimistic

db.Execute ("INSERT INTO students (students.firstName,
students.lastName, students.instructorCode) " _
& "VALUES ('First', 'Last', '7')")

Also, I'm loading the data into forms using:

Set txtFirstName.DataSource = adoRecordSet
txtFirstName.DataField = "students.firstName"

Set txtLastName.DataSource = adoRecordSet
txtLastName.DataField = "students.lastName"

And I'm traversing the records with statements like

adoRecordSet.MoveFirst, adoRecordSet.MoveLast etc.

Adrian

[Steve Gerrard]

"Adrian Parker" <ad***********@NOSPAMsympatico.ca> wrote in message
news:G6********************@news20.bellglobal.com. ..

"Adrian Parker" <ad***********@NOSPAMsympatico.ca> wrote in message
news:3d*******************@news20.bellglobal.com.. .

Ok, I got a new record inserted, db.Execute ("INSERT INTO students
(firstName, lastName, instructorCode) " _
& "VALUES ('New 2', 'Addition 2', '7')")

But using adorecordset.movenext or adorecordset.movelast cannot traverse to the new entry.

adoRecordSet.Open "select * from students, instructors " _
& "WHERE students.instructorCode = 1 AND students.instructorCode = instructors.instructorCode", _
db, adOpenStatic, adLockPessimistic

As I read it, your new record has instructorCode 7, and your recordset
is filtering for records with instructorCode 1, so it is correctly not
showing the new record after a refresh.

[Adrian Parker]

"Steve Gerrard" <no*************@comcast.net> wrote in message
news:s8********************@comcast.com...

"Adrian Parker" <ad***********@NOSPAMsympatico.ca> wrote in message
news:G6********************@news20.bellglobal.com. ..

"Adrian Parker" <ad***********@NOSPAMsympatico.ca> wrote in message
news:3d*******************@news20.bellglobal.com.. .

Ok, I got a new record inserted, db.Execute ("INSERT INTO students
(firstName, lastName, instructorCode) " _
& "VALUES ('New 2', 'Addition 2', '7')")

But using adorecordset.movenext or adorecordset.movelast cannot

traverse to

the new entry.

adoRecordSet.Open "select * from students, instructors " _
& "WHERE students.instructorCode = 1 AND students.instructorCode = instructors.instructorCode", _
db, adOpenStatic, adLockPessimistic

As I read it, your new record has instructorCode 7, and your recordset
is filtering for records with instructorCode 1, so it is correctly not
showing the new record after a refresh.

I noticed after.

My big concern now is how do I do a query, which finds the value of just one
field, and stores that in a small variable (int or something). I don't want
to use an entire recordset just to get one field.

Adrian