By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,294 Members | 2,708 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,294 IT Pros & Developers. It's quick & easy.

How to insert records in a table

P: 66
With reference to the attachment my Query is I want to insert 1 common value for the Columns Ref No & Address.

i.e If there are total 6 records in my table,The column Ref No should hold value 310/SA & the column Address should hold value Bedok .


Here the problem is these 2 columns values lies same for all other worksheets,whereas other columns always 've a differing value.So in the given table i m trying to insert data in these 2 columns differently.
Below is my code to insert the values for columns Ref No & Address.
Expand|Select|Wrap|Line Numbers
  1. rs.MoveFirst
  2. For i = 0 To rs.RecordCount
  3. rs.Edit
  4. rs.Fields(7) = CStr(Range("D39").Value)
  5. rs.Fields(9) = CStr(Range("A33").Value)
  6. rs.Update
  7. rs.MoveNext
  8. i = i + 1
Attached Files
File Type: txt outer2.txt (293 Bytes, 225 views)
Feb 8 '10 #1
Share this Question
Share on Google+
4 Replies


nico5038
Expert 2.5K+
P: 3,072
As you need all values set, you can use a faster UPDATE query instead like:
Expand|Select|Wrap|Line Numbers
  1. UPDATE tblYourTableName SET [Ref No] = "310/SA", [Address]="Bedok"
  2.  
Nic;o)
Feb 8 '10 #2

P: 66
As suggested I m using the below code for updating the table.
Can u Plz help me out with the syntax.Below is my code but getting error..

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim str As String
Set db = DAO.DBEngine.OpenDatabase("C:\---")
Set rs = db.OpenRecordset("outer2", dbOpenDynaset)

db.Execute ("update [outer2] set [outer2].[Refno]=CStr(Range("D39").Value)")
MsgBox "done"
End Sub
Getting the error as expected list separator.
Feb 9 '10 #3

nico5038
Expert 2.5K+
P: 3,072
Just use:
Expand|Select|Wrap|Line Numbers
  1. currentdb.execute ("update [outer2] set [outer2].[Refno]='" & CStr(Range("D39").Value) & "'")
  2.  
Nic;o)
Feb 9 '10 #4

P: 66
Thanks alot !!Its working
Feb 9 '10 #5

Post your reply

Sign in to post your reply or Sign up for a free account.