manipulating SQL data through Excel
 

Hi everyone,

I have a couple of users (management) that want to keep Excel for editing
data from a database.

How can I display the result of a query (table or view) from a SQL database
to Excel?

I also would like that the database is updated when the user clicks save on
excel.

I would appreciate any direction.

I have programmed quite a lot with vb.net and sql databases but I have never
done VBA for Office programs however I know it is very similar, so if you
could point me out to a guide or tutorial, it will be very helpful.

Thanks

Gentian

manipulating SQL data through Excel
 

This might be a help for getting data to and from Excel and Access: It
includes examples of using variables in SQL queries.
http://www.bygsoftware.com/examples/sql.html

Or you can get there from the "Excel with Access Databases" section on page:
http://www.bygsoftware.com/examples/examples.htm

It demonstrates how to use SQL in Excel's VBA to:

* create a database,
* create a table
* insert records
* select records,
* update records,
* delete records,
* delete a table,
* delete a database.

DAO and ADO files available.

You can also download the demonstration file called "excelsql.zip".

The code is open and commented.


--
Andy Wiggins FCCA
www.BygSoftware.com
Excel, Access and VBA Consultancy
-

"Gentian Hila" wrote in message
...
Hi everyone,

I have a couple of users (management) that want to keep Excel for editing
data from a database.

How can I display the result of a query (table or view) from a SQL
database
to Excel?

I also would like that the database is updated when the user clicks save
on
excel.

I would appreciate any direction.

I have programmed quite a lot with vb.net and sql databases but I have
never
done VBA for Office programs however I know it is very similar, so if you
could point me out to a guide or tutorial, it will be very helpful.

Thanks

Gentian

manipulating SQL data through Excel
 

See if this helps. Don't forget to; Tools|References and check the box for
'Microsoft ActiveX Data Objects 2.x Library'

Use the highest version that will still support your clients.

Public Sub testread()
Dim cnn As ADODB.Connection
Dim rs1 As ADODB.Recordset
Dim strSQL1 As String, strConn
Dim i As Integer
i = 1

'Use for Access (jet)
'strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=C:\Program Files\Microsoft Office\" _
& "OFFICE11\SAMPLES\Northwind.mdb;Persist Security Info=False"

'Use for SQL server (OLEDB)
strConn = "Provider=SQLOLEDB.1;" _
& "Integrated Security=SSPI;" _
& "Persist Security Info=False;" _
& "Initial Catalog=Northwind;" _
& "Data Source=PE1600"


strSQL1 = "SELECT LastName, FirstName " _
& "FROM Employees; "
Set cnn = New ADODB.Connection
Set rs1 = New ADODB.Recordset
cnn.Open strConn
rs1.Open strSQL1, cnn, adOpenForwardOnly, adLockReadOnly
Do While rs1.EOF = False
Sheets("Sheet1").Range("A" & i) = rs1!LastName
Sheets("Sheet1").Range("B" & i) = rs1!FirstName
i = i + 1
rs1.MoveNext
Loop
rs1.Close
cnn.Close
End Sub

Public Sub testwrite()
Dim cnn As ADODB.Connection
Dim rs1 As ADODB.Recordset
Dim strSQL1 As String, strConn
Dim i As Integer
i = 1

'Use for Access (jet)
'strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=C:\Program Files\Microsoft Office\" _
& "OFFICE11\SAMPLES\Northwind.mdb;Persist Security Info=False"

'Use for SQL server (OLEDB)
strConn = "Provider=SQLOLEDB.1;" _
& "Integrated Security=SSPI;" _
& "Persist Security Info=False;" _
& "Initial Catalog=Northwind;" _
& "Data Source=PE1600"


strSQL1 = "SELECT LastName, FirstName " _
& "FROM Employees; "
Set cnn = New ADODB.Connection
Set rs1 = New ADODB.Recordset
cnn.Open strConn
rs1.Open strSQL1, cnn, adOpenDynamic, adLockOptimistic
For i = 1 To 3
rs1.AddNew
rs1!LastName = Sheets("Sheet1").Range("A" & i)
rs1!FirstName = Sheets("Sheet1").Range("B" & i)
i = i + 1
rs1.Update
Next
rs1.Close
cnn.Close
End Sub


--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

"Gentian Hila" wrote:
| Hi everyone,
|
| I have a couple of users (management) that want to keep Excel for editing
| data from a database.
|
| How can I display the result of a query (table or view) from a SQL
database
| to Excel?
|
| I also would like that the database is updated when the user clicks save
on
| excel.
|
| I would appreciate any direction.
|
| I have programmed quite a lot with vb.net and sql databases but I have
never
| done VBA for Office programs however I know it is very similar, so if you
| could point me out to a guide or tutorial, it will be very helpful.
|
| Thanks
|
| Gentian
|
|