How to insert new Records in a table using SQL in Access97

[hallian92]

Hi,
I am using Access97 and am trying to insert new Records in my code. Before adding new records to table I am doing some update in the same table and after that I am inserting the new records it's in the same sub. I am using the following to add the new records:

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70 'select
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70 'copy
DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste Append

but once I do this a dialog-box pops up and says:

This record has been changed by another user since you started editing it ........ and have three buttons to select from i.e.
Save Record
Copy To Clipboard
DropChanges

Now my question is there any other way to add the new records besides using DoCmd.DoMenuItem. I'll really appreciate for the help.
Thanks!

 

[adrikev]

hallian92,

Yes there is another way to do this that is better. You will use the DoCmd.RunSQL statement.

Basically, the easiest way to implement this is to build an append query and change to SQL view from the design grid. Then use the SQL to build the statement in VBA.

Search Access help for "DoCmd.RunSQL", there you will find the syntax needed to build the statement.

Let me know if you need further assistance. --------------------------------
Fortius, Citius, nullis secundus
Strength, Speed, second to none

 

[hallian92]

Hi,
It's not adding new data in my table following is the code:

For Number = 1 To 10
DoCmd.RunSQL "INSERT INTO Items (Notes) VALUES ('Harry');"
Next Number

is there a problem in the code above. Any help is appreciated.
Thanks

 

[FireViking]

Hi,

I like to have a little more control in adding and editing information. The following code is a guide and may assist you.

Dim db as dao.database
Dim rs as dao.recordset
dim strSQL as string

strSQL = ("SELECT * FROM table&quot
set db = currentdb
set rs = db.openrecordset(strSQL)

If not rs.bof then
rs.movefirst
else
end if

do while not rs.eof
if rs(field1)= "???" then
rs.edit
rs(field2) = "Harry"
else
end if
rs.update
rs.movenext
loop

rs.close
db.close

This is simple code that is an alternative and may help you.