Solved

Excel - editing data in an online SQL table

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

Enroll in June's Course of the Month

June’s Course of the Month is now available! Experts Exchange’s Premium Members, Team Accounts, and Qualified Experts have access to a complimentary course each month as part of their membership—an extra way to sharpen your skills and increase training.

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

728 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