Updating SQL table with Excel

[travisbrown]

I'm trying to set up Excel as a data entry front end for a SQL2K table. I see there are various ways to do this.

The end user will probably want to keep the .xls on their desktop, and the file may move around from user to user. All the examples I've seen have the workbook on a share and you set up a linked server on SQL Server with a declaration of the .xls location and use in combination with a select macro in XLS.

What's the best approach for this? Can this be done without setting up a linked server - just a dsn-less macro in the workbook that will insert and select on the db table? This is how far I've got:

Sub PSSPROD_MASTER_TMI_TEMPLATE()
    Dim qt As QueryTable
    Dim servername, database, username, password
    servername = "servername"
    database = "db"
    uid = "abc"
    pwd = "def"
    
    ' Set up the SQL Statement
    sqlstring = "SELECT col1,col2... FROM table"
    connstring = "odbc;Driver={SQL Server};Server=" & servername & ";Database=" & database & ";UID=" & uid & ";PWD=" & pwd & ""
    With ActiveSheet.QueryTables.Add(Connection:=connstring, Destination:=Range("A1"), Sql:=sqlstring)
     .Refresh
    End With
End Sub

 

[SkipVought]

Hi,

I don't think that you want to do this with a QueryTable, since you wnat to UPDATE the SQL Server Table. You'll need to employ ADO, and therefore, it will be a VBA solution.

Please post further inquries in Forum707.

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[travisbrown]

Oh, yeah, I realize that. I was just initially populating the worksheet with the db data. From the examples I'd seen, you'd do this QueryTable sub in conjunction with a linked table in SQL Server.

I'll take this to VBA.

Thanks Skip.