I've got access97 and sql2005, and
I've got a DSN to a sql2005 db and
I've linked a sql2005 table (tblCust) and
I've created an access query (qryCust) and
I'm using the copyFromRecordset method of an DAO recordset to create
an excel worksheet
after an office update, this is failing with 430 class doesn't support
automation
microsoft suggests using ADO and getRows()

so I want to change this DAO
dim rs as dao.recordset
set rs = currentdb.openRecordset("qryCust")
blah.copyRecordset rs


now I'm trying to use ADO to do the same thing
Dim rs As New ADODB.Recordset
Dim cnt As New ADODB.Connection
Dim strSql As String

strSql = "qryCust"
cnt.Open "Dsn=dsnSql;Uid=me;Pwd=12345678"
rs.Open strSql, cnt
recArray = rs.GetRows
etc

the cnt.open statement works
the rs.open statement gives me error "couldn't find stored procedure
qryCust", which seems to indicate that an ADO connection wants
everything to exist on the sql server

I've proven this by changing strSql to "dbo.tblCust"

but is there a way, by changing the connection string, to use the
existing query, qryCust ?

In a code module - goto tools/References and make sure you have selected
Microsoft ActiveX Data Objects 2.? Library (make sure you select 2.5 or
higher - don't use 2.1)

Then ADODB is used like this (don't use your ODBC connection - it
totally defeats the point of using ADO - plus, I don't think it works).

------------------------------------------------
Dim cmd As New ADODB.Command, RS As New ADODB.Recordset
Dim db As DAO.Database, RS1 As DAO.Recordset, i As Integer

cmd.ActiveConnection = "Provider=SQLOLEDB; Data
Source=yourSqlSvr;Database=YourDB;Trusted_Connecti on=Yes"

cmd.ActiveConnection.CursorLocation = adUseClient
cmd.CommandType = adCmdText
cmd.CommandText = "Select t1.* From tbl1 t1 Join tbl2 t2 On t1.ID =
t2.ID And t1.fld1 = t2.fld2 Where fld1 = 'something'"

Set RS = cmd.Execute
Set db = CurrentDb

Set RS1 = db.OpenRecordset("yourAccessTbl")
'--here is where you transfer the date from
'--the ADO recordset to the Access table
'-- you need to use a DAO recordset

Do While Not RS.EOF
RS1.AddNew
For i = 0 To rs.Fields.Count - 1 '--field count
RS1(i) = RS(i) '--begins at 0
Next
RS1.Update
RS.MoveNext
Loop

------------------------------------------------

In this sample, RS and RS1 contain the exact same fields.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

On Mar 25, 1:34*pm, Rich P <rpng... (AT) aol (DOT) com> wrote:
what I need is to create an ADO recordset pointing to "myAccessQuery"
so that I can use the ADO's recordset getRows() function

can this be done ?

or do I need to change the "myAccessQuery" to a sql server view so
that I can use
cmd.CommandText = "Select * From mySqlView"

On Mar 25, 1:56*pm, Roger <lesperan... (AT) natpro (DOT) com> wrote:
looks like this works
strSql = "Provider=Microsoft.jet.oledb.4.0;Data Source=" & _
CurrentDb.Name & ";"
cnt.Open strSql

strSql = "myAccessQuery"
rsT.Open strSql, cnt


but what I can't figure out... is connection 'cnt' referring to the
mdb running the vba or did it open a connection to a second instance
of the mdb

reason I'm asking, if 'myAccessQuery' is a passthrough query, and the
query's sql string was set just prior to the connection, am I
executing the new sql string ?

Lets analyze your problem: You are going to do something in Access with
some data from a sql server, and you are not clear how to retrieve this
data using ADO. You have a query with some sql statements which will or
used to retrieve a dataset from the sql server. The query has a name -
like qrySteve.

All I am showing you is how to retrieve the data from the sql server and
put it in Access in a table where you can use DAO and Jet sql to
manipulate the data further. Forget about GetRows - you don't need that
(unless you are writing a vb script for an asp page). Forget about
qrySteve. Just take out the sql statements and use them as the
commandText for your ADODB command object. The ADODB command Object
will populate an ADODB recordset which you then transfer to a DAO
recordset which writes the data to a table in your Access mdb. Very
simple. Just replace the names with your actual object names.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

On Mar 25, 3:56*pm, Roger <lesperan... (AT) natpro (DOT) com> wrote:
A DAO.Recordset has a GetRows method that does the same thing
the ADODB.Recordset GetRows method does. If a two dimensional
array is what you are after, you don't need to convert anything to
ADO.

On Mar 25, 8:54*pm, rkc <r... (AT) rkcny (DOT) com> wrote:
just tried it, behaviour is slightly different

dim rsT as dao.recordset
dim recArray as variant

strsql = "select * from ..."
Set rsT = CurrentDb.OpenRecordset(strSql)
If (Not (rsT.EOF)) Then
recArray = rsT.GetRows
lngRecCount = UBound(recArray, 2) + 1
endif

lngRecCount has a value of 1



dim rsT as new adodb.recordset
dim recArray as variant

strSql = "Provider=Microsoft.jet.oledb.4.0;Data Source=" & _
CurrentDb.Name & ";"
cnt.Open strSql

strsql = "select * from ..."
rst.open strSql, cnt
If (Not (rsT.EOF)) Then
recArray = rsT.GetRows
lngRecCount = UBound(recArray, 2) + 1
endif

lngRecCount has a value of 1465 (which correct)

I believe this is related to the default value of the parameter passed
to getRows()

On Mar 26, 11:29*am, Roger <lesperan... (AT) natpro (DOT) com> wrote:
But you can use DAO.Recordset.GetRows to return the rows of
a recordset as two dimensional array. Isn't that the point?

On Mar 26, 2:01*pm, rkc <r... (AT) rkcny (DOT) com> wrote:
yup, just need to determine the record count first

dim rsT as dao.recordset
dim recArray as variant
dim lngRecCount as long


strsql = "select * from ..."
Set rsT = CurrentDb.OpenRecordset(strSql)
If (Not (rsT.EOF)) Then
rst.moveLast
lngRecCount = rst.recordCount
rst.moveFirst
recArray = rsT.GetRows (lngRecCount)
lngRecCount = UBound(recArray, 2) + 1
endif