Access to Excel 97 gurus help needed.

[Eric]

OK..maybe I am asking the wrong question latelty or not
explaining myself quite right.

How do you gurus get the records from a Access database
put into Excel? I know that you can do it through
automation, but here is my situation.

I have a Excel spreadsheet where users have a dropdown
list and they can select a plant. In our master Access
database, we have product lines associated to a plant in a
query called qryPlantProductLine. When the user selects a
Plant, it should call the query and display the results in
a sheet which the following code runs:

***********************************************

Sub PlantProductLines()
Dim DBFullName As String
Dim Cnct As String, Src As String
Dim Connection As ADODB.Connection
Dim Recordset As ADODB.Recordset
Dim Col As Integer
Dim Row As Integer

Dim strPlantName As String

'Database Information
DBFullName = "J:\QA\QAMaster\QAMaster.mdb"

'Open the connection
Set Connection = New ADODB.Connection
Cnct = "Provider=Microsoft.Jet.OLEDB.4.0; "
Cnct = Cnct & "Data Source=" & DBFullName & ";"
Connection.Open ConnectionString:=Cnct

'Create RecordSet
strPlantName = ActiveSheet.Range("L4").Value '
Set Recordset = New ADODB.Recordset
With Recordset
'Filter
Src = "SELECT qryPlantProductLine.PlantName,
qryPlantProductLine.LineCode, qryPlantProductLine.LineName
FROM `J:\QA\QAMaster\QAMaster`.qryPlantProductLine
qryPlantProductLine WHERE
(qryPlantProductLine.PlantName='Leola')" 'for test puropses
.Open Source:=Src, ActiveConnection:=Connection

*******************************************************

Here's where I am having problems (the rest of the code):

********************************************************
Debug.Print Recordset.MaxRecords ' I get zero here!!!

'Write the field names (which come in)
For Col = 0 To Recordset.Fields.Count - 1
Range("P12").Offset(0, Col).Value =
Recordset.Fields(Col).Name
Next
MsgBox Recordset.RecordCount 'get zero here too!
End With

Set Recordset = Nothing
Connection.Close
Set Connection = Nothing
End Sub

***********************************************

When I go to the Debug.Print .MaxRecords and look in the
Immediate Window, I get zero records returned. If I plug
the SQL statement in ANY Access database, I get results
for the query (they are correct).

Why can't I get any results from my query in Excel if I
get results in a Access query? Am I doing something
wrong???

 

[merjet]

Like I said in reply to your earlier post:
1. the code you posted is missing a line which copies the records
2. with a query (but not a table) you would have to move to the
last record to get the correct record count.

HTH,
Merjet

 

[Thomas Bartkus]

OK..maybe I am asking the wrong question latelty or not
explaining myself quite right.

How do you gurus get the records from a Access database
put into Excel? I know that you can do it through
automation, but here is my situation.

I have a Excel spreadsheet where users have a dropdown
list and they can select a plant. In our master Access
database, we have product lines associated to a plant in a
query called qryPlantProductLine. When the user selects a
Plant, it should call the query and display the results in
a sheet which the following code runs:

***********************************************

Sub PlantProductLines()
Dim DBFullName As String
Dim Cnct As String, Src As String
Dim Connection As ADODB.Connection
Dim Recordset As ADODB.Recordset
Dim Col As Integer
Dim Row As Integer

Dim strPlantName As String

'Database Information
DBFullName = "J:\QA\QAMaster\QAMaster.mdb"

'Open the connection
Set Connection = New ADODB.Connection
Cnct = "Provider=Microsoft.Jet.OLEDB.4.0; "
Cnct = Cnct & "Data Source=" & DBFullName & ";"
Connection.Open ConnectionString:=Cnct

'Create RecordSet
strPlantName = ActiveSheet.Range("L4").Value '
Set Recordset = New ADODB.Recordset
With Recordset
'Filter
Src = "SELECT qryPlantProductLine.PlantName,
qryPlantProductLine.LineCode, qryPlantProductLine.LineName
FROM `J:\QA\QAMaster\QAMaster`.qryPlantProductLine
qryPlantProductLine WHERE
(qryPlantProductLine.PlantName='Leola')" 'for test puropses
.Open Source:=Src, ActiveConnection:=Connection

