Query won't run in VBA 2010 but does in 2003

[kwvh]

Is there any obvious reason that the following would not run in Access 2010? The message says "Operation must use updateable query"

Code: Select all

DoCmd.RunSQL ("INSERT INTO tblSiteYearHours ( SM_FKSite, SM_year ) " & _
    "SELECT sqOpenSites.S_pk_Site, [Forms]![frmSiteYearEndHours]![t" & _
    "xtYear] AS Expr1 " & _
    "FROM sqOpenSites;")

It runs in 2k and 2003 version. I have decompiled it, but get the same results. Any ideas are greatly appreciated.

Ken

[HansV]

What is the SQL for sqOpenSites?

[kwvh]

Hans,

Thanks for following up.
Here is the sqOpenSites query.

Code: Select all

SELECT tblSite.S_pk_Site
FROM tblSite
WHERE (((tblSite.S_logSiteClosed)=False) AND ((tblSite.S_logInactiveSite)=False));

Best Regards,
Ken

[kwvh]

I also tried hard coding in '2011' for the [Forms]![frmSiteYearEndHours]![txtYear], but get same results.

[Mark L]

I'd create a new query from db Window, and use it to try to run the SQL code you have. You are likely to get a better error message.

[kwvh]

Mark,

Thanks for the suggestion. Actually, I did that as well, with same results and same cryptic message. Yet it runs fine in A2k and A2003. I am beyond confused.

[HansV]

Could you attach a stripped down and zipped copy of the database?

[kwvh]

Substituting the following resulted with "Cannot update. Database or object is read-only"

Code: Select all

Dim Yr As Integer
Dim db As DAO.Database
Dim rst, rstSiteHours As DAO.Recordset
Dim strSQL As String
Yr = [Forms]![frmSiteYearEndHours]![txtYear]




Set db = CurrentDb
strSQL = "SELECT S_pk_Site " & _
        "FROM tblSite " & _
        "WHERE ((Not (tblSite.S_logSiteClosed)=True) AND (Not (tblSite.S_logInactiveSite)=True));"
    
Set rstSiteHours = db.OpenRecordset("tblSiteYearHours", dbOpenDynaset)


Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot)
Do While rst.EOF = False
    rstSiteHours.AddNew
        rstSiteHours!SM_FKSite = rst!S_pk_Site
        rstSiteHours!SM_year = Yr
    rstSiteHours.Update
    rst.MoveNext
Loop

[HansV]

I'd still need to see a copy of the database.

Just one remark: the line

Dim rst, rstSiteHours As DAO.Recordset

declares rst as a Variant because you don't specify its data type. Only rstSiteHours is declared as a DAO.Recordset. To declare both as recordsets, use

Dim rst As DAO.Recordset, rstSiteHours As DAO.Recordset

VBA is different in this respect from most programming languages.

But that doesn't cause the error message that you get.

[Mark L]

Mark,

Thanks for the suggestion. Actually, I did that as well, with same results and same cryptic message. Yet it runs fine in A2k and A2003. I am beyond confused.

Is there any possibility that the table is not updatable? Does it have a Primary Key?

[kwvh]

Hans,

Thanks for the heads up on the "Dim rst As DAO.Recordset,". I will need to correct that in other places as I have used that format before. duh!

Mark,
Yes, the table does have a primary key.

Both,
The table resides on an external USB drive. I will move to local drive and see what happens. The strange thing for me is that it works in 2003 version, but not 2010. ugh.

Thanks for your suggestions. If moving to new drive doesn't work, I will strip down the app and data and upload.

Ken