Updating SQL Server Table Using Excel

[MikeG]

Hi there,

I am using SQL Server 2000 and have users that would like to insert and / or update an existing table using Excel as Excel is a tool they understand. Does anyone have an idea how to do that? If you think Access would be easier, I'm sure I could get my users to turned on to that instead. Any help is greatly appreciated. (;>)

[cweiss]

You can do this using DAO or ADO, I believe. You might have an easier time of it using Access, but using Excel is also possible.

In access, you can build a link to the remote table by going to Insert->Table-Link Table. When the file browser pops up, at the bottom where you see Files of Type, select ODBC Databases (should be the last option). You'll get a pop-up that allows you to select the data source. It may be listed on the Machine Data Source tab, if not you'll need to build it (Add New).

After you create your link, you can work with this table just like you would any other table in access (create an append query, append new records).

I've only done this with an Oracle database though, so it could differ with SQL Server.

If you want to go the Excel route, you'll need to write code to handle it. You can use either ADO or DAO for that.

There are probably other ways to do this as well, but those are the methods I generally use if I have to go through Excel or Access.

[CaptainD]

I have a budget Excel sheet that, through a module in VBA using ADO and VBA events access a SQL Database, populates the sheet, saves and deletes records etc.

Here is the connection "Functions" in the Module with a few changes.

'You could remove the global Const, I have it here because I use this on test databases 'as well as production and it's easier to just make one entry
 
Global Const UseDatabase = "BudgetRequest2006_07SQL"
 
 
 
Function GetADORS(strSql As String) As ADODB.Recordset
 
 
Dim objConn As ADODB.Connection
 
'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
    ' Note, UseDatabase is a Global constant located in ModADO
    ' Change the Name there to set which Database to use
    'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
 
 
Set objConn = New ADODB.Connection
objConn.Open "Provider=sqloledb.1;data source=YourServerHere;Initial catalog=" & UseDatabase & ";Integrated Security = SSPI;"
 
Set GetADORS = New ADODB.Recordset
GetADORS.Open strSql, objConn, adOpenStatic, adLockReadOnly
 
End Function
Function ADOExecuteSql(strSql As String)
On Error GoTo Errhandler
Dim objConn As ADODB.Connection
Set objConn = New ADODB.Connection
 
 
'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
    ' Note, UseDatabase is a Global constant located in ModADO
    ' Change the Name there to set which Database to use
    'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
 
 
objConn.Open "Provider=sqloledb.1;data source=YourServerHere;Initial catalog=" & UseDatabase & ";Integrated Security=SSPI;"
objConn.Execute (strSql)
objConn.Close
Exit Function
Errhandler:
    MsgBox "Error executing the SQL statement, error # " & Err.Number & ", " & Err.Description, vbOKOnly, "Error"
        objConn.Close
End Function
 
Function OpenADORS(strSql As String) As ADODB.Recordset
 
Dim objConn As ADODB.Connection
Set objConn = New ADODB.Connection
 
 
'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
    ' Note, UseDatabase is a Global constant located in ModADO
    ' Change the Name there to set which Database to use
    'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
 
objConn.Open "Provider=sqloledb.1;data source=YourServerHere;Initial catalog=" & UseDatabase & ";Integrated Security=SSPI;"
 
Set OpenADORS = New ADODB.Recordset
OpenADORS.Open strSql, objConn, adOpenDynamic, adLockOptimistic
 
 
 
End Function
Function GetADORSP(strSql As String) As ADODB.Recordset
On Error GoTo Err_GetADORSP
 
Dim objConn As ADODB.Connection
 
 
Set objConn = New ADODB.Connection
objConn.Open "Provider=sqloledb.1;data source=YourServerHere;Initial catalog=" & UseDatabase & ";Integrated Security=SSPI;"
 
Set GetADORSP = New ADODB.Recordset
GetADORSP.Open strSql, objConn, adOpenStatic, adLockReadOnly
 
Exit Function
Err_GetADORSP:
    MsgBox "Error getting data, error #" & Err.Number & ", " & Err.Description
 
End Function
 

You need to reference ADO 2.0 or higher

In your events you would do something like this

Dim rs as adodb.recordset
Dim strSql as string
 
strSql = "Select query here"
Set rs = GetADORS(strSql)
 
'Work with the data
 
Set rs = nothing
 

[MikeG]

Thanks CaptainD.
This is quite a bit more programming than I had hoped for as I could probably develop a small Java app using JDBC to do this.

