Solved

Excel - editing data in an online SQL table

Posted on 2011-09-07
2
176 Views
Last Modified: 2012-06-27
Hi

I used the following code (recorded as a macro) to import a table from an online SQL database
I want the user to edit certain cells and have these changes also happen in the database.

How could I achieve this?

Thanks
Sub Macro1()
'
' Macro1 Macro
'

'
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
        "OLEDB;Provider=SQLOLEDB.1;Persist Security Info=True;User ID=Gnnn;Data Source=123.220.43.247,6644;Use Procedure for Prepare=1;Auto " _
        , _
        "Translate=True;Packet Size=4096;Workstation ID=USER-PC;Use Encryption for Data=False;Tag with column collation when possible=Fal" _
        , "se;Initial Catalog=psql"), Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdTable
        .CommandText = Array("""psql"".""Graham2"".""Performance""")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .SourceConnectionFile = _
        "C:\Users\User\Documents\My Data Sources\196.220.43.247,1444 psql Performance.odc"
        .ListObject.DisplayName = "Table__196.250.43.247_1411_psql_Performance"
        .Refresh BackgroundQuery:=False
    End With
    ActiveCell.FormulaR1C1 = "Date"
    Range("D10").Select
End Sub

Open in new window

0
Comment
Question by:murbro
2 Comments
 
LVL 10

Accepted Solution

by:
Makrini earned 500 total points
ID: 36501549
It *can* be done, but requires some significant VBA and Sql knowledge.   If you have another option, use it.

  Having said that - here is a reference if you are willing to start to learn it

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

0
 

Author Closing Comment

by:murbro
ID: 36510323
thanks a lot
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

920 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now