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!