Solved

error 3027 No Linked Tables Querydef Updating  ?

Posted on 2007-03-22
4
287 Views
Last Modified: 2012-06-21
access 2003
I'am trying to update fields in a query through from another table.

I get runtime 3027   ?? database or table read only...
No Linked tables ?


Dim cD As String, sC As String, score, x
Dim stN As String
Dim cArr As Variant
Dim NounPos(0 To 200) As Integer
Dim tempC As String
Dim nf1 As String
Dim nf2 As String
Dim nf3 As String
Dim nf4 As String
Dim nf5 As String
'Dim strCC As Label

Dim rsCust As DAO.Recordset
Dim rsNoun As DAO.Recordset
'Dim rsNoun2 As DAO.Recordset
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim rsCore As DAO.Recordset
Dim sD As String
Dim cArrB, sB As String
Dim wdFound, scrFound As String
Dim cArrg, sG As String
Dim dbs As Database
Dim rsSql As DAO.Recordset
Dim strSql As String
Dim qd As DAO.QueryDef
Dim lb, ub As Integer
'Set qd = CurrentDb.QueryDefs("qryBruteForceResult")
Set dbs = CurrentDb
'=================================
' DECLARE RECORDSET
Set rsCust = CurrentDb.OpenRecordset("tblData")
Set rsNoun = CurrentDb.OpenRecordset("tblNounMod1", dbOpenTable)
Set rsCore = CurrentDb.OpenRecordset("tblCoreskuInformationST")

' TAKE TO FIRST RECORD
rsCust.MoveFirst
rsNoun.MoveFirst
' Start The Looping through customer input recs.
rsNoun.Index = "NOUNPHRS1"

