Update Database

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi
Can someone please advise how we can do a find for a record so we can amend
rather than going through every one of the records before reaching the one we
are after.

The database we have is very large and at the momnet the code we have in VBA
checks each record one after another until the record we are after is found
but since there is a large volumne of data is there a way to do a find
instead.

below is the code we currently have.

Set db = CurrentDb
Set qy = db.CreateQueryDef("", "SELECT tblRedemptions.* FROM
tblRedemptions WHERE (((tblRedemptions.RedemptionID) Is Null) AND
((tblRedemptions.StoreNo)= " & Me.StoreNo & "));")
Set rs = qy.OpenRecordset

Do Until rs.EOF
rs.Edit
rs.Fields(2) = Me.Combo7
rs.Fields(3) = Date
rs.Fields(5) = RedemptionID
rs.Update
rs.MoveNext
Loop

Thanks
Noemi
 
Why not go:

dim strSql as string

strSql = "update tblRedemptions " & _
set Field2 = " & me.combo7 & "," & _
"Field3 = #" & format(Date,"mm/dd/yyyy") & "#," & _
"Field5 = " & RedemptionID & _
" where StoreNo = " me.StoreNo

debug.print strSql
currentdb.Execute strSql

You have to change the Field2, 3 an 5 with the correct field names you have.

Furhter, make sure there is a index on StoreNo

And, if field2 is a text type field, you need to surround the me.combo7
value witth quotes

eg:

set Field2 = '" & me.combo7 & "'," & _

After you get it working...you can remove the debug.print (it is there so
you can go into the debugger..and cut/past the sql into the query builder
for testing....


Also, how many records are you trying to update? it should go quite
fast......
 
Hi Albert

i have just add your code to mine however it doesn't like the word set
before Field2, can you please help me with this.

Thanks
Noemi
 
There is a " missing right before the "set"

eg:
strSql = "update tblRedemptions " & _
"set Field2 = " & me.combo7 & "," & _
"Field3 = #" & format(Date,"mm/dd/yyyy") & "#," & _
"Field5 = " & RedemptionID & _
" where StoreNo = " me.StoreNo

debug.print strSql
 
Back
Top