423,688 Members | 1,889 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,688 IT Pros & Developers. It's quick & easy.

Updating SQL Server Table Using Excel

P: 9
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. (;>)
May 3 '06 #1
Share this Question
Share on Google+
4 Replies


P: 36
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.
May 3 '06 #2

100+
P: 135
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.
Expand|Select|Wrap|Line Numbers
  1. '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
  2.  
  3. Global Const UseDatabase = "BudgetRequest2006_07SQL"
  4.  
  5.  
  6.  
  7. Function GetADORS(strSql As String) As ADODB.Recordset
  8.  
  9.  
  10. Dim objConn As ADODB.Connection
  11.  
  12. 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
  13.     ' Note, UseDatabase is a Global constant located in ModADO
  14.     ' Change the Name there to set which Database to use
  15.     'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
  16.  
  17.  
  18. Set objConn = New ADODB.Connection
  19. objConn.Open "Provider=sqloledb.1;data source=YourServerHere;Initial catalog=" & UseDatabase & ";Integrated Security = SSPI;"
  20.  
  21. Set GetADORS = New ADODB.Recordset
  22. GetADORS.Open strSql, objConn, adOpenStatic, adLockReadOnly
  23.  
  24. End Function
  25. Function ADOExecuteSql(strSql As String)
  26. On Error GoTo Errhandler
  27. Dim objConn As ADODB.Connection
  28. Set objConn = New ADODB.Connection
  29.  
  30.  
  31. 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
  32.     ' Note, UseDatabase is a Global constant located in ModADO
  33.     ' Change the Name there to set which Database to use
  34.     'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
  35.  
  36.  
  37. objConn.Open "Provider=sqloledb.1;data source=YourServerHere;Initial catalog=" & UseDatabase & ";Integrated Security=SSPI;"
  38. objConn.Execute (strSql)
  39. objConn.Close
  40. Exit Function
  41. Errhandler:
  42.     MsgBox "Error executing the SQL statement, error # " & Err.Number & ", " & Err.Description, vbOKOnly, "Error"
  43.         objConn.Close
  44. End Function
  45.  
  46. Function OpenADORS(strSql As String) As ADODB.Recordset
  47.  
  48. Dim objConn As ADODB.Connection
  49. Set objConn = New ADODB.Connection
  50.  
  51.  
  52. 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
  53.     ' Note, UseDatabase is a Global constant located in ModADO
  54.     ' Change the Name there to set which Database to use
  55.     'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
  56.  
  57. objConn.Open "Provider=sqloledb.1;data source=YourServerHere;Initial catalog=" & UseDatabase & ";Integrated Security=SSPI;"
  58.  
  59. Set OpenADORS = New ADODB.Recordset
  60. OpenADORS.Open strSql, objConn, adOpenDynamic, adLockOptimistic
  61.  
  62.  
  63.  
  64. End Function
  65. Function GetADORSP(strSql As String) As ADODB.Recordset
  66. On Error GoTo Err_GetADORSP
  67.  
  68. Dim objConn As ADODB.Connection
  69.  
  70.  
  71. Set objConn = New ADODB.Connection
  72. objConn.Open "Provider=sqloledb.1;data source=YourServerHere;Initial catalog=" & UseDatabase & ";Integrated Security=SSPI;"
  73.  
  74. Set GetADORSP = New ADODB.Recordset
  75. GetADORSP.Open strSql, objConn, adOpenStatic, adLockReadOnly
  76.  
  77. Exit Function
  78. Err_GetADORSP:
  79.     MsgBox "Error getting data, error #" & Err.Number & ", " & Err.Description
  80.  
  81. End Function
  82.  
You need to reference ADO 2.0 or higher

In your events you would do something like this
Expand|Select|Wrap|Line Numbers
  1. Dim rs as adodb.recordset
  2. Dim strSql as string
  3.  
  4. strSql = "Select query here"
  5. Set rs = GetADORS(strSql)
  6.  
  7. 'Work with the data
  8.  
  9. Set rs = nothing
  10.  
