Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Brugge, Belgium
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sending orderinformation to A Access Database (MS Access 2007)

    In my MS Access 2007 database on my desktop i have a routine that makes it possible to read data from a Excel worksheet into a Access Datatable.
    Until now , when i visit customers, i book all the orders and at the end of the day when i entered, i open my database on my desktop and enter all orders i booked along the day.
    To eliminate entering orderinformation twice,and to give my personal the posibillity to work with this new orders as soon as possible, I wonder if with the help of the routine I mentioned above i can do the following.
    When i visit a customer with my laptop, i book the orders that i receive on a Excel worksheet . To minimalize the necessary information for each orderitem I only enter the Klnt_Id, the Artnr and the QTY, thus only three numbers.
    Once all orders are booked for one or maybe several customers, i setup a E-mail with this Excel worksheet as attachment, and send this to my desktop which is connected with the internet.
    On my desktop we open the E-mail and put the Excel attachment in a dedicated folder on the harddisk.
    With the routine I mentioned in the beginning of this article, the information of the worksheet is loaded in a Access datatable named "tblImpData".
    From there, purely programatically and without any other intervenience of using the forms "frmOrders" and "sfmOrfderdetails" the following actions must be done:
    1) Open a recordset based on the table "tblOrders" as a dbDynaset and Add a new record
    2) Get the "klnt_Id "out of tblImpData and put it into the recordset.
    3) Start a special routine that generate the next "OrderID" ( - multiuser application-) and put it into the recordset and also saves this number into a variable.
    4) With the help of DBLookUp functions and the klnt_ID, i get all the rest of the client information, necessary for the order ,out of the table "tblClients"
    5) Open a recordset based on the table "tblOrderDetails" as dbDynaset
    6) Add a new recordset
    7) Put the OrderID that was saved into a variable into this recordset.
    8)Get the Artnr and Qty for the first orderitem out of tblImpdata and put it into the recordset.
    9) With the help of DBlookUp function get all the other necessary information for this orderItem ( e.g. description of the article, Unitprice, VAt, Stock etc out of the table "tblArticles"
    10) Update the recordset based on tblOrderdetails
    11) Repeat pt 6 to pt 10 for each orderitem of the order.
    12 Update the recordset based on tblOrders.
    13) After all the work is done, we can open the "frmOrders" and "sfmOrderdetails" and control the newly entered orders.

    Question: 1) Is such a way of working possible
    2) Is the use of a Excel woorksheet a good choice or is the use of Outlook a better choice?

    I know that the use of vb.net would be more adequate for this job, but here as my IP-Address from my provider continually change there is a problem of making the connection,

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sending orderinformation to A Access Database (MS Access 2007)

    It's possible to write code using DAO or ADO to create the records that you want, but I don't understand why you would want to copy information from tblClients into tblOrders - the value of klnt_id should be enough, this enables you to look up client information for an order in a query based on tblOrders and tblClients.
    Similarly, it shouldn't be necessary to copy product information from tblArticles into tblOrderDetails - the value of ArtNr enables you to look up product information for an order item in a query based on tblOrderDetails and tblArticles.

  3. #3
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Brugge, Belgium
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sending orderinformation to A Access Database (MS Access 2007)

    Hans, thank you very much for the quick reply. I will review my program in order to take in account the advice you have given.

Posting Permissions

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