' Trim Up The Description before running Matching
DoCmd.OpenQuery "UpdTrimDescriptionBeforeDescMatch"
Do Until rsCust.EOF
    i = 0
    If rsCust("fldDescription").Value <> "" Then
        ' split on array
        cD = rsCust("fldDescription")
        cArr = Split(cD, " ")
   
        nfound = False
        For x = LBound(cArr) To UBound(cArr)
            ' FORCING ARRAY ELEMENT TO ZERO
            ' ASSUME NOT GOING TO FIND IT
            NounPos(x) = 0
            rsNoun.Seek "=", cArr(x)
            ' Finds the Nouns IN MOUNPHRS1 FIELD
            If rsNoun.NoMatch = False Then
                ' YES WE FOUND IT
                NounPos(x) = 1
                i = i + 1
            End If
        Next
        ' IF ZERO OR MORE THAN 2 WE DON'T WANT TO HANDLE IT
        If i = 0 Then
            rsCust.Edit
            rsCust!fldDelete = "Y"
            rsCust.Update
        End If
        ' GREATER THEN 1   NOUN
        If i >= 1 Then
            For x = LBound(cArr) To UBound(cArr)
                    ' pass cArr(x) to the SQL which will
                    ' extract all the SKUS with
                    ' that noun.
                   If x = UBound(cArr) Then
                        nf1 = Trim(nf1 & Chr(39) & cArr(x) & Chr(39))
                   Else
                        nf1 = Trim(nf1 & Chr(39) & cArr(x) & Chr(39) & ",")
                   End If
            Next
            '''==============================================
Set qd = CurrentDb.QueryDefs("qryBruteForceResult")
' sql
strSql = "SELECT TOP 3 tblNounMod1.ITEM, tblNounMod1.NOUNPHRS1, tblNounMod1.NOUNPHRS2, tblNounMod1.NOUNPHRS3, tblCoreSkuInformationST.RICHTEXT, tblCoreSkuInformationST.fldFndNouns ,tblCoreSkuInformationST.fldScore,RANKLST2.COUNT,tblCoreSkuInformationST.fldDId, "
strSql = strSql & "tblCoreSkuInformationST.WWGMFRNUM, tblCoreSkuInformationST.WWGMFRNAME, tblCoreSkuInformationST.WWGDESC, tblCoreSkuInformationST.COMMENTS, "
strSql = strSql & "tblCoreSkuInformationST.REDBOOKNUM, tblCoreSkuInformationST.XREF, tblCoreSkuInformationST.SPIN, "
strSql = strSql & "tblCoreSkuInformationST.UOM, tblCoreSkuInformationST.[UOM Qty], tblCoreSkuInformationST.[Customer Willcall Qty], tblCoreSkuInformationST.[Customer Ship Qty], tblCoreSkuInformationST.ALT1, "
strSql = strSql & "tblCoreSkuInformationST.fldMfgname, tblCoreSkuInformationST.fldMfgnameOrig, tblCoreSkuInformationST.fldMfrnumST, tblCoreSkuInformationST.fldMfrnumOrigST, tblCoreSkuInformationST.fldDescription, tblCoreSkuInformationST.fldDescriptionOrig "
strSql = strSql & "FROM (tblNounMod1 INNER JOIN tblCoreSkuInformationST ON tblNounMod1.ITEM = tblCoreSkuInformationST.ITEM) INNER JOIN RANKLST2 ON tblCoreSkuInformationST.ITEM = RANKLST2.ITEMNUM "
strSql = strSql & "WHERE (((tblNounMod1.NOUNPHRS1) In (" & nf1 & "))) ORDER BY tblCoreSkuInformationST.fldScore DESC , RANKLST2.COUNT DESC;"

qd.sql = strSql
Set rsSql = dbs.Recordsets(strSql, dbOpenDynaset)
rsSql.MoveFirst

    ' prepare customer desc
    sC = UCase(Trim(rsCust("fldDescription")))
    cArr = Split(sC, " ")
    tempC = " " & sC & " "
   
    ' prepare grainger desc
    sG = UCase(Trim(rsSql("RICHTEXT")))
    cArrg = Split(sG, " ")
     
    ' prepare grainger brand
    sB = UCase(Trim(rsSql("WWGMFRNAME")))
    cArrB = Split(sB, " ")
   
   
    rsSql.MoveFirst 'Extracted Noun Loop
    Do Until rsSql.EOF  ' query loop
        score = 0
        wdFound = ""
        scrFound = 0
        ' Search into Grainger Richtext for Each customer Term
        sD = " " & Trim(rsSql("RICHTEXT")) & " "
       
        lb = LBound(cArr)
        ub = UBound(cArr)
        For x = lb To ub
            If InStr(sD, " " & cArr(x)) > 0 Then
                score = score + 5
                scrFound = score
                wdFound = wdFound & "," & cArr(x)
            End If
        Next
        ' Search into Customer Terms for Each Grainger Richtext term
        lb = LBound(cArrg)
        ub = UBound(cArrg)
        For x = lb To ub
            If InStr(sC, " " & cArrg(x)) > 0 Then
                score = score + 5
                scrFound = score
                wdFound = wdFound & "," & cArrg(x)
            End If
        Next
       
        ' search into grainger brand for each customer term
        sB = " " & Trim(rsSql("WWGMFRNAME")) & " "
        lb = LBound(cArr)
        ub = UBound(cArr)
        For x = lb To ub
            If InStr(sB, " " & cArr(x)) > 0 Then
                score = score + 5
                scrFound = score
                wdFound = wdFound & "," & cArr(x)
            End If
        Next
       
        ' search into customer terms for each grainger brand
        ' sC = " " & sC & " "
        lb = LBound(cArrB)
        ub = UBound(cArrB)
        For x = lb To ub
            If InStr(sC, " " & cArrB(x)) > 0 Then
                score = score + 5
                scrFound = score
                wdFound = wdFound & "," & cArrB(x)
            End If
        Next
       
        With rsSql    
            .Edit    '   <-------   ERROR 3027   CANNOT UPDATE DATABASE OR TABLE READONLY ?
            !fldScore = score '& "(found: " & wdFound & " )"
            !fldFndNouns = "(found: " & wdFound & " )"
            !fldDId = rsCust.Fields("fldDid").Value
            !fldMfgname = rsCust.Fields("fldMfgname").Value
            !fldMfgname = rsCust.Fields("fldMfgnameOrig").Value
            !fldMfrnumST = rsCust.Fields("fldMfrnum").Value
            !fldMfrnumOrigST = rsCust.Fields("fldMfrnumOrig").Value
            !fldDescription = rsCust.Fields("fldDescription").Value
            !fldDescriptionOrig = rsCust.Fields("fldDescriptionOrig").Value
            .Update
        End With
        'score = 0: wdFound = ""
        rsSql.MoveNext
    Loop
       'append records to tblDataAppend
   
    DoCmd.OpenQuery "ApdBruteQueryToDescMatchTable"
             '''=======================================
            'getScoreDescMatch nf1
        End If
    End If
  rsCust.MoveNext
Loop
0
Comment
Question by:fordraiders
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 18777351
try opening the recordset

change
Set rsSql = dbs.Recordsets(strSql, dbOpenDynaset)

to

Set rsSql = dbs.OpenRecordset(strSql, dbOpenDynaset)
0
 
LVL 3

Author Comment

by:fordraiders
ID: 18777885
cap,
did not have primary key on one of the query tables... really weird..
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 18778890
if you run qryBruteForceResult, does it give any error? results?
0
 
LVL 3

Author Comment

by:fordraiders
ID: 18781709
results yes, But again it was because one of the inner jointables did not have a primary key >

0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question