473,325 Members | 2,771 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,325 software developers and data experts.

How to insert records in a table

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, 283 views)
Feb 8 '10 #1
4 1291
nico5038
3,080 Expert 2GB
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
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
3,080 Expert 2GB
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
Thanks alot !!Its working
Feb 9 '10 #5

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

Similar topics

2
by: george | last post by:
This is like the bug from hell. It is kind of hard to explain, so please bear with me. Background Info: SQL Server 7.0, on an NT box, Active Server pages with Javascript, using ADO objects. ...
9
by: Martin | last post by:
Hello, I'm new with triggers and I can not find any good example on how to do the following: I have two tables WO and PM with the following fields: WO.WONUM, VARCHAR(10) WO.PMNUM,...
14
by: serge | last post by:
I have a scenario where two tables are in a One-to-Many relationship and I need to move the data from the Many table to the One table so that it becomes a One-to-One relationship. I need to...
16
by: Philip Boonzaaier | last post by:
I want to be able to generate SQL statements that will go through a list of data, effectively row by row, enquire on the database if this exists in the selected table- If it exists, then the colums...
11
by: Sezai YILMAZ | last post by:
Hello I need high throughput while inserting into PostgreSQL. Because of that I did some PostgreSQL insert performance tests. ------------------------------------------------------------ --...
4
by: Chris Kratz | last post by:
Hello all, We have run into what appears to be a problem with rules and subselects in postgres 7.4.1. We have boiled it down to the following test case. If anyone has any thoughts as to why...
3
by: Bob Alston | last post by:
I have a routine to copy data to new versions of my app via insert into sql statements. Unfortunately, due to evolution of my app, sometimes the new version has more restrictive editing than an...
2
by: mivey4 | last post by:
Okay I have 2 tables: Table A - holds a list of new hardware serial numbers and their corresponding model (no constraints or indexes) Table B - holds a distinct list of current serial numbers...
4
by: Bob | last post by:
Hi all, I'm trying to import data, modify the data then insert it into a new table. The code below works fine for it but it takes a really long time for 15,000 odd records. Is there a way I...
0
chumlyumly
by: chumlyumly | last post by:
Hello scripters - OS: Mac OSX Language: PHP w/ MySQL database I've created an insert page where a user inputs his info, which then goes to four different tables in a MySQL database. The...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.