Results 1 to 10 of 10
  1. #1
    boss1b is offline Novice
    Windows Vista Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    5

    Access2010 accdb conversion to SQL

    Hi everyone! This is my first attempt at converting an Access database to use SQL tables instead of Access tables. The database currently runs in both 2007 and 2010. I have successfully moved the data into SQLExpress2012. The database can access the data with no problem, all forms fill out, etc. However, when the program hits the following code, I get the "Run-time error '-2147467259 (80004005)' Query is too complex" error msg.

    sTotalDescr = sTotalDescr & " = " & Format(cTotalCost, "$#,###") & vbCrLf
    sUnitsLegal = sUnitsLegal & "Total Cost:" & vbCrLf & sTotalDescr
    rs("UnitsInfo") = sUnitsInfo
    rs("FullLegalDescr") = sUnitsLegal
    rs("TotalLiableAmt") = cTotalLiableCost
    rs("TotNoOfWells") = lTotNoOfWells
    rs("TotalAFECost") = rs("TotNoOfWells") * cAFEcost
    rs("TotalCost") = rs("TotalAFECost")
    cTotalLiableCost = 0
    rs("LetterId") = lLetterId
    rs("DateCreated") = Now()
    rs.Update

    I have tried eliminating fields until I got down to just the DateCreated field, getting the same error.
    The error occurs when the rs.Update line is executed. Is this command invalid when dealing w/Sql?
    I can convert them all to queries, but there is a LOT of them! Just need a push in the right direction!
    Any ideas?



    Thanks in advance!
    epb

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You can certainly add/update records in SQL Server tables via a recordset; I do it all the time. Odd error though. Are the tables linked? How is the recordset opened? Do the tables have key fields in SQL Server? Access will let you get away without, SQL Server won't.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    And where is the "rs.AddNew" or "rs.Edit"???

  4. #4
    boss1b is offline Novice
    Windows Vista Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    5
    Quote Originally Posted by pbaldy View Post
    You can certainly add/update records in SQL Server tables via a recordset; I do it all the time. Odd error though. Are the tables linked? How is the recordset opened? Do the tables have key fields in SQL Server? Access will let you get away without, SQL Server won't.

    Thank you for the quick answer! I checked on your questions. Yes, the tables are linked. The table did NOT have a key. I assigned the primary key as LeaseId and reran, with the same resulting error. the following is how the record set is opened.

    SqlStr = "Select * From tempExportsForMerge Where LeaseId = " & lLeaseId & " And UserId = '" & TempVars("UserId") & "'"
    rs.Source = SqlStr
    rs.Open

    The above is followed by a lot of data manipulation and acquiring data via other record sets. The next function performed is the rs.Update. Based on something I read, I wonder..... This table has 115 fields in each record. Seem to remember something about a 50 cell limit on update? If I understand the code correctly (I'm rather new to this!), since the rs is created from a SELECT *, will it not try to update all 115 data fields?
    Thanks!

  5. #5
    boss1b is offline Novice
    Windows Vista Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    5
    Quote Originally Posted by ssanfu View Post
    And where is the "rs.AddNew" or "rs.Edit"???
    ssanfu,
    The only function before the rs.Update is the following:

    SqlStr = "Select * From tempExportsForMerge Where LeaseId = " & lLeaseId & " And UserId = '" & TempVars("UserId") & "'"
    rs.Source = SqlStr
    rs.Open

    Thanks!
    epb

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I've never heard about a 50 field limit, but I would think it would have failed in Access if that's the problem. Did you relink the tables to make sure Access realizes they have a key now? Might not be necessary, but might as well cover the base. Poked around and found this, so see if it helps:

    http://support.microsoft.com/kb/192716

    Which does support what you mentioned about 50 fields.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    It looks like you are using ADO which appears that ".AddNew" / ".Edit" are not required. (I use only DAO)


    My first thought would be to check if the recordset is open.
    Add the two lines in BLUE
    Set a break point at the debug line and check the SQL string to see if it is a valid SQL string.

    Code:
    SqlStr = "Select * From tempExportsForMerge Where LeaseId = " &  lLeaseId & " And UserId = '" & TempVars("UserId") & "'"
    Debug.Print SqlStr
    
        rs.Source = SqlStr
        rs.Open
    
    MsgBox rs("DateCreated")
    I also wonder if the table is normalized. 115 fields seems like a lot of fields....

    BTW, looking at the reference that Paul provided, Jet 4 (DAO) allows for up to 99 fields.

    My $0.02 worth...

  8. #8
    boss1b is offline Novice
    Windows Vista Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    5
    Will check that out.
    Yes, records are very large. this app is to be rewritten using sql/.Net. Current attempt is band-aid. Thanks for the help!

  9. #9
    boss1b is offline Novice
    Windows Vista Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    5

    thanks!

    Quote Originally Posted by boss1b View Post
    Will check that out.
    Yes, records are very large. this app is to be rewritten using sql/.Net. Current attempt is band-aid. Thanks for the help!
    Thanks, everyone! pbaldy, you were right on with the key issue. I ended up blowing away the sql db, reloading it from the access tables using the 32-bit migration tool, and started comparing. It was bringing the data, but NOT the keys. After I went thru and manually set the keys in the tables to match what they were in Access, that seemed to have taken care of the problem.

    thanks again for your help and insight! I appreciate it!

    Earl

  10. #10
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Glad you got it sorted out!
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 08-17-2012, 02:01 PM
  2. Adding a logo to an email that is being sent through Access2010
    By crowegreg in forum Import/Export Data
    Replies: 3
    Last Post: 04-22-2012, 09:28 PM
  3. Replies: 3
    Last Post: 12-02-2010, 12:23 PM
  4. Access2010 problem with .close
    By jong in forum Access
    Replies: 1
    Last Post: 11-18-2010, 12:53 PM
  5. Access97 conversion to Access2010
    By jong in forum Access
    Replies: 3
    Last Post: 11-17-2010, 03:33 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums