Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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.. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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.. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Trouble manipulating worksheet chosen from combo box | Excel Programming | |||
Manipulating Controls on a worksheet | Excel Programming | |||
Problems manipulating a EXCEL Worksheet based on a SharePoint List | Excel Programming | |||
Excell HELp PLSSSS | Excel Programming | |||
Manipulating Data in a row | Excel Programming |