Results 1 to 9 of 9
  1. #1
    Join Date
    Apr 2002
    Location
    Portugal
    Posts
    146

    Unanswered: Dlookup function doesn't work with upsized db

    Hi,

    Recently i upsize a backend db and a frontend db, then i created a new project, imported the views and stored procedures, forms and modules of the frontend upsized db, and linked the tables to the backend upsized db.

    then i wanted to try db, so i choose a form and started to enter data, the problem ocurs when updating a field that has on the event procedure somewhere in the code a Dlookup function. First the error was about the "!", that indicates access wicth form as the control: Forms![aaaa].field , i searched in the knowledge base and that error is about the regional defenitions, ok, i changed the defenitions from Portuguese to English. With that i thought the problem was solved, but when i tried again, a new error appears; "Unexpected error -- 2147217855 , the comand has one or more errors".
    I have searched the knowledge base but there is nothing about this.

    Can you explain me this, is it a bug with access 2002?

    Thanks!

  2. #2
    Join Date
    Nov 2003
    Posts
    1,487
    Make sure your Form & field names are indeed correct within the WHERE string for your DLookup function. Also, you may want to check References.

  3. #3
    Join Date
    Apr 2002
    Location
    Portugal
    Posts
    146
    Thanks for your reply.

    I've checked the field names and the references and they are correct.
    I only work with ADO not DAO.
    There's no explanation for the form to work in access db and not in the project.

  4. #4
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    441
    I don't know much about ADP's, but I just made a DLookup work in one, so I assume the Domain Aggregates should be available, if you want to use them.

    One of the differences of using SQL vs Access, is the generation of "autonumbers", Access generates them when you start entering info, SQL server, I believe generates them on save - could it be you are referencing an autonumber field which isn't yet populated?
    Roy-Vidar

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I wouldn't recommend using DLOOKUP in an ADP project, though it should be feasible. The point of upsizing to an ADP application is to push processing onto the SQL Server for efficiency and ease of maintenance. I would encourage you to create your lookup list as a view or a procedure on SQL Server and then reference it in your application control.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Jet SQL (the native / default SQL engine for Access) has differences to ANSI SQL and the SQL in SQL Server. You can reduce the impact of these using the ANSI Syntax check box in
    tools | options | tables/queries tab

    In an ADP domain aggregate functions are to be avoided, as blindman suggests, but then I think they are to be avoided in ANY Access app. You are potentailly sucking a great deal of data over the network where a 'properly' designed query should return the value you want with trashing your network.

    You do need to do a lot more than just switch to SQL server to get the benefits of a true client / server model.
    HTH

  7. #7
    Join Date
    Apr 2002
    Location
    Portugal
    Posts
    146
    Thank you all!

    But if i do not use the domain aggregate function, how do i get the pretended value from the view or procedure? By using SQL statments in VB!?

    In this case the Dlookup, returns a value for generating a code, needed for the productions orders of the company!

    How do i get this without Dlookup!?

    Thanks!

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Well, first try to port your CODE over to SQL Server procs, views, and functions. Use the ADP file only as an interface, and not for business logic. The only code I put into an ADP file is that which is necessary for handling user responses.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Apr 2002
    Location
    Portugal
    Posts
    146
    Hi again,

    First i'd like to thank you all for helping me with the Dlookup function. I solved the problem.

    Now i have a diferent problem:

    I have this database running in a company, the database has 3 distinct front-ends and 1 global back-end with all the tables. 1 front-end is for comercial treatment (orders,custumers,stocks,etc.); another front-end is for poduction/manufacturing issues (Planning,fabrication orders,time calculation,etc.) and finally the lst front-end is for operational issues (all the data from the production, how much was produced, what time was the machine stoped,etc.)
    This is working fine with 5 users, no problems were detected in this years, even when the server crashed a few weeks ago.
    But i know that later or sonner i will have problems,and my poblem is that i don't now what to do, upsizing the back-end and link the tables via ODBC, or upsizing all.
    Upsizing only the back-end is a easier way to do because i do not have to make lots of changes in the front-ends, but upsing all is more reliable.
    Can i get reliability with ODBC linked tables?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •