asked on

Getting "Invalid Operation" Error when opening a Recordset

I am opening a recordset and trying to update a linked table. The table is in an SQL Database and is linked by ODBC. Below is the code

-----------------
Public Sub AddDatesToTable()
Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim strSQL As String
Dim vYN

strSQL = "SELECT DatePop " _
& "FROM TUDatesPop"

Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL)

vDate = Date

Do
rst.MoveFirst
vYN = "No"

Do
If rst("DatePop") = vDate Then
vYN = "Yes"
End If

rst.MoveNext

Loop Until rst.EOF = True

If vYN = "No" Then
rst.AddNew
rst("DatePop") = vDate
rst.Update
End If

vDate = vDate - 1

Loop Until Format(Mid(txtFromDate, 5, 2) & "/" & Right(txtFromDate, 2) & "/" & Left(txtFromDate, 4), "Short Date") = Format(vDate, "Short Date")

rst.Close
Set rst = Nothing

End Sub
-----------------

It stops at "rst.AddNew" and I get the following message:

"Run-time error '3219' Invalid Operation"

It works if I use a local table, but when I changed to the linked table I started getting this error.

Thanks,
Scott

Patrick Matthews

Dumb question: for the linked table, are you 100% certain that the account you are using in the ODBC connection has full permissions for that table?

Scott Palmer

ASKER

Good question, always start with the obvious. Yes, I have full permission for the table.

Scott Palmer

ASKER

I renamed to match my VBA code

crystal (strive4peace) - Microsoft MVP, Access

aside from wondering if the table is actually referred to as "dbo_TUDatesPop" since it is linked to SQL Server, there are other problems ...

shouldn't this:
Format(Mid(txtFromDate, 5, 2) & "/" & Right(txtFromDate, 2) & "/" & Left(txtFromDate, 4), "Short Date") = Format(vDate, "Short Date")
refer to a field in the recordset you are looping through, and not a control on the form?

in the assignment for strSQL, filter SQL string with conditions so you can loop through every record returned and not have to test to see if something is filled

there are also 2 loops ... it seems that the test for yes and no (in code: True and False if stored as boolean or yes/no), and what follows, should be in the same place ... just one loop with a condition to test (If)

after Set rst = db.OpenRecordset(strSQL):

with rst
   if not .eof
      if SomeCondition = true Then    'replace SomeCondition  with what you want to test -- may not be needed if data is already filtered 
          'statement(s) with what to do if true
         else
          'statement(s) with what to do if false
      end if
      .MoveNext 'go to the next record
    Loop
   .close 'close rst
end with
set rst = nothing

Open in new window

When others give you help ... spend one minute, or more, to understand each statement -- at least the logic to be sure that whoever helped understood what you needed ... and ask questions if anything is confusing ~

what is your purpose for doing this? if we understand, we can help you better, thanks ~

Scott Palmer

ASKER

First, I am using the fields on the form to filter the query for the wanted data to put into the recordset so I am confused by your question.

Second, I don't know what you mean by "the same place"? Technically I am trying to see if the values in two different data sets match and so I am using two loops to go through each value in each dataset.

crystal (strive4peace) - Microsoft MVP, Access

it seems that DatePop is a yes/no field that is changed if the date is the current date (this can be tested other ways without storing an idicator) ... and if not, then another record is added -- just looking for the logic of this. If indeed the logic is good, then it would be faster to run an Update query and then an Append query if necessary.

with 2 loops, perhaps you should use 2 recordsets even though they are the same source ... but the logic of doing this is escaping me ...

> "I am trying to see if the values in two different data sets match"

actually, it seems the same is being used

Scott Palmer

ASKER

Let me see if this helps.

The program runs reports and then records the date they were processed and sent out. This piece of code deals with the table that records the name of the report and the date the report was went out. To do this I first need to update the table with the current dates. If I last ran the program on 4/4 it needs to add 4/5, 4/6 and 4/7 (today's date) to the table.

On the form there is a "From" date and "To" date. vDate = today's date

The first loop starts with the vDate. Each time it loops the vDate goes back 1 day. The loop ends when vDate = the "To" date on the form.

The second loop compares vDate with all of DatePop values in the table.

So it takes today's date and then looks to see if it matches anything in the table. If it does not it adds the date to the table. After it loops through all of dates it then checks vDate - 1 to see if it matches a date in the table and so on. Admittedly I could make it more efficient, but it does work and that would not fix my problem.

So have wrote that my question is if we made all of the changes you suggested, would that fix my original problem?

ASKER CERTIFIED SOLUTION

Gustav Brock

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Scott Palmer

ASKER

Thanks, Gustav, that is what I needed. Works now.

Scott

Gustav Brock

You are welcome!

/gustav

Getting &quot;Invalid Operation&quot; Error when opening a Recordset

Getting "Invalid Operation" Error when opening a Recordset