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)
Tim, aren’t you learning Python? You can learn the fundamentals of SQL in probably 4 hours of online tutorials. It’s totally worth it.
In Access you can open the SQL view for things IIRC, maybe set up a table and look at the SQL view and get some insight into the PK/FK stuff?
I don’t know what you’re using right now, but I believe you want SQL Server Management Studio to do stuff in a SQL Server instance. From there I believe there is a data editor similar to Access for adding rows.
I’ve done that with select statements. That might give me the tip I need. I am learning Python. I wanted to do SQL the easy way, lol.
I am using the management UI that comes with SQL Server 2014. I have found SQL Server a much better user experience than any of the GUI in the open source (MySQL, etc) stuff I’ve used on my Mac, except for creating relationships, which is super simple in Access. I should just knuckle down and spend a little time learning it, I suppose.
True! I was doing that last night. Instead of typing, I want to select some information from another table. If I don’t, what happens is I end up introducing spelling errors as I input the titles or vendors names, and then the Excel reports I generate (from the finished database) suffer.
I was able to figure out how to make the relationships I needed to make in SQL Server by watching a video and messing around with it. However, I don’t seem to be able to reproduce the lookup relationship that Access creates. It uses a bunch of system relationships to pull this off, but I haven’t quite figured out how it works yet. What I created in SQL Server is similar, but I have to enter the key ID (and my table shows me the key ID instead of allowing me to select from a list of text and showing me the value for the key, instead of the key itself).
Still, it’s pretty good.
I can use select and join statements (that SQL management studio creates for me!) to add the data from this new table to my reports, which was my ultimate goal anyhow.
I was up super late last night figuring all this out and now I’m wasted, and a little annoyed because I haven’t been able to figure out how Access manages to do its magic with just a few extra relationships and an intermediary table, but … I have to remember to celebrate my accomplishments and now I can create relationships in SQL Server.
It’s crazy how you can see something and think: Yes, I want to create that! But then when it comes down to it, I feel like I could invest more time in this (I’m sure I could figure it out) or I could just type the numbers. Hahah, it would be easier to just keep importing Excel spreadsheets, which is what I wanted to avoid in the first place.
