Using ODBC & ADO in manipulating data from worksheet. HELP! plssss
 

How can I use this two to manipulate data in my worksheets through userforms.
SQL is OK 4 me but i just don't know how to apply it. I think it's ODBC or
ADO configuration. wahhh. Don't know what to do.. Pls. help MVP's..

Using ODBC & ADO in manipulating data from worksheet. HELP! plssss
 

Here are examples I posated yesterday using ADO.

It is based upon a workbook with a table of data name PeopleData


Option Explicit


Const adOpenForwardOnly As Long = 0
Const adLockReadOnly As Long = 1
Const adCmdText As Long = 1


Dim aryData


Public Sub GetData()
Dim oConn As Object
Dim oRS As Object
Dim sFilename As String
Dim sConnect As String
Dim sSQL As String


sFilename = "c:\TestFolders\Some book 1.xls"
sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sFilename & ";" & _
"Extended Properties=Excel 8.0;"


Set oRS = CreateObject("ADODB.Recordset")


sSQL = "SELECT * FROM PeopleData"
oRS.Open sSQL, sConnect, adOpenForwardOnly, _
adLockReadOnly, adCmdText


' Check to make sure we received data.
If Not oRS.EOF Then
aryData = oRS.Getrows()
Else
MsgBox "No records returned.", vbCritical
End If


' Clean up our Recordset object.
oRS.Close
Set oRS = Nothing


End Sub


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


sFilename = "C:\TestFolders\Some book 1.xls"
Set oConn = CreateObject("ADODB.Connection")
oConn.Open = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sFilename & ";" & _
"Extended Properties=Excel 8.0;"


sSQL = "INSERT INTO PeopleData (FirstName, LastName,Phone, Notes) " & _
" VALUES ('Jim','Brown','01202 345678','Client')"
oConn.Execute sSQL


oConn.Close
Set oConn = Nothing
End Sub


Sub UpdateData()
Dim oConn As Object
Dim oRS As Object
Dim sConnect As String
Dim sSQL As String
Dim ary
Dim sFilename As String


sFilename = "c:\TestFolders\Some book 1.xls"
sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sFilename & ";" & _
"Extended Properties=Excel 8.0;"


sSQL = "SELECT * From PeopleData"
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 PeopleData " & _
" SET Phone = 'None' " & _
"WHERE FirstName = 'Bob' AND LastName = 'Phillips'"
oRS.ActiveConnection.Execute sSQL


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


End Sub


--
HTH

Bob

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

"Chinx21" . wrote in message
...
How can I use this two to manipulate data in my worksheets through
userforms.
SQL is OK 4 me but i just don't know how to apply it. I think it's ODBC or
ADO configuration. wahhh. Don't know what to do.. Pls. help MVP's..