Solved

Excel - editing data in an online SQL table

Posted on 2011-09-07
2
175 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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Suggested Solutions

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

747 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

12 Experts available now in Live!

Get 1:1 Help Now