Most of what I need to do with SQL Server I can do by importing Excel spreadsheets. But occasionally it would be easier to add a few rows manually, instead of importing a spreadsheet with only a few rows. I’d like to use Access to do these kinds of updates, so I can just have a GUI to work in. I’m still learning SQL and not that proficient in the query editor.
I can create a table in my SQL Server database and link to it from Access. But in Access I want to be able to select from a lookup, so I don’t have to type in each entry every time.
And there is the problem.
I can easily make an Access database with a lookup that gets its data from another table, but I can’t do that to the linked table in Access, because the linked table has to be modified in SQL Server. From Access all I can do is manually add data by hand. I’ve tried various things to get myself where I want to go, but nothing is working that well. I thought I’d ask here for ideas.
Here is what I’ve tried:
Creating relationships in SQL Server, but honestly all the FK and PK stuff is hard to figure out. I still haven’t been able to accomplish a relationship like the one I have in my Access database. In fact, I’m not even sure what a success looks like in SQL server, because it’s not the same as working in Access. Usually a SQL server has some sort of Web UI front end for making the updates.
Creating the tables and relationships in Access, then importing it all into SQL Server, but the relationships didn’t import correctly (or probably I didn’t set up the import correctly).
Have any of you done this and can you give me some guidance?
Here is what I am after:
- Update a linked SQL table from Access.
- Select from a list of predefined entries on the Access side (for stuff like “author name” and “title”).
- Manually add other times (like number sold, royalties)