May 3 '06 #3

P: 9
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.
Expand|Select|Wrap|Line Numbers
  1. '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
  2.  
  3. Global Const UseDatabase = "BudgetRequest2006_07SQL"
  4.  
  5.  
  6.  
  7. Function GetADORS(strSql As String) As ADODB.Recordset
  8.  
  9.  
  10. Dim objConn As ADODB.Connection
  11.  
  12. 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
  13.     ' Note, UseDatabase is a Global constant located in ModADO
  14.     ' Change the Name there to set which Database to use
  15.     'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
  16.  
  17.  
  18. Set objConn = New ADODB.Connection
  19. objConn.Open "Provider=sqloledb.1;data source=YourServerHere;Initial catalog=" & UseDatabase & ";Integrated Security = SSPI;"
  20.  
  21. Set GetADORS = New ADODB.Recordset
  22. GetADORS.Open strSql, objConn, adOpenStatic, adLockReadOnly
  23.  
  24. End Function
  25. Function ADOExecuteSql(strSql As String)
  26. On Error GoTo Errhandler
  27. Dim objConn As ADODB.Connection
  28. Set objConn = New ADODB.Connection
  29.  
  30.  
  31. 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
  32.     ' Note, UseDatabase is a Global constant located in ModADO
  33.     ' Change the Name there to set which Database to use
  34.     'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
  35.  
  36.  
  37. objConn.Open "Provider=sqloledb.1;data source=YourServerHere;Initial catalog=" & UseDatabase & ";Integrated Security=SSPI;"
  38. objConn.Execute (strSql)
  39. objConn.Close
  40. Exit Function
  41. Errhandler:
  42.     MsgBox "Error executing the SQL statement, error # " & Err.Number & ", " & Err.Description, vbOKOnly, "Error"
  43.         objConn.Close
  44. End Function
  45.  
  46. Function OpenADORS(strSql As String) As ADODB.Recordset
  47.  
  48. Dim objConn As ADODB.Connection
  49. Set objConn = New ADODB.Connection
  50.  
  51.  
  52. 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
  53.     ' Note, UseDatabase is a Global constant located in ModADO
  54.     ' Change the Name there to set which Database to use
  55.     'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
  56.  
  57. objConn.Open "Provider=sqloledb.1;data source=YourServerHere;Initial catalog=" & UseDatabase & ";Integrated Security=SSPI;"
  58.  
  59. Set OpenADORS = New ADODB.Recordset
  60. OpenADORS.Open strSql, objConn, adOpenDynamic, adLockOptimistic
  61.  
  62.  
  63.  
  64. End Function
  65. Function GetADORSP(strSql As String) As ADODB.Recordset
  66. On Error GoTo Err_GetADORSP
  67.  
  68. Dim objConn As ADODB.Connection
  69.  
  70.  
  71. Set objConn = New ADODB.Connection
  72. objConn.Open "Provider=sqloledb.1;data source=YourServerHere;Initial catalog=" & UseDatabase & ";Integrated Security=SSPI;"
  73.  
  74. Set GetADORSP = New ADODB.Recordset
  75. GetADORSP.Open strSql, objConn, adOpenStatic, adLockReadOnly
  76.  
  77. Exit Function
  78. Err_GetADORSP:
  79.     MsgBox "Error getting data, error #" & Err.Number & ", " & Err.Description
  80.  
  81. End Function
  82.  
You need to reference ADO 2.0 or higher

In your events you would do something like this
Expand|Select|Wrap|Line Numbers
  1. Dim rs as adodb.recordset
  2. Dim strSql as string
  3.  
  4. strSql = "Select query here"
  5. Set rs = GetADORS(strSql)
  6.  
  7. 'Work with the data
  8.  
  9. Set rs = nothing
  10.  
May 4 '06 #4

P: 9
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.
May 4 '06 #5

Post your reply

Sign in to post your reply or Sign up for a free account.