×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

how can I update database ?

how can I update database ?

how can I update database ?

(OP)
Database is in Access 2000, I am using ADO. I can open the recordset, but can't seem to be able to add new records. Here is some of my code, perhaps someone can point me in the right direction:

strSQL = "SELECT * FROM Emp_Passwords"
Set Con = New ADODB.Connection
Con.Open "DSN=Training;UID=;PWD=;"
Set rs = Con.Execute(strSQL)


With rs
.AddNew
!EmpPassword = arrPassword(Index)
!Emp_No = EmpNo(Index)
.Update
End With
Replies continue below

Recommended for you

RE: how can I update database ?

use a sql query
like you did when you selected it
"update IPTimer set timed = '%s', mode = '0' where IP = '%s'"

where %s would be replaced with whatever value you wanted
same thing with adding, or inserting
"insert into Table set (Field1, Field2) values ('f1', 'f2')"
that kina thing, if you need to know more on using SQL strings to do almost all your database opening and changing task, check your SQL Server, or Acess help file for SQL Query.

Karl
kb244@bellsouth.net

Experienced in , or have messed with : VC++, Borland C++ Builder, VB-Dos, VB1 thru VB6, Delphi 3 pro, Borland C++ 3(DOS), Borland C++ 4.5, HTML, ASP(somewhat), QBasic(hehe, yea it was 4.5 too, least i didnt start with COBOL)

RE: how can I update database ?

(OP)
thanks karl, but after I set my sql, do I do an Execute again?

RE: how can I update database ?

yes, but if you do a select sql command "select ...." you need to close it
but insert, delete, update, anything that does it right away, autmatically closes itself
also another helpful thing if you didnt know already is like
select * from table where SearchableField = '1'
it'll only throw records int ther where their feild = 1
helps me when i need to remove, something or check to see if a groupe of certain types of records exist, so on.

Karl
kb244@bellsouth.net

Experienced in , or have messed with : VC++, Borland C++ Builder, VB-Dos, VB1 thru VB6, Delphi 3 pro, Borland C++ 3(DOS), Borland C++ 4.5, HTML, ASP(somewhat), QBasic(hehe, yea it was 4.5 too, least i didnt start with COBOL)

RE: how can I update database ?

(OP)
ok, I tried that, and now I am getting an error saying "too few parameters. Expected 2"

Heres the code I added:

strSQL2 = "UPDATE Emp_Passwords set Emp_Password = " & arrPassword(Index) _
& ", mode = '0' Where No_Employe =" & Val(EmpNo(Index))
Set rs = Con.Execute(strSQL2)

I have this inside a loop, that shouldn't matter though, right?

RE: how can I update database ?

depends tho you might just have the index a lil off, if the actual sql command gerated an error, you wouldnt
get a syntax message, much more rather a critical COM type error. so I'm probally guessing that command that uses the index
is the prob.
also you may require 3 parameters if its a connection object
cnn1.Execute strSQLRestore, , adExecuteNoRecords
kinda like that, check your MSDN help, or goto msdn.microsoft.com
i'll look more into it later today.

Karl
kb244@bellsouth.net

Experienced in , or have messed with : VC++, Borland C++ Builder, VB-Dos, VB1 thru VB6, Delphi 3 pro, Borland C++ 3(DOS), Borland C++ 4.5, HTML, ASP(somewhat), QBasic(hehe, yea it was 4.5 too, least i didnt start with COBOL)

RE: how can I update database ?

(OP)
I am not getting any more error messages, but its not adding the records to my database

RE: how can I update database ?

are you trying to Add, or update, an existing record, use Insert into if yer trying to add a brand new record

"insert into Table set (table1, table2, table3) values (value1, 'value2', value3)"

Karl
kb244@bellsouth.net

Experienced in , or have messed with : VC++, Borland C++ Builder, VB-Dos, VB1 thru VB6, Delphi 3 pro, Borland C++ 3(DOS), Borland C++ 4.5, HTML, ASP(somewhat), QBasic(hehe, yea it was 4.5 too, least i didnt start with COBOL)

RE: how can I update database ?

(OP)
I decided to try Insert Into - but says I can't insert a record, as one of the fields I am inserting into is linked to another table - how can I do this? There is a value already in the second table, I don't want to change this value, just have it link to first table.


