Question Locked

How to update many records in an Microsoft Access file?

0 helpful
4 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I have a huge table with many fields or columns and with more than 50,000 rows of data. But I have to insert data in one column-location with respect to another column-access randomly. By using action query I can update only one record at a time. Another solution I think is to use forms, i.e. by creating two text boxes, where access text box is for entering the random number and location text box for entering the actual required data. But for this solution I don't the coding, or atleast if i can get the tutorial material for how to use forms I can overcomke this problem.

Thanking you.

4 replies

by tony hopkinson

Update MyTable set NamedColumn = RandomColumn

You would have to build the sql statement to do that.
e.g.
SQLCommand = "Update MyTable Set " + strColumnToUpdate + "=" + ColumnToUpdateFrom

You could use meta data on the table to pick a column to update from randomly.

It would be interesting to know what you are actually doing, I'm at a dead loss as to what it could be.

by vamshna

I use an Access database file. I have to update huge number of records.

1. I have two columns and more than 50,000 rows.
2. Column names are emp_name and emp_dept
3. For the emp_name column I have already filled the names for more than 50,000.
4. Now I have to fill emp_dept for each emp_name respectively and also randomly(for this type of situation we can use the search and replace function available in the Access, but each and every time for entering these 50,000 records makes the work tougher)
5. Hence, for this situation, I think we can use Forms, where we will create two text boxes, namely emp_name for entering the employee names and emp_dept for employee department, if we enter employee name in the first text box then it should automatically be inserted to that particular row and also provide us to enter the department name also.
6. So, this is my requirement.
7. I think I am lacking the coding part for this problem.

Thank you for the response.
Bye

by tony hopkinson

Create another table tmpEmployees
EmpNo,EmpName, EmpDept
Make EmpNo an AutoNumber (identity(1,1)
Insert into tmpEmployees
Select EmpName,EmpDept From Employees.
get rid of your original and rename tmpEmployees.

Then Create a form to show name and dept from the query
Select * from Employees where EmpNo = RND(50000)
and just let the user update the record through that.
That of course will only set one record at a time
You could set a random spread of employees to same dept with this
sql
Declare @Norecs int
Select @NoRecs = rnd(50000)
While @Norecs > 0
Begin
Update Employees Set EmpDept = Forms!Formname!Controlname where EmpNo = rnd(50000)
Select @NoRecs = @NoRecs - 1
end

Forms!Formname!Controlname
might be Forms!DeptForm!Deptname

HTHs

or at least gives you a vague clue anyway

This discussion is closed to new replies.