*******************************************************

Here's where I am having problems (the rest of the code):

********************************************************
Debug.Print Recordset.MaxRecords ' I get zero here!!!

'Write the field names (which come in)
For Col = 0 To Recordset.Fields.Count - 1
Range("P12").Offset(0, Col).Value =
Recordset.Fields(Col).Name
Next
MsgBox Recordset.RecordCount 'get zero here too!
End With

Set Recordset = Nothing
Connection.Close
Set Connection = Nothing
End Sub

***********************************************

When I go to the Debug.Print .MaxRecords and look in the
Immediate Window, I get zero records returned. If I plug
the SQL statement in ANY Access database, I get results
for the query (they are correct).

Why can't I get any results from my query in Excel if I
get results in a Access query? Am I doing something
wrong???

I'm going to take this out of order, offering a few pointers that *might*
just solve your problems.

Why are you specifying the database path name in your query [Src]? Gawd
that's hard to read! And totally unnecessary. Your connection object
obviously knows it from [DBFullname]. Don't put it in your SELECT query.

The good news is you are doing something very smart by testing your sql
statement this way. After you assemble your string variable [src], I assume
you are doing a Debug.Print src, copying it and pasting it into an Access
query to prove the validity of your query. This is good.

Debug.Print Recordset.MaxRecords ' I get zero here!!!

This is not too surprising. Recordset.MaxRecords is a property you specify
to restrict the maximum number of records to retrieve. The default (0)
simply indicates you want to retrieve *all* records. If you set it to 500,
you are telling it to retrieve only the first 500 records.

MsgBox Recordset.RecordCount 'get zero here too!

Also not surprising. With the default CursorType [adOpenDynamic], you would
first need to perform a .MoveLast before you get a proper count. No matter!
I suggest you deliberately set your CursorType to [adOpenForwardOnly]. This
is the fastest performing recordset when you need to make only a single pass
through the data. AND - you will still need to .MoveLast before you get a
correct value.

Recordset.Open Source:=Src, Source:=ActiveConnection,
CursorType:=adOpenForwardOnly

Why can't I get any results from my query in Excel if I
get results in a Access query? Am I doing something
wrong???

As a first guess - no. You probably *did* get results in your recordset.
You seem to be relying on that .RecordCount before you have moved to the end
of recordset to tell you data is there. Your recordset may well contain the
data you seek. Use .MoveLast
OR - better
Why don't you just print the data under those nice field headers you printed
with your "For" loop?

' Print the recordset data on the row beneath the headers.
Range("P12").cells(2,1).CopyFromRecordset Recordset

This should blast it all onto your spreadsheet very nicely. THEN you can
test RecordSet.RecordCount and get the results you expected.

AND - major quibble:

Please, please - don't declare your ADODB.Recordset object with the name
"Recordset". I'm surprised it works. Use something like "rs" or
"rsPlantProducts" or the even dreadful and uninformative "MyRecordset" would
be an improvement over giving an object the same name as its TYPE. Does VBA
actually permit that? Someone should be shot!

Minor quibble:

You don't need to qualify each field with the table (stored query) name when
there is no chance of ambiguity - and there isn't in your example. Your
FROM tells it the single table (stored query) to work with - no need to
qualify each field name in SELECT. Ditto for your WHERE clause. You only
*need* to do this when you are SELECTing from 2 or more different tables.

Hope this helps!
Thomas Bartkus

 

[Tom Ogilvy]

Range("P12").cells(2,1).CopyFromRecordset Recordset

In excel 97, copyfromrecordset only supports DAO recordsets; not ADO.

--
Regards,
Tom Ogilvy

OK..maybe I am asking the wrong question latelty or not
explaining myself quite right.

How do you gurus get the records from a Access database
put into Excel? I know that you can do it through
automation, but here is my situation.