strSQL4 = "insert into Emp_Passwords values (' & arrPassword(Index)" _
& "', '" & Val(EmpNo(Index)) & "', " & examcode & "')"

Set rs = Con.Execute(strSQL4)


the value in examcode is a primary key in Exam_Table

RE: how can I update database ?

oh hehe its
"insert into IPTimer (IP, timed, mode) values ('value1', 'value2', '0')"
like that, into table (Fields) values (fieldvalues)
to add a brandnew record

Karl
kb244@bellsouth.net

Experienced in , or have messed with : VC++, Borland C++ Builder, VB-Dos, VB1 thru VB6, Delphi 3 pro, Borland C++ 3(DOS), Borland C++ 4.5, HTML, ASP(somewhat), QBasic(hehe, yea it was 4.5 too, least i didnt start with COBOL)

RE: how can I update database ?

(OP)
I posted that last one too soon lol ok, heres my new query string - the first two values I want to add to Emp_passwords, the last value, is already entered into Exam_Table, but if I leave that parameter off my SQl statement, I get an error, 1 parameter needed

strSQL4 = "insert into table set(Emp_Passwords, Emp_Passowrds, Exam_Table) _
values (' & arrPassword(Index)" & "', '" & Val(EmpNo(Index)) & "' ," & examcode & ")"

RE: how can I update database ?

(OP)
lol I am going to stop posting so fast - I will retry with your last suggestion :o)

RE: how can I update database ?

(OP)
ok, I tried, but still won't add a record because the table is linked to another - any more suggestions? Heres my SQL command, just so you can double check it :o)

strSQL4 = "insert into Emp_Passwords(Emp_Password, No_Employe, Exam_No) _
values (' & arrPassword(Index)" & "', '" & Val(EmpNo(Index)) & "' ," & examcode & ")"

RE: how can I update database ?

hmm, interesting, when you insert a record, all fields must be accounted for unless in Access the field is marked as not required, and as far as inserting joint fields, i am really uncertain about that. But I'll be leaving work at about 12:15(early i know, but i have an appointment, then i am home the rest of the day), I will concentrate on your problem once i get home. and see if yuo can acheive the same task without trying to join the tables, oh and try putting a space bettween that table name and the first (
oh also , i dont know if you didnt notice it yourself, but your arrPassword(index) is inside the quotation,
you have
"insert into Emp_Passwords(Emp_Password, No_Employe, Exam_No) _
values (' & arrPassword(Index)" & "', '" & Val(EmpNo(Index)) & "' ," & examcode & ")"

should be
"insert into Emp_Passwords (Emp_Password, No_Employe, Exam_No) _
values ('" & arrPassword(Index) & "', '" & Val(EmpNo(Index)) & "' ," & examcode & ")"

there that should do it

Karl
kb244@bellsouth.net

Experienced in , or have messed with : VC++, Borland C++ Builder, VB-Dos, VB1 thru VB6, Delphi 3 pro, Borland C++ 3(DOS), Borland C++ 4.5, HTML, ASP(somewhat), QBasic(hehe, yea it was 4.5 too, least i didnt start with COBOL)

RE: how can I update database ?

(OP)
fixed that, but still getting the same error message:

-2147217900 (80040e14)
[Microsoft][ODBC Microsoft Access Driver] You cannot add or change a record because a related record is required in table 'Exam_Table'

RE: how can I update database ?

did you check to make sure the Table actually exist in the database, and if yer putting in a _ cuz theres a space there, thats not the same the tables are case snetative, and you shouldnt be putting spaces into the table name, and make sure they are listed in the System DSN on the ODBC32 in your control pannel.

Karl
kb244@bellsouth.net

Experienced in , or have messed with : VC++, Borland C++ Builder, VB-Dos, VB1 thru VB6, Delphi 3 pro, Borland C++ 3(DOS), Borland C++ 4.5, HTML, ASP(somewhat), QBasic(hehe, yea it was 4.5 too, least i didnt start with COBOL)

RE: how can I update database ?

(OP)
yes, the table does exist, and I put the _ because thats how I named the field in the table. And yes, it is listed in the System DSN. I think the problem may be the way my relationships are on the database. I am going to take the weekend to look it over, perhaps I can figure out where I went wrong. Again, thanks so much for all your help. :o)

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close