Member Avatar for NewVBguy

Hi There,
Can anybody help me with the following:

1. I am trying to open an excel file using adodb connection but i got a run time error on the rs. open. Here is my code

Dim cnn As New ADODB.Connection, rs As New ADODB.Recordset
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\temp\XLS.XLS;Extended Properties=Excel 8.0;"
rs.Open "select * from data", cnn, adOpenStatic, adLockOptimistic, adCmdText


2. Can somebody show how to insert or delete or add a column of a recordset on the fly or at run time.

Many thanks.

Newvbguy

  • 3 Contributors
  • 5 Replies
  • 175 Views
  • 1 Month Discussion Span
  • Latest Post Latest Post by maheshsayani
Member Avatar for Comatose

Firstly, I don't believe Excel works with an ADO connection. You can create an excel object, and work with it, but as far as I know, SQL (Structured Query Languages), and Access (which are database systems) are the ones you can interface with using ADO. Did you read the sticky thread?

Member Avatar for NewVBguy

Hi Comatose,
I just thought it will work because there is no error on opening the connection but just in that opening the recordset that I got the error. I haven't read yet the sticky one but i will try.

thnkx for more....

newvbguy

Member Avatar for NewVBguy

Hi,
I already figured out how this connection work. Here is the code:

Dim cnn As New ADODB.Connection, rs As New ADODB.Recordset
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\temp\XLS.XLS;Extended Properties=Excel 8.0;"
rs.Open "select * from data", cnn, adOpenStatic, adLockOptimistic


I am also working with #2 but I'm still getting an error with this line.

rs.fields.append "fldname", adchar, 20

can anybody know why?

Many thanks.

Newvbguy

Member Avatar for maheshsayani

Here Is the sample code

The Mistake is for Rs.open there should be only 4 arguments

Dim cnn As New ADODB.Connection, rs As New ADODB.Recordset
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\temp\XLS.XLS;Extended Properties=Excel 8.0;"
rs.Open "select * from data", cnn, adOpenStatic, adLockOptimistic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.