Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy an Access recordset starting with Column 2
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy an Access recordset starting with Column 2
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy an Access recordset starting with Column 2
On Jul 19, 10:39*pm, 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. 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
SQL
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
SQL
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 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 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.- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
SQL
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 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 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 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.- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Start copying an access recordset starting with the next column | Excel Programming | |||
Code for getting Access recordset into Excel | Excel Programming | |||
Access Recordset Rows to Excel | Excel Programming | |||
Copy recordset from an Access "make table" query | Excel Programming | |||
Access Recordset with Built-In Function | Excel Programming |