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. (;>)
4 34684
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.
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
-
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
-
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.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Robert Fitzpatrick |
last post by:
I am running PostgreSQL 7.4.5 and have a trigger on a table called
tblriskassessors which inserts, updates or delete a corresponding record
in tblinspectors by lookup of a contact id and license...
|
by: beary |
last post by:
I am using php5 with mysql and also using excel 2003 running on winxp.
Anyway, I am currently opening my excel.xls file, then saving as csv file, then closing, then copying it over to the web...
|
by: somanyusernamesaretakenal |
last post by:
What I am trying to achieve:
Basically I have generated a report in access. This report needs to be updated using excel. (Updating the new data, not changing existing data)
What I did was I...
|
by: OldStd |
last post by:
Updating data using 2 data sets
I am having some problems in updating the database using two datasets as suggested by someone.
1. Data is displayed in a data grid from a dataset generated using...
|
by: =?Utf-8?B?RGFuaWVs?= |
last post by:
Hello i've got a little bit of problem. i wrote a small script that will
update a record using vb.net for the web but i set my table so that whenever
a record is update to autmatically to get the...
|
by: Ciara9 |
last post by:
I am having problems trying to update a field in a database using a field in a form. I currently have two fields, Today and Tomorrow in a table named Date. The Today field automatically defaults to...
|
by: comp21 |
last post by:
Hi,
Now, I have retrieved or imported data from excel spreadsheet to vb6 application. Now I want to compare this part of data with the one already existing in mssql2000 server(table name being...
|
by: alnoir |
last post by:
I'm trying to update some records using the UPDATE and SELECT query.
I have two databases. The first database (db1) is a subset of the second database (db2). However, the first database is...
|
by: LoanB |
last post by:
Hey guys need some info here.
I am adding, updating and deleting data on a datagrid. I pass the datagrid's datasource (datatable dt) to my data layer.
Assuming I have already created and...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
| |