Hi,
I have an MS SQL Server database which contains a simple table with five columns. It is not linked to any other tables.
I read information in this table from a phone system and use it to route calls.
I want the customer to be able to modify data in this table and want to find a simple way to allow them to do this.
The users will be non technical and will use Windows 10 with MS Office installed.
Bearing in mind that I am not a developer or dbadmin what would be the easiest way to allow this?
I have played with Excel and I can view data from the table without much trouble but cannot find a way to update data using it.
Coding a web portal is not an option unless it is pretty simple.
Can Excel, Access or anything else provide this?
Access.
It sounds like a really bad idea but
https://www.toadworld.com/platforms/sql-server/w/wiki/10392.editing-an-sql-server-table-in-excel
We use an Access front end to view, report from, edit and add/delete data in linked SQL tables with ODBC connection to the user machines but it's all on the same office network/domain. We were looking at publishing the Access stuff to a website to allow external access but MS have just pulled that functionality from Access so that route isn't going to work now.
I'm no expert and have no experience with linking our Access front end to SQL Server on another network which is, I think what you're asking for.
So to try and salvage some usefulness from my ramblings:
- Yes, you could use an Access front end to do that IF you can get the connection between your SQL Server and your customer to work. I suspect that's a big IF!
How many rows?
Are the customers to access all the data or just their own?
Think a shared spreadsheet like the chub club one might be the best option.
Access.
Good answer, well explained 😉
It'd be trivial to create a Windows app to allow this to be updated - assuming the users can connect to SQL and that you trust them to do the updates of course..
[i]Cougar wrote:[/i]
Access.
pfft, just give them SSMS - what could possibly go wrong?
Access front end is the easiest way if they have MS Office installed. If you've already managed to connect to the database from Excel then you will probably manage just fine from Access. There is a bit of a learning curve although it's not hugely steep for what you are trying to do.
Cheapest way is to buy something like 'Access for dummies' and do it yourself (assuming you enjoy that stuff and aren't counting your time)
Easiest is to pay someone. It's a very easy task for someone who has used Access before and they can probably help sanitise the data a bit - never trust users to always pay attention to what they enter
Easy way to allow users to break a SQL database
ftfy 😉
PHPmyadmin or similar
Thanks for the responses. The table is really simple but it has to live on SQL server for my phone system to access it.
The SQL server and users will be in the same Windows domain so Access looks favourite but I will check out the link supplied by oldtennishsoes.
stevehine is it really easy to create a Windows app to do this? Can you give me some pointers on how to start - e.g. what language/app etc.
I am even giving you a link. Does pretty much what you want out-of-the-way box if I understand your requirements
Hi Cletus,
Yes it is - a simple winforms app in VB/c# with a bound datagrid is all you really need - can you post the table definition here (or drop me an email - it's in my profile)
Phpmyadmin might work ... But it's a bit overkill imo !
[quo https://www.phpmyadmin.net/
I am even giving you a link. Does pretty much what you want out-of-the-way box if I understand your requirements
Requirements understanding fail. Microsoft SQL not MySQL!
Any of the above will work but the most important thing is creating a login in SQL Server which restricts the user to select/insert/update/delete in your 1 simple table only - and nothing else.
Requirements understanding fail. Microsoft SQL not MySQL!
Yes! Total miss read. Must be an equivalent out there.
please don't use access - it's the bane of any DBA's life
What you want can be done but as mentioned, you really need to lock it down so something along the lines of
Grant update, select on [table] to {user}
(you did only mention update, and not insert or delete)
The other thing that would be handy would be a trigger that populates another table so you can see the before and after data - just in case. If you only want a user to update their own rows, and not other people's, it does get a little more complicated, but can be done.
Anyway - here's a nice webpage that should explain a bit more. Or here
As it's potentially open to a massive number of potential users, have you got Express edition? If you need a larger DB than Express allows, Mr Microsoft may not be entirely happy if you're using Standard.
Is it a live update, or can you pull it once every night? You could do something like this, with a scheduled job
Er.. that seems like a rather in depth tool to give a handful of users access to update a single table...
Just give them the SA username and password right? 😂
A web page is actually probably exactly what you need and likely not too tricky if you use SQL Authentication (secure the page regardless of where its served) - but Access (assuming this is internal only) is perfectly acceptable (at this level) and probably the quickest route to a solution.
Thanks for the responses - I have got access to the SQL data as a linked table in Access - pretty simple.
Sample data is shown below - the top row is the column names.
CallingNumber,CustomerName,Queue
1234,Trek,Standard
2345,Marin,VIP
5678,Orange,VIP
4567,Canyon,Standard
The CallingNumber column contains a unique value for each customer. The phone system will look this up is the database, retrieve the customer name for a screenpop and will use the Queue value to send the call to a standard or VIP queue.
I realize the database is very simple but it has to live in SQL server as the phone system integrates with that (it could alternately be stored in an XML file).
stevehine - would it be easy to create a Winforms app to link to SQL and manage this? - I need users to be able to create, modify and delete entries.
If anyone can recommend a way of managing this type of data stored in an XML file I would be interested in that too.
Thanks for all the responses so far.
<div class="bbp-author-role"></div>
If you have a linked table in Access then you are most of the way there already. All you need now is a form with some checking to avoid duplicate numbers, names, misspellings etc and you are good unless you want to get more complex
Hey Cletus - yes this would be quite easy to put together - drop me an email if you'd like me to help out; it's in my profile. I'm guessing this ties in to your other thread around standalone exe's too ?
I'd be building a simple webpage and 4 REST web services for your Insert / Update / Delete / Read operations.
As above, tie down the database permissions with a user that only has access to the specified table. Other things I'd be asking are - how often is the table read? How often will it be updated? Do the updates need to be applied in real time? MS access doesn't play very well with multiple users updating at the same time.