I have a Excel spreadsheet where users have a dropdown
list and they can select a plant. In our master Access
database, we have product lines associated to a plant in a
query called qryPlantProductLine. When the user selects a
Plant, it should call the query and display the results in
a sheet which the following code runs:

***********************************************

Sub PlantProductLines()
Dim DBFullName As String
Dim Cnct As String, Src As String
Dim Connection As ADODB.Connection
Dim Recordset As ADODB.Recordset
Dim Col As Integer
Dim Row As Integer

Dim strPlantName As String

'Database Information
DBFullName = "J:\QA\QAMaster\QAMaster.mdb"

'Open the connection
Set Connection = New ADODB.Connection
Cnct = "Provider=Microsoft.Jet.OLEDB.4.0; "
Cnct = Cnct & "Data Source=" & DBFullName & ";"
Connection.Open ConnectionString:=Cnct

'Create RecordSet
strPlantName = ActiveSheet.Range("L4").Value '
Set Recordset = New ADODB.Recordset
With Recordset
'Filter
Src = "SELECT qryPlantProductLine.PlantName,
qryPlantProductLine.LineCode, qryPlantProductLine.LineName
FROM `J:\QA\QAMaster\QAMaster`.qryPlantProductLine
qryPlantProductLine WHERE
(qryPlantProductLine.PlantName='Leola')" 'for test puropses
.Open Source:=Src, ActiveConnection:=Connection

*******************************************************

Here's where I am having problems (the rest of the code):

********************************************************
Debug.Print Recordset.MaxRecords ' I get zero here!!!

'Write the field names (which come in)
For Col = 0 To Recordset.Fields.Count - 1
Range("P12").Offset(0, Col).Value =
Recordset.Fields(Col).Name
Next
MsgBox Recordset.RecordCount 'get zero here too!
End With

Set Recordset = Nothing
Connection.Close
Set Connection = Nothing
End Sub

***********************************************

When I go to the Debug.Print .MaxRecords and look in the
Immediate Window, I get zero records returned. If I plug
the SQL statement in ANY Access database, I get results
for the query (they are correct).

Why can't I get any results from my query in Excel if I
get results in a Access query? Am I doing something
wrong???

I'm going to take this out of order, offering a few pointers that *might*
just solve your problems.

Why are you specifying the database path name in your query [Src]? Gawd
that's hard to read! And totally unnecessary. Your connection object
obviously knows it from [DBFullname]. Don't put it in your SELECT query.

The good news is you are doing something very smart by testing your sql
statement this way. After you assemble your string variable [src], I assume
you are doing a Debug.Print src, copying it and pasting it into an Access
query to prove the validity of your query. This is good.

Debug.Print Recordset.MaxRecords ' I get zero here!!!

This is not too surprising. Recordset.MaxRecords is a property you specify
to restrict the maximum number of records to retrieve. The default (0)
simply indicates you want to retrieve *all* records. If you set it to 500,
you are telling it to retrieve only the first 500 records.

MsgBox Recordset.RecordCount 'get zero here too!

Also not surprising. With the default CursorType [adOpenDynamic], you would
first need to perform a .MoveLast before you get a proper count. No matter!
I suggest you deliberately set your CursorType to [adOpenForwardOnly]. This
is the fastest performing recordset when you need to make only a single pass
through the data. AND - you will still need to .MoveLast before you get a
correct value.

Recordset.Open Source:=Src, Source:=ActiveConnection,
CursorType:=adOpenForwardOnly

Why can't I get any results from my query in Excel if I
get results in a Access query? Am I doing something
wrong???

As a first guess - no. You probably *did* get results in your recordset.
You seem to be relying on that .RecordCount before you have moved to the end
of recordset to tell you data is there. Your recordset may well contain the
data you seek. Use .MoveLast
OR - better
Why don't you just print the data under those nice field headers you printed
with your "For" loop?

' Print the recordset data on the row beneath the headers.
Range("P12").cells(2,1).CopyFromRecordset Recordset

