USing SQL in Excel using VBE

[Guest]

Hello! I'm ate ease using SQL statements to manipulate data in a database.
But my problem is how can I use it in Excel. Pls. I need everyone's help so
badly!

 

[Bob Phillips]

Using ADO.

Here are some examples using an Access database

Sub AddData()
Dim oConn As Object
Dim oRS As Object
Dim sSQL As String

Set oConn = CreateObject("ADODB.Connection")
oConn.Open = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & "c:\bob.mdb"

sSQL = "INSERT INTO Contacts (FirstName, LastName,Phone, Notes) " & _
" VALUES ('Bob','Phillips','01202 345678','me')"
oConn.Execute sSQL

oConn.Close
Set oConn = Nothing
End Sub

Sub GetData()
Const adOpenForwardOnly As Long = 0
Const adLockReadOnly As Long = 1
Const adCmdText As Long = 1
Dim oRS As Object
Dim sConnect As String
Dim sSQL As String
Dim ary

sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & "c:\bob.mdb"

sSQL = "SELECT * From Contacts"
Set oRS = CreateObject("ADODB.Recordset")
oRS.Open sSQL, sConnect, adOpenForwardOnly, _
adLockReadOnly, adCmdText

' Check to make sure we received data.
If Not oRS.EOF Then
ary = oRS.getrows
MsgBox ary(0, 0) & " " & ary(1, 0) & ", " & ary(2, 0)
Else
MsgBox "No records returned.", vbCritical
End If

oRS.Close
Set oRS = Nothing
End Sub

Sub UpdateData()
Const adOpenForwardOnly As Long = 0
Const adLockReadOnly As Long = 1
Const adCmdText As Long = 1
Dim oConn As Object
Dim oRS As Object
Dim sConnect As String
Dim sSQL As String
Dim ary

sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & "c:\bob.mdb"

sSQL = "SELECT * From Contacts"
Set oRS = CreateObject("ADODB.Recordset")
oRS.Open sSQL, sConnect, adOpenForwardOnly, _
adLockReadOnly, adCmdText

' Check to make sure we received data.
If oRS.EOF Then
MsgBox "No records returned.", vbCritical
Else
sSQL = "UPDATE Contacts " & _
" SET Phone = 'None' " & _
"WHERE FirstName = 'Bob' AND LastNAme = 'Phillips'"
oRS.ActiveConnection.Execute sSQL

sSQL = "SELECT * From Contacts"
oRS.ActiveConnection.Execute sSQL
ary = oRS.getrows
MsgBox ary(0, 0) & " " & ary(1, 0) & ", " & ary(2, 0)
End If


oRS.Close
Set oRS = Nothing
End Sub
--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

thanks bob! but I have no Microsoft.Jet.OLEDB.4.0 and one more thing do i
have to add an active x control (im reffering to ADO). pls. reply. thanks
again.

 

[RB Smissaert]

I have a demo workbook that shows you how you can use the free SQLite
for this. This is a very fast database and very simple to install, in fact
there
is no installation at all.
It may not be worth it to go this route if you are only dealing with small
amounts of data, but it will be much better if you have larger amounts.

RBS

 

[Bob Phillips]

You just need a connection string appropriate to your database, see
http://www.carlprothman.net/Default.aspx?tabid=81

What exactly do you mean by the second part?

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

THanks for the quick reply but I just want to ask if i still want to make a
manual connection like going into control panel then find odbs then
blah..blah.. Thanks!

 

[Bob Phillips]

Carl has a section on DSN connections.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)