?
Solved

Editing an SQL Server table in Excel

Posted on 2012-04-03
11
Medium Priority
?
221 Views
Last Modified: 2012-11-18
Hi,

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.

Sincerely,
Kostas Vilimas
0
Comment
Question by:kvilimas
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
  • 2
11 Comments
 
LVL 79

Expert Comment

by:arnold
ID: 37804071
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.
0
 
LVL 20

Expert Comment

by:Marten Rune
ID: 37804099
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
0
 

Author Comment

by:kvilimas
ID: 37805266
where can I find the file you mention?
0
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 
LVL 20

Expert Comment

by:Marten Rune
ID: 37805291
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
0
 
LVL 20

Expert Comment

by:Marten Rune
ID: 37805304
The GUI went bananas in my Iphone,
If you need a createf user un
Should read
You need to create a user in
0
 

Author Comment

by:kvilimas
ID: 37805427
yes but there is no file attached to any of your comments.
0
 
LVL 20

Expert Comment

by:Marten Rune
ID: 37805440
Do you have Excel, microsoft excel?
Create a new document within there!
0
 

Author Comment

by:kvilimas
ID: 37807579
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?
0
 
LVL 79

Expert Comment

by:arnold
ID: 37807889
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.
0
 
LVL 20

Accepted Solution

by:
Marten Rune earned 1000 total points
ID: 37810274
I found the file on:
http://www.todaydocs.com/index.php?m=url&type=xls&url=http%3A%2F%2Ffiles.cogniview.com%2FUpdate_SQLServer.xlsm&title=Update%20SQLServer.xlsm

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

//Marten
0
 
LVL 20

Expert Comment

by:Marten Rune
ID: 37826619
Do you need further assistance? Or is the question solved?

Regards Marten
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question