I am bringing information from an Access recordset into Excel. I want
to
start copying the recordset data starting with column two in the
Access
record. Column one is my primary key identifier and I don't want
that.
I'm thinking it has something to do with .Fields???

Below is my coding so far...

'Copy the matched recordset into the worksheet starting at column
"AN"
With wsSheet1
..Cells(2, 40).CopyFromRecordset rs2.Fields(2,?????) <----unsure here
End With
'Close connections with permit_life
rs2.Close

Any help would be greatly appreciated.

YOU have to change your SQL statement. The SQL has two parts that you need
to look at.

1) The Select portion which defines which records to retrieve. which defines
the table to use and the filters which is specified by the Where statements.
2) The Orderby which specifies which fields to retrieve and the order to
place the fields into the excel table.

"gab1972" wrote:

> I am bringing information from an Access recordset into Excel. I want
> to
> start copying the recordset data starting with column two in the
> Access
> record. Column one is my primary key identifier and I don't want
> that.
> I'm thinking it has something to do with .Fields???
>
> Below is my coding so far...
>
> 'Copy the matched recordset into the worksheet starting at column
> "AN"
> With wsSheet1
> ..Cells(2, 40).CopyFromRecordset rs2.Fields(2,?????) <----unsure here
> End With
> 'Close connections with permit_life
> rs2.Close
>
> Any help would be greatly appreciated.
>

On Jul 19, 10:39*pm, Joel <J...@discussions.microsoft.com> wrote:
> YOU have to change your SQL statement. *The SQL has two parts that you need
> to look at.
>
> 1) The Select portion which defines which records to retrieve. which defines
> the table to use and the filters which is specified by the Where statements.
> 2) The Orderby which specifies which fields to retrieve and the order to
> place the fields into the excel table.
>
>
>
> "gab1972" wrote:
> > I am bringing information from an Access recordset into Excel. *I want
> > to
> > start copying the recordset data starting with column two in the
> > Access
> > record. *Column one is my primary key identifier and I don't want
> > that.
> > I'm thinking it has something to do with .Fields???
>
> > Below is my coding so far...
>
> > 'Copy the matched recordset into the worksheet starting at column
> > "AN"
> > With wsSheet1
> > ..Cells(2, 40).CopyFromRecordset rs2.Fields(2,?????) *<----unsure here
> > End With
> > 'Close connections with permit_life
> > rs2.Close
>
> > Any help would be greatly appreciated.

So you're saying I need to change something in these lines of coding?

' connect to the Access database
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=Z:\COMMON FILES\Encroachment Permits
\Permit.Tracker\Database\Permit.Tracker.mdb;"
' open a recordset
Set rs = New ADODB.Recordset
str = "SELECT * FROM permit_info WHERE permit_num = '" & strPnum &
"'"
rs.Open str, cn, adOpenKeyset, adLockOptimistic

permit_info is the name of the database. strPnum is a number stored
from a userform drop down menu. I have several databases and I use
the strPnum value as the primary key in each one. I want Excel to
pull the information from the recordset, but I want it to start at the
second column, not the first, because I don't need the permit number
column brought over.

Thanks again

What I often do to get the syntac correct is to turn on a macro recorder in
excel. Then using the data menu Import External Data - New Database Query
set up the query to the access database. The dialog menus will allow you to
choose you options and filters. Select the collumns yo want to import.
When you get done stop recording. The Command Text portion of the query is
the SQL. You can then take the SQL and put into you macro.

Your present code is retrieveing the entire table. You need to specify the
columns and giv e each column an alias in the select portion of the SQL. The
Orderby will use the alias column names to retrieve the columns you want
skipingt the 1st column.

The recorded macro will get you started.


"Joel" wrote:

> YOU have to change your SQL statement. The SQL has two parts that you need
> to look at.
>
> 1) The Select portion which defines which records to retrieve. which defines
> the table to use and the filters which is specified by the Where statements.
> 2) The Orderby which specifies which fields to retrieve and the order to
> place the fields into the excel table.
>
> "gab1972" wrote:
>
> > I am bringing information from an Access recordset into Excel. I want
> > to
> > start copying the recordset data starting with column two in the
> > Access
> > record. Column one is my primary key identifier and I don't want
> > that.
> > I'm thinking it has something to do with .Fields???
> >
> > Below is my coding so far...
> >
> > 'Copy the matched recordset into the worksheet starting at column
> > "AN"
> > With wsSheet1
> > ..Cells(2, 40).CopyFromRecordset rs2.Fields(2,?????) <----unsure here
> > End With
> > 'Close connections with permit_life
> > rs2.Close
> >
> > Any help would be greatly appreciated.
> >

gab1972,

If you want to keep your SQL as it stands, maybe something like below
would help. I'm not using the CopyFromRecordset, however, but just
looping it through:

With rs
Do While Not .EOF
For n = 1 To .Fields.Count - 1' n=1 and .fields.count-1
because the .fields array is zero-based array.
Debug.Print .Fields(n).Name'You wouldn't need this as
it's for ilustration purposses only.
Debug.Print .Fields(n).Value
Next n
.MoveNext
Loop
End With


Instead of these
Debug.Print .Fields(n).Name
Debug.Print .Fields(n).Value
you can have your logic like:

wsSheet1.Cells(2, 40).value = rs.Fields(n).Value
and then loop to next column in your sheet and then next row when the
rs.movenext happens.




