Editing an SQL Server table in Excel


I need to create an excel workbook which would allow to make changes to an sql server table.

I have come along one example on the internet ( see link), but it seems to be uncomplete and the examlpe file stated is not to be found. I wonder if some could help me to make this VBA code operational.

 link to example

Looking forward to an expert assistances.

Kostas Vilimas
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Any reason you are not using ssms/e? The same access as you need with excel, you would have with ssms/e.
You could use the query editor to issue the updates.
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
Youre supposed to build the file yourself.

Its not that hard. There seems to be information missing in:
Now retrieve the primary key information, using SQL Server's table meta data...
But copy text above, and below the box (including the box) and paste to notepad and youll see the complete code.

I e create a excelfile. Add a sheet for a table in your db, press (im on a mac but i think this is the shortcur) ALT+F11 and the vba window shows. In here find the code headings meantioned in the article, and fill in your code. Save both in macro/vba program AND the Excel file (it contains the macro/VBA). And by the way, the language described is VBA for Applications. VBA for Excel, its similar but not the same as regular VBA code.

Try on a simple small testdb. I would only recommend this on fairly small tables since a opening of the worksheet means getting the full table downloaded from SQL.

If it was up to me, I would propose that SSMS or SSMSE would be used instead, in there people could edit the tables, in a program thats designed for this. But thats not really what you asked.

Regards Marten
kvilimasAuthor Commented:
where can I find the file you mention?
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Marten RuneSQL Expert/Infrastructure ArchitectCommented:
The file is an Excel file.

I e open Excel, and create a file (save that is).
Then it's CTRL+F11 (perhaps SHIFT+F11 or ALT+f11). And follow instructions.

The user and pwd mentioned is a sql user. If you need a createf user un the desired SQL instance. And it needs access in the database in order to work.

Regards Marten
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
The GUI went bananas in my Iphone,
If you need a createf user un
Should read
You need to create a user in
kvilimasAuthor Commented:
yes but there is no file attached to any of your comments.
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
Do you have Excel, microsoft excel?
Create a new document within there!
kvilimasAuthor Commented:
but what I was asking for,  to get the working code. Yes, of course I have excel, and can creat a new document and open vba editor. You sugesting me to write this code? What kind of help is that?
You are doing twice the work than is necessary. The generate query and then setup the code to handle the update within excel compared to using SSMS/SSMSE which outputs the data in a similar fashion which I do not understand what is the end goal. Are you planing on distributing or sharing this excel spreadsheet and that is the reason you are looking to make it include code to handle the update?
This further poses an issue dealing with locking/race conditions where two people use the spreadheet and update the same record.
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
I found the file on:

You can download it from there, but you still need the database he used!


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
Do you need further assistance? Or is the question solved?

Regards Marten
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.