Results 1 to 5 of 5
  1. #1
    AquaChaos is offline Novice
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Jun 2010
    Posts
    3

    Link to multiple sources

    I am fairly new to Access, but am using it to make viewing/editing the MySQL database that runs my online shopping cart easier. I currently have Access linked to MySQL database through the ODBC link, so I can see and make changes locally without needing to do so inside MySQL. A couple of items that I can't seem to solve:

    1. The simplest solution would be to add entries directly into Access, but I need to be able to enter them wherever I want to, and I can only get it to add a new entry at the end i.e. I have 10 entries with Primary Key header ID with values 1-10. I can add a new entry with number 11, but I need the ability to add a new row at say id 5, with all entries afterward changing id to accommodate. Is this possible?

    2. Within each entry is a field that references the id of certain entries, i.e. entry 8 has a field linked to the id on entry 5. If I add an entry before number 5, the id number will change and I need entry 8 field to change with it. Again, is it possible?

    3. I can accomplish both of the above requirements in Excel, so I was trying to import it, but having problems there too! My ideal solution (barring that BOTH of the above can be done is Access) would be to have a table within Excel with the same name as the linked one in Access, and somehow have Access sync all 3 files (MySQL, Access, Excell), i.e. if I add an entry in Excel then Access updates itself and MySQL (automatically or manually) and vis-a-versa.



    If none of the above solutions are possible, how can I cut the info from Excel and paste it into a linked database? Currently when I try I either get an error along the lines of "pasted area to small" (only dealing with 10 records as I am trying to work the dynamics out, will be hundreds down the road) or "NULL value can't be inserted (even though no fields are empty, they all have either some text, numbers, or a 0 or 1, and some are actually the exact same as some records that are in the database, though I deleted the duplicates to see if that was the issue).



    Thanks for any help!

  2. #2
    evander is offline Competent Performer
    Windows 7 Access 2003
    Join Date
    Apr 2010
    Location
    Philippines
    Posts
    206
    (Answer to 1) Yes it is possible, with a bit of programming. However, is this primary key used as a foreign key in some other table? If so, did you check the option "Cascade on update?"

    (Answer to 2) Yes, this is called a self-join. You basically create a second copy of your table in your relationships window, and perform a self-join there. I do, however, need to know why you want the related numbers to update themselves.


    Pasted area too small = It must be referring to the size of the data type, and not to the number of rows your table can accommodate.

    Paste data in the table where the primary key is first, then paste in the table where that PK is used as an FK.

  3. #3
    AquaChaos is offline Novice
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Jun 2010
    Posts
    3
    Thanks for the reply!

    It sounds like I can get Access to do it all for me, but let me detail exactly what I am doing so someone can hopefully point me in the direction of some literature or details on how to accomplish it.

    I sell water filter systems and I have a database driven shopping cart that uses MySQL. There are 60-some tables that control settings, user info, carts, products, etc, but at this point there main ones I am focusing on - categories, and products and related tables.

    The software does have a web interface that allows modifying and adding data, but new data is added at the end. I want to be able to add it wherever I want so that I can keep similar items close (it is also easier to enter the info in Access or Excel than the web interface). Starting up is not an issue, I would simply enter all of one type of item (say water softeners) before adding the next type. Once everything is set up though , if I have 300 items and my softeners are ids 100-150, when I add a new product the new softener will be id 301, where I would want it to be id 151. Same thing applies to the categories.

    To detail the tables:

    Category table consists of the category id number, description, etc.

    product table consists of the product id number and the main category id number it is associated with. To the best of my knowledge there is no direct link, if you delete a category or rename it, the product is still linked to the id number regardless of what it actually represents. There is also detailed info, independent of any other tables.

    There are then various sub product tables:

    products_categories - has an independent id, the id of the product, and any additional categories (with one entry for each additional category). For example, for a certain softener, it will list the id of the softener category as the main category in the product table, but may also list the id of the digital category and the commercial category (as separate entries) under the product_categories table.

    There are also tables for additional pics, reviews, special pricing, options, etc. that are all linked to the product id.

    So what I am looking to do is this:

    Link to the MySQL database on my hosting server. It pulls the info from MySQL as it may change independently, I will do most of the product updating, but there may be others who occasionally use the web interface to add an item, so I will need to be able to see and update it to the correct id range.

    I then need to be able to inert a row and assign it any PK I need, with any following rows adjusting their PK as necessary.

    I will also need some linking, if I add a category, the following rows adjust their id, and the related tables (products and products_category) will automatically change the referenced category id to match the new arrangement. A similar link will need to be made between the id on the products table and the sub tables, but the id on the sub tables are independent, so shouldn't need linked to anything.

    Any suggestions, documentation, etc. that will help get me going is greatly appreciated!

    Regarding pasting from Excel, just tried it again, and this is what I did:

    1. Select all fields in excel excluding the headers and CTRL+C. I did so both by dragging from the first field to the last field and by clicking on the first row and dragging to the last.

    2. Select all fields in Access, both by dragging across the rows and by dragging across the fields (id number was not selectable in the field drag method). and CTRL+V.

    I get the following errors:

    "You tried to assign the Null value to a variable that is not a Variant data type" > OK

    "Do you want to supress further error messages telling you why records can't be pasted? If you click NO, a message will appear for every record that can't be pasted." > No

    "The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data." > OK

    By selecting the fields it does actually paste some records, but I get some duplicates.

    When I try to select the rows for each record I get either
    "ODBC--call failed. [MySQL][ODBC 5.1 Driver][myswld-5.1.47-community-log]Duplicate entry '3' for key 'PRIMARY' (#1062)" (duplicate entry number changes) or the "field is too small" error.

    Thanks again!

  4. #4
    evander is offline Competent Performer
    Windows 7 Access 2003
    Join Date
    Apr 2010
    Location
    Philippines
    Posts
    206
    AquaChaos, I just finished reading your narrative. I want to confirm my own understanding of your system.


    1. The simplest solution would be to add entries directly into Access, but I need to be able to enter them wherever I want to, and I can only get it to add a new entry at the end i.e. I have 10 entries with Primary Key header ID with values 1-10. I can add a new entry with number 11, but I need the ability to add a new row at say id 5, with all entries afterward changing id to accommodate. Is this possible? ...

    I want to be able to add it wherever I want so that I can keep similar items close...
    Once everything is set up though , if I have 300 items and my softeners are ids 100-150, when I add a new product the new softener will be id 301, where I would want it to be id 151.
    Reading this point of yours, I conclude that customers placing their orders thru your site do not list inventory items according to ProductID because this ID may change at any time you insert a new product. Is this correct?

    Starting up is not an issue, I would simply enter all of one type of item (say water softeners) before adding the next type. Once everything is set up though , if I have 300 items and my softeners are ids 100-150, when I add a new product the new softener will be id 301, where I would want it to be id 151. Same thing applies to the categories.
    You are simply updating a master list for any new category and product you wish to include in your catalog, and you are not referring to an actual inventory item that you purchased and that you wish to include in your goods available for sale. Is this also correct?

    product table consists of the product id number and the main category id number it is associated with. To the best of my knowledge there is no direct link, if you delete a category or rename it, the product is still linked to the id number regardless of what it actually represents. There is also detailed info, independent of any other tables.
    This means there exists an ad-hoc relationship between Product table and Category table. If I delete a category, the ID value of the deleted entry can still be found in the Product table.

    I now understand the behavior that you want for the primary keys in the Products table.

    Now, here are my questions:
    (1) How does the dynamic changing of primary keys in the Product table benefit your site? For example, do you need the grouping for displaying "suggested" inventory items when a customer encodes a certain ProductID?

    (2) Do you need to update the Primary Keys while your customers are currently placing orders thru your site?

    (3) Do all primary keys have to be incremented by 1 (at most 1) every time you insert a new product?

    I think your goal is to eliminate your manual process of updating data using Excel. At some point in reading your post, I realized that you can improve your database design, thereby avoiding the need to update your primary keys dynamically.

    I will be very careful with updating primary keys the way you want it to happen though. Your update routine probably has to be encapsulated in a Transaction (ADODB in VBA has it). Remember that you are working with a large record of master lists and actual customer orders, so updating your keys might leave your database in an inconsistent state.

    But if you want to proceed with solutions based on your specifications, I'll be glad to offer some help.

    Good luck.

  5. #5
    AquaChaos is offline Novice
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Jun 2010
    Posts
    3
    Reading this point of yours, I conclude that customers placing their orders thru your site do not list inventory items according to ProductID because this ID may change at any time you insert a new product. Is this correct?
    Correct. Product ID is used only for referencing the product and category inside the shopping cart and for navigation, i.e. if someone clicks a link to view Item1, the cart queries the MySQL database for product with ID 1 and returns the information associated with it. For actual ordering purposes there is an Item number that will remain the same.

    You are simply updating a master list for any new category and product you wish to include in your catalog, and you are not referring to an actual inventory item that you purchased and that you wish to include in your goods available for sale. Is this also correct?
    Correct. While there is a field named In_Stock, we do not currently use any kind of program to track our inventory. However, if something of that nature will affect how things need to be done it would be best to allow for syncing of that info. There is a field Qty_Sold that tracks how many of each item is sold, and at some point we may attempt to maintain an In Stock level (though it may be difficult, as not all of our sales are done through the website and not all of our items are stocked in our location or even a single location.)


    This means there exists an ad-hoc relationship between Product table and Category table. If I delete a category, the ID value of the deleted entry can still be found in the Product table.
    Correct.

    I now understand the behavior that you want for the primary keys in the Products table.

    Now, here are my questions:
    (1) How does the dynamic changing of primary keys in the Product table benefit your site? For example, do you need the grouping for displaying "suggested" inventory items when a customer encodes a certain ProductID?
    The benefit is for myself alone, allowing me to easily find and modify an item. We have many thousands of items and I plan on entering the biggest sellers and setting the site live. Then as time permits adding the rest, plus we are constantly adding items. By maintaining an order of items in a given range are in a given category I can more easily maintain the database and make updates and modifications down the road. The cart does offer a suggested item feature, but I it is something that is done manually i.e. there is a separate table product_related, it consists of an entry for each item and each relation, Item1 - Item2, Item1 Item3, so it would need to change dynamically as well.

    (2) Do you need to update the Primary Keys while your customers are currently placing orders thru your site?
    No, updating and uploading can be two separate events, it does not need to be immediate. It is probably something I would do early in the day or late at night, as it may cause a hiccup in the navigation. (Someone looking at Item1 at web address www.site.com/index.php?product=1 after a refresh may see NewItem1 when the id's were adjusted).

    (3) Do all primary keys have to be incremented by 1 (at most 1) every time you insert a new product?
    Not real sure I follow. If I had items 1-10, and added an item at 8, I would think logically 8>9, 9>10, 10>11 would be easiest, but if there is a better/easier way I don't think single digit increment it a requirement.

    I think your goal is to eliminate your manual process of updating data using Excel. At some point in reading your post, I realized that you can improve your database design, thereby avoiding the need to update your primary keys dynamically.
    At this point, I was using Excel simply because it allowed me to insert lines wherever I wanted, and with a simple =ROW()-1 formula (the -1 to account for the header row) in the first column and using absolute linking I was able to accomplish what I was wanting, at least inside a single table. I haven't attempted linking multiple tables yet.

    If improvement in the database design is possible without changing it's structure I am all for it. However, if it involves changing the structure, the cart is php ran and references the tables and fields as they are and would require a LOOOOOT of reprogramming. (Original programming was not done by me, or I would have started out with a better design ).

    I will be very careful with updating primary keys the way you want it to happen though. Your update routine probably has to be encapsulated in a Transaction (ADODB in VBA has it). Remember that you are working with a large record of master lists and actual customer orders, so updating your keys might leave your database in an inconsistent state.
    Thanks for pointing that out. The cart does in fact refer to the item by id, so I would have to link to yet another table to make it feasible, and once I start messing with the order history I would rather not risk screwing it up


    But if you want to proceed with solutions based on your specifications, I'll be glad to offer some help.

    Good luck.
    Any suggestions you can offer are appreciated, I would still like to be able to group things somehow. Thinking just now off the top of my head I should be fairly easy to sort the columns by catID and that would help

    If you have the time, I would still like to know how I can meet my specifications, in case I need it for a future application.

    On a side note, I have been doing research and learning about Access and can now create a form from a table (as I said, pretty new ) which is a pretty convenient way to manipulate the data, but when I am linked to the MySQL database, I can't change the design much (width of ALL fields together, and can't change the height) which would help as some fields will contain quite a large amount of info. Also, when I do change the info, it does not reflect it back in the table. Probably a pretty noob failure here, but I can learn if I don't ask

    Thanks for all your help!

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

Similar Threads

  1. one form two sources - invalid control source msg
    By techexpressinc in forum Forms
    Replies: 1
    Last Post: 05-28-2010, 02:11 PM
  2. Link Master Field and Link Child Field
    By evander in forum Forms
    Replies: 2
    Last Post: 05-25-2010, 09:13 PM
  3. Replies: 2
    Last Post: 05-25-2010, 02:45 PM
  4. Cross check data from 2 sources
    By Zukster in forum Queries
    Replies: 7
    Last Post: 09-22-2009, 10:54 AM
  5. 2 SQL Database sources one frontend
    By kevinharding in forum Programming
    Replies: 3
    Last Post: 03-22-2006, 06:34 AM

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