On 20 July, 04:40, Joel <J...@discussions.microsoft.com> wrote:
> What I often do to get the syntac correct is to turn on a macro recorder in
> excel. *Then using the data menu Import External Data - New Database Query
> set up the query to the access database. *The dialog menus will allow you to
> choose you *options and filters. *Select the collumns yo want to import. *
> When you get done stop recording. *The Command Text portion of the query is
> the SQL. *You can then take the SQL and put into you macro.
>
> Your present code is retrieveing the entire table. *You need to specifythe
> columns and giv e each column an alias in the select portion of the SQL. *The
> Orderby will use the alias column names to retrieve the columns you want
> skipingt the 1st column.
>
> The recorded macro will get you started.
>
>
>
> "Joel" wrote:
> > YOU have to change your SQL statement. *The SQL has two parts that you need
> > to look at.
>
> > 1) The Select portion which defines which records to retrieve. which defines
> > the table to use and the filters which is specified by the Where statements.
> > 2) The Orderby which specifies which fields to retrieve and the order to
> > place the fields into the excel table.
>
> > "gab1972" wrote:
>
> > > I am bringing information from an Access recordset into Excel. *I want
> > > to
> > > start copying the recordset data starting with column two in the
> > > Access
> > > record. *Column one is my primary key identifier and I don't want
> > > that.
> > > I'm thinking it has something to do with .Fields???
>
> > > Below is my coding so far...
>
> > > 'Copy the matched recordset into the worksheet starting at column
> > > "AN"
> > > With wsSheet1
> > > ..Cells(2, 40).CopyFromRecordset rs2.Fields(2,?????) *<----unsure here
> > > End With
> > > 'Close connections with permit_life
> > > rs2.Close
>
> > > Any help would be greatly appreciated.- Hide quoted text -
>
> - Show quoted text -

Having looked at your previous posts a couple of days back I realize I
should have mentioned the zero-based array of .fields property - that
might have caused you the trouble.

The zero-based means that the FIRST field in the Access Recordset
doesn't have index=1 but = 0, therfore in my code below
For n = 1 To .Fields.Count - 1
the n starts at 1 (while it's actually column #2) and ends at
=.Fields.Count - 1 (and not .Fields.Count).

Hopefully this time i rectified the issue.

On 20 July, 10:37, AB <austris.bahanovs...@gmail.com> wrote:
> gab1972,
>
> If you want to keep your SQL as it stands, maybe something like below
> would help. I'm not using the CopyFromRecordset, however, but just
> looping it through:
>
> * * With rs
> * * * * Do While Not .EOF
> * * * * * * For n = 1 To .Fields.Count - 1' n=1 and .fields.count-1
> because the .fields array is zero-based array.
> * * * * * * * * Debug.Print .Fields(n).Name'You wouldn't need this as
> it's for ilustration purposses only.
> * * * * * * * * Debug.Print .Fields(n).Value
> * * * * * * Next n
> * * * * * * .MoveNext
> * * * * Loop
> * * End With
>
> Instead of these
> * * * * * * * * Debug.Print .Fields(n).Name
> * * * * * * * * Debug.Print .Fields(n).Value
> you can have your logic like:
>
> wsSheet1.Cells(2, 40).value = rs.Fields(n).Value
> and then loop to next column in your sheet and then next row when the
> rs.movenext happens.
>
> On 20 July, 04:40, Joel <J...@discussions.microsoft.com> wrote:
>
>
>
> > What I often do to get the syntac correct is to turn on a macro recorder in
> > excel. *Then using the data menu Import External Data - New Database Query
> > set up the query to the access database. *The dialog menus will allowyou to
> > choose you *options and filters. *Select the collumns yo want to import. *
> > When you get done stop recording. *The Command Text portion of the query is
> > the SQL. *You can then take the SQL and put into you macro.
>
> > Your present code is retrieveing the entire table. *You need to specify the
> > columns and giv e each column an alias in the select portion of the SQL.. *The
> > Orderby will use the alias column names to retrieve the columns you want
> > skipingt the 1st column.
>
> > The recorded macro will get you started.
>
> > "Joel" wrote:
> > > YOU have to change your SQL statement. *The SQL has two parts that you need
> > > to look at.
>
> > > 1) The Select portion which defines which records to retrieve. which defines
> > > the table to use and the filters which is specified by the Where statements.
> > > 2) The Orderby which specifies which fields to retrieve and the orderto
> > > place the fields into the excel table.
>
> > > "gab1972" wrote:
>
> > > > I am bringing information from an Access recordset into Excel. *Iwant
> > > > to
> > > > start copying the recordset data starting with column two in the
> > > > Access
> > > > record. *Column one is my primary key identifier and I don't want
> > > > that.
> > > > I'm thinking it has something to do with .Fields???
>
> > > > Below is my coding so far...
>
> > > > 'Copy the matched recordset into the worksheet starting at column
> > > > "AN"
> > > > With wsSheet1
> > > > ..Cells(2, 40).CopyFromRecordset rs2.Fields(2,?????) *<----unsurehere
> > > > End With
> > > > 'Close connections with permit_life
> > > > rs2.Close
>
> > > > Any help would be greatly appreciated.- Hide quoted text -
>
> > - Show quoted text -- Hide quoted text -
>
> - Show quoted text -