This should blast it all onto your spreadsheet very nicely. THEN you can
test RecordSet.RecordCount and get the results you expected.

AND - major quibble:

Please, please - don't declare your ADODB.Recordset object with the name
"Recordset". I'm surprised it works. Use something like "rs" or
"rsPlantProducts" or the even dreadful and uninformative "MyRecordset" would
be an improvement over giving an object the same name as its TYPE. Does VBA
actually permit that? Someone should be shot!

Minor quibble:

You don't need to qualify each field with the table (stored query) name when
there is no chance of ambiguity - and there isn't in your example. Your
FROM tells it the single table (stored query) to work with - no need to
qualify each field name in SELECT. Ditto for your WHERE clause. You only
*need* to do this when you are SELECTing from 2 or more different tables.

Hope this helps!
Thomas Bartkus

 

[Jamie Collins]

...

Why are you specifying the database path name in your query
You don't need to qualify each field with the table (stored query) name when
there is no chance of ambiguity

Could have been edited from SQL generated by a wizard e.g. MS Query
writes SQL a bit like this.

With the default CursorType [adOpenDynamic], you would
first need to perform a .MoveLast before you get a proper [row] count.

A few points here. First, Jet does not support dynamic cursors. The
adOpenDynamic cursor type is specified for the Jet optimizer i.e. to
tell it the SQL has been generated on the client (i.e. not on the
server). The resulting cursor type will in fact be either keyset if
using a server-side cursor or static if using a client-side cursor.
Second point: isn't the ADO default cursor type forward-only anyhow?
Finally, AFAIK with ADO you don't need to MoveLast to get an accurate
RecordCount, that was a feature of DAO.

Jamie.

--

 

[Guest]

OK Here's what I got now:

Sub PlantProductLines()
Dim DBFullName As String
Dim Cnct As String, Src As String
Dim Connection As ADODB.Connection
Dim rs As ADODB.Recordset
Dim Col As Integer
Dim Row As Integer

Dim strPlantName As String

'Database Information
DBFullName = "J:\QA\QAMaster\QAMaster.mdb"

'Open the connection
Set Connection = New ADODB.Connection
Cnct = "Provider=Microsoft.Jet.OLEDB.4.0; "
Cnct = Cnct & "Data Source=" & DBFullName & ";"
Connection.Open ConnectionString:=Cnct

'Create RecordSet
'strPlantName = ActiveSheet.Range("L4").Value
strPlantName = Leola
Set rs = New ADODB.Recordset
With rs
'Filter
Src = "SELECT qryPlantProductLine.PlantName,
qryPlantProductLine.LineCode, qryPlantProductLine.LineName
FROM qryPlantProductLine WHERE
(qryPlantProductLine.PlantName = '" & strPlantName & "')"
.Open Source:=Src, ActiveConnection:=Connection,
CursorType:=adOpenForwardOnly
'.MoveLast
'Debug.Print Recordset.MaxRecords
Debug.Print Src
Debug.Print .RecordCount
'Write the field names
For Col = 0 To .Fields.Count - 1
Range("P12").Offset(0, Col).Value = .Fields
(Col).Name
Next
Range("P12").Cells(2, 1).CopyFromRecordset
rs 'bombs 'here with the message class isn't supported by
automation.
MsgBox .RecordCount
End With

Set Recordset = Nothing
Connection.Close
Set Connection = Nothing
End Sub

 

[merjet]

I don't have Office97 anymore and wasn't aware that
CopyRecordset does not work with it. However, if
you instead use the RS2WS code shown at:
http://www.erlandsendata.no/english/index.php?d=envbadacimportado
it expect it will work.

HTH,
Merjet

 

[Tom Ogilvy]

What isn't clear about copyfromrecordset doesn't work with ADO recordsets in
Excel 97?

 

[Guest]

I got it!!!!

Duh! I didn't set the quotes around the strPlantName. I
was using:

strPlantName = Leola instead of strPlantName = "Leola"

Thanks for all your help!!!