Basic ADO medthod - working with recordset, plz help.

  • Thread starter Thread starter OverAC
  • Start date Start date
O

OverAC

Hi all,

I'm using ADO as a begginner.
I'm confused with recordset melthod: seek, edit, addnew, sort ...

Thanks
 
Dear Bob Phillips

Thanks for your link, it's very helpful and It helped me to understand
more about ADO. It will be great if those code is for Excel VBA (they
all now are for html :( ).

Thanks anyway.

OverAC
 
Recordsets work in the same way in Excel. You are after all just accessing
via ADO, which simplifies matters. Here is a simple example of reading an
Excel sheet into a recordset

Public Sub GetData()
Dim oConn As ADODB.Connection 'Object
Dim oRS As ADODB.Recordset 'Object
Dim sFilename As String
Dim sConnect As String
Dim sSQL As String

sFilename = "c:\Mytest\Volker1.xls"
sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sFilename & ";" & _
"Extended Properties=Excel 8.0;"

sSQL = "SELECT * FROM [Sheet1$]"

Set oRS = New ADODB.Recordset 'CreateObject("ADODB.Recordset")
oRS.Open sSQL, sConnect, adOpenForwardOnly, _
adLockReadOnly, adCmdText

If Not oRS.EOF Then
ActiveSheet.Range("A1").CopyFromRecordset oRS
Else
MsgBox "No records returned.", vbCritical
End If

oRS.Close
Set oRS = Nothing

End Sub



--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
Thanks. That's great.

a little confusing thing remains, please help me the code to list ou
all records which have a field match my conditions. for ex
CustomerName begins with ABC
(I understand how to start with ADO now, so ignore cornection)
Thanks in advance

OverA
 
Do you want to do it in the recordset, in VBA, or in another Excel
spreadsheet?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
.... or even in the initial query?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
I'm trying to access to a excel database without open it so I used ADO
in VBA from another excel file. That's why I want to know about ADO,
recordset..
I've just discovered a little about SQL but I think your explaination
will make clearer.
Thanks.
OverAC
 
Use the WHERE clause in the SQL.

For instance, assuming you have a column with a header of Name, you can use

sSQL = "SELECT * FROM [Sheet1$] WHERE Name = 'OverAC'"


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
Understood, That's great.
Maybe these are some thing I will ask, but it's great till now.

a little curious and personal, I wonder what "HTH" means (in your
post)

Thanks very much.

OverAC
 
I can't find these kind of info in your site

I am working on examples for that also
 
Thanks Ron de Bruin,

I'm waiting for your update.

Dears,

I have this connection
Set cn = New ADODB.Connection
With cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=" & filepath & _
filename; Extended Properties=Excel 8.0"
.CursorLocation = adUseClient
.Open
End With
Click to expand...
Everytime it runs. the file (connected file) were opened. But I really
don't want that. How can I stop opening the file.
Thanks

OverAC
 
Dears,

Anybody help me. How can I stop opening the file when connecting With
ADO.
Thanks so much

OverAC
 
Dears,

I use ADO to connect to another excel file, and every time whe
connection are made, the data file open. and I don't why! Please hel
to explain this and tell me the way to prevent it. Thanks so much.

OverA
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Show progress with an Asynchronous ADO recordset 4
Problem creating PivotTable from ADO recordset using VBA 0
ADO Recordset to Excel 2007 Worksheet Cell Values via XML 5
ADO Recordset to Msgbox 4
ADO - Access to Excel 5
return ADO record set from Add-In? 1
Counting Access recordset to import to Excel using ADO 5
Retrieving Pipe (|) delimited text file into ADO Recordset 2

Back
Top