I have found that Access can quite easily be linked to SQL Server tables for ease of maintenance so I have gon e that route.

Many thanks for the code.

Rgds

I have a budget Excel sheet that, through a module in VBA using ADO and VBA events access a SQL Database, populates the sheet, saves and deletes records etc.

Here is the connection "Functions" in the Module with a few changes.

'You could remove the global Const, I have it here because I use this on test databases 'as well as production and it's easier to just make one entry
 
Global Const UseDatabase = "BudgetRequest2006_07SQL"
 
 
 
Function GetADORS(strSql As String) As ADODB.Recordset
 
 
Dim objConn As ADODB.Connection
 
'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
    ' Note, UseDatabase is a Global constant located in ModADO
    ' Change the Name there to set which Database to use
    'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
 
 
Set objConn = New ADODB.Connection
objConn.Open "Provider=sqloledb.1;data source=YourServerHere;Initial catalog=" & UseDatabase & ";Integrated Security = SSPI;"
 
Set GetADORS = New ADODB.Recordset
GetADORS.Open strSql, objConn, adOpenStatic, adLockReadOnly
 
End Function
Function ADOExecuteSql(strSql As String)
On Error GoTo Errhandler
Dim objConn As ADODB.Connection
Set objConn = New ADODB.Connection
 
 
'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
    ' Note, UseDatabase is a Global constant located in ModADO
    ' Change the Name there to set which Database to use
    'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
 
 
objConn.Open "Provider=sqloledb.1;data source=YourServerHere;Initial catalog=" & UseDatabase & ";Integrated Security=SSPI;"
objConn.Execute (strSql)
objConn.Close
Exit Function
Errhandler:
    MsgBox "Error executing the SQL statement, error # " & Err.Number & ", " & Err.Description, vbOKOnly, "Error"
        objConn.Close
End Function
 
Function OpenADORS(strSql As String) As ADODB.Recordset
 
Dim objConn As ADODB.Connection
Set objConn = New ADODB.Connection
 
 
'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
    ' Note, UseDatabase is a Global constant located in ModADO
    ' Change the Name there to set which Database to use
    'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
 
objConn.Open "Provider=sqloledb.1;data source=YourServerHere;Initial catalog=" & UseDatabase & ";Integrated Security=SSPI;"
 
Set OpenADORS = New ADODB.Recordset
OpenADORS.Open strSql, objConn, adOpenDynamic, adLockOptimistic
 
 
 
End Function
Function GetADORSP(strSql As String) As ADODB.Recordset
On Error GoTo Err_GetADORSP
 
Dim objConn As ADODB.Connection
 
 
Set objConn = New ADODB.Connection
objConn.Open "Provider=sqloledb.1;data source=YourServerHere;Initial catalog=" & UseDatabase & ";Integrated Security=SSPI;"
 
Set GetADORSP = New ADODB.Recordset
GetADORSP.Open strSql, objConn, adOpenStatic, adLockReadOnly
 
Exit Function
Err_GetADORSP:
    MsgBox "Error getting data, error #" & Err.Number & ", " & Err.Description
 
End Function
 

You need to reference ADO 2.0 or higher

In your events you would do something like this

Dim rs as adodb.recordset
Dim strSql as string
 
strSql = "Select query here"
Set rs = GetADORS(strSql)
 
'Work with the data
 
Set rs = nothing
 

[MikeG]

Thanks cweiss. I discovered myself yesterday that approach you outlined below for Access. It is easy to implement and use. All my users have Access installed on their systems so this is the solution I have converged on.

CaptainD has also elaborated on the approach you outline for Excel. His code is part of a response to this thread for other users who are interested in Excel, albeit more complicated.

Rgds

You can do this using DAO or ADO, I believe. You might have an easier time of it using Access, but using Excel is also possible.

In access, you can build a link to the remote table by going to Insert->Table-Link Table. When the file browser pops up, at the bottom where you see Files of Type, select ODBC Databases (should be the last option). You'll get a pop-up that allows you to select the data source. It may be listed on the Machine Data Source tab, if not you'll need to build it (Add New).

After you create your link, you can work with this table just like you would any other table in access (create an append query, append new records).

I've only done this with an Oracle database though, so it could differ with SQL Server.

If you want to go the Excel route, you'll need to write code to handle it. You can use either ADO or DAO for that.

There are probably other ways to do this as well, but those are the methods I generally use if I have to go through Excel or Access.