First Try at Updating via code Help please

[mdjks]

I have put together the following and just can't figure out what I'm missing. I want to update a field in a local Access (2000) table with the correct Account Rep found in a linked sql server table. What I wrote below appears to run in a loop but nothing actually gets updated.

I know it must be something simple but I've been searching the forum most of the day and am not putting 2+2 together.

I've been doing this with update queries but they take much too long to run and I need to figure out how to build more effective applications. The bit below is just for one field I actually will be reproducing this to populate numerous fields the docmd.openquery way takes nearly 30 minutes to run.

Your help would be greatly appreciated, thanks in advance.

Public Function UpdateAcctRep()
Dim db As dao.Database
Dim rs As dao.Recordset
Dim strSQL As String
Set db = CurrentDb
Set rs = db.OpenRecordset("tTempCompany")

While Not rs.EOF

strSQL = "UPDATE  [tTempCompany] SET tTempCompany.[Account Rep] = [chuserID], WHERE (((tTempCompany.iCompanyId = dbo_ContactInternal.iOwnerID and dbo_ContactInternal.iContactTypeId) In (125,151)) AND dbo_ContactInternal.tiRecordStatus)=1"


rs.MoveNext

Wend
Set rs = Nothing
Set db = Nothing


End Function

 

[KenHigg]

For starters,

While Not rs.EOF

rs.MoveNext

Wend

Isn't really doing anything, is it? I'm guessing you've missed the bigger picture somewhere when it comes to using dao. ???

How many records and flds are you trying to update?

 

[mdjks]

I have about 150K records and for each I need 8 columns filled from various sql tables.

I am very much missing the bigger picture

While Not rs.EOF
strSQL = "UPDATE  [tTempCompany] SET tTempCompany.[Account Rep] = [chuserID], WHERE (((tTempCompany.iCompanyId = dbo_ContactInternal.iOwnerID and dbo_ContactInternal.iContactTypeId) In (125,151)) AND dbo_ContactInternal.tiRecordStatus)=1"

rs.MoveNext

 

[KenHigg]

How many rows are in each of the sql tables?

 

[yeatmanj]

I don't see where you are actually executing the SQL statement. I'm not sure but I think you have to use RunSQL to execute.

 

[mdjks]

How many rows are in each of the sql tables?

The sql tables have 2-5 million rows

I don't see where you are actually executing the SQL statement. I'm not sure but I think you have to use RunSQL to execute.

That is exactly the problem, I don't know how to write it correctly.

 

[yeatmanj]

I would use

DoCmd.RunSQL strSQL

The sql tables have 2-5 million rows

isn't access limited to 2 million records. I don't think it actually matters in this case because you are only updating existing data, but could be wrong yet again.

 

[mdjks]

I tried DoCmd.RunSQL strSQL and get "Syntax error in Update Statement" but this is progress

 

[mdjks]

Access does have that limitation but my access table is only 150,000 or so records. The sql tables are huge so I'm extracting the information I need from them an putting it in the much smaller Access table for quick ad hoc reporting.

 

[KenHigg]

If your original queries were too slow, I'm guessing you're headed back in the same direction

 

[mdjks]

If your original queries were too slow, I'm guessing you're headed back in the same direction

Looks like it, I correct my syntax problem and it runs but the whole point was to speed it up. It seems the docmd.runsql is no different than docmd.openquery, same amount of time. Actualy the way I had it written it kept running over and over to EOF so I stopped it.

Can you or anyone suggest a better approach? I have worked with databases in the past where it would "build" a table by cycling through these sorts of functions but I don't remember exactly how it was done.

 

[KenHigg]

Just curious, what are you trying to pull out of your sql table to put in the Access table? I'm guessing there are a number of issues here...

 

[mdjks]

Just curious, what are you trying to pull out of your sql table to put in the Access table? I'm guessing there are a number of issues here...

My local table contains all good accounts as well as who owns them at 5 different levels and has contact information for each. It is a lot of data and I'd like to refresh it more than once a day. Always want to have the most up to date information.

Having it in the form I do has been extremely useful I can slice and dice it many ways and have info back to sales reps and management in a matter of seconds.

 

[RV]

Just create an update query, save it and run it:

UPDATE  [tTempCompany] 
SET tTempCompany.[Account Rep] = [chuserID
WHERE tTempCompany.iCompanyId = dbo_ContactInternal.iOwnerID 
AND dbo_ContactInternal.iContactTypeId In (125,151)
AND dbo_ContactInternal.tiRecordStatus)=1

RV

 

[mdjks]

Just create an update query, save it and run it:

That's what I'm doing now, I want to find a more efficent method. Each of my update queries takes 2-5 minutes to run and there are 10 or so making the whole thing unwieldy.

 

[yeatmanj]

This is code I use for inserting new records, but it may help you some with syntax.

DoCmd.RunSQL "INSERT INTO atblUtilEquip ( idsUtil, FacID, NPDESPermit, PermitNum, UtilAcct1, UtilAcctNum1, MeterNum1, " _
    & "UtilityCo1, UtilAcct2, UtilAcctNum2, MeterNum2, UtilityCo2, TeleStat, StOwnEquip1, EquipStat1, StOwnEquip2, EquipStat2, StOwnEquip3, EquipStat3 ) " _
    & "SELECT tblUtilEquip.idsUtil, tblUtilEquip.FacID, tblUtilEquip.NPDESPermit, tblUtilEquip.PermitNum, tblUtilEquip.UtilAcct1, " _
    & "tblUtilEquip.UtilAcctNum1, tblUtilEquip.MeterNum1, tblUtilEquip.UtilityCo1, tblUtilEquip.UtilAcct2, " _
    & "tblUtilEquip.UtilAcctNum2, tblUtilEquip.MeterNum2, tblUtilEquip.UtilityCo2, tblUtilEquip.TeleStat, " _
    & "tblUtilEquip.StOwnEquip1, tblUtilEquip.EquipStat1, tblUtilEquip.StOwnEquip2, tblUtilEquip.EquipStat2, tblUtilEquip.StOwnEquip3, tblUtilEquip.EquipStat3 " _
    & "FROM tblUtilEquip WHERE tblUtilEquip.FacID = '" & strFacIDArchive & "';"

I normally use a different method for updating specific records that looks like this.

Dim dbCurrent As Database, qdfSite As QueryDef, rstSite As Recordset
Dim qdfDischarge As QueryDef, rstDischarge As Recordset
Dim qdfContact As QueryDef, rstContact As Recordset

Set dbCurrent = CurrentDb

Set qdfSite = dbCurrent.CreateQueryDef("")

qdfSite.SQL = "SELECT tblSite.FacID, tblSite.staffID, tblSite.ContractorID, tblSite.ContractorName, tblSite.SiteName, " _
                & "tblSite.Score, tblSite.Phase, tblSite.DateAssigned, tblSite.Team6 " _
                & "From tblSite;"

Set rstSite = qdfSite.OpenRecordset()

If lngDischargeID > 0 Then

    rstSite.FindFirst "FacID = '" & strFacID & "'"

    With rstSite
        .Edit
        !StaffID = Me.cboStaffID.Value
        !ContractorID = Me.cboContractorID.Value
        !ContractorName = Me.tbxContName.Value
        !SiteName = Me.tbxSiteName.Value
        !Score = Me.tbxScore.Value
        !Phase = Me.cboPhase.Value
        !DateAssigned = Me.tbxAssignDate.Value
        !Team6 = Me.chkTeam6.Value
        .Update
    End With

Instead of Me.blah you can create two Select query recordsets, one for each table involved, and use rstWhatever.FieldName (or rstWhatever!fieldname, I can never remember for sure) as the equaling value.

Remember this is all in DAO instead of ADO. I think that ADO may work better, but the syntax and commands in ADO are still pretty new to me. Also, I didn't put the close cmds in there like dbCurrent.close and qdfSite.close. Make sure you close your recordsets after use.

 

[yeatmanj]

Also, you can use the syntax from your query by switching the query to SQL view and copying the SQL statement straight out of it. May require quotes and brackets be placed in it, but overall that works pretty well for ensuring you have the right syntax. Create a query using the query design view built into access then switch to SQL view to copy the code.

 

[RV]

That's what I'm doing now, I want to find a more efficent method.

There isn't, using an update query is the most efficient method.....

RV

 

[KenHigg]

I suspect your main problem may be that the sql table does not have an index set up on the fk. Maybe you should look at using a pass through query and bring the data over local, the perform the updates, Just a thought...

 

[mdjks]

I suspect your main problem may be that the sql table does not have an index set up on the fk. Maybe you should look at using a pass through query and bring the data over local, the perform the updates, Just a thought...

I gave this a try this morning and it did speed up a little but I'm going to give yeatmanj's code a good look and see what happens with that.