Solved

Excel - editing data in an online SQL table

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Find and Replace Function not working in Excel 13 45
Find results from sql within a time span 11 31
SQL Error - Query 6 26
Text file into sql server 5 26
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

773 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