Navigating Recordsets w/out specifying name

Tethys · December 2nd, 2005, 06:59 PM

Hey folks. Quick question, hopefully requiring a quick answer:

Below is an example of code I'm using to populate a table (tblEntry) and its fields User_ID and User_Code. What I want to be able to do is instead of specifying the fields User_ID and User_Code I'd like instead to navigate from one field to the next on every row I update, using a loop and moving from column 1, 2, 3 ... 52, etc., for each row.
I will comment in where I tried to do this and failed. If you have an idea on how to go about accomplishing this please post. Thank you in advance.

Code:

Private Sub btnFileLoad_Click()
    Dim cnDB As DAO.Database
    Dim rst As DAO.Recordset

    Dim xlsAPP As Excel.Application
    Dim xlsWBK As Excel.Workbook
    Dim xlsWST As Excel.Worksheet

    Dim cnt As Long
    Dim posC As Integer
    Dim x as Integer

    Dim strCMD As String

    Set xlsAPP = New Excel.Application
    xlsAPP.Workbooks.Open ("C:\MySpreadsheet\test.xls")

    Set xlsWBK = xlsAPP.ActiveWorkbook
    Set xlsWST = xlsWBK.Sheets("Worksheet_Entry")
    xlsWST.Activate

    Set cnDB = CurrentDb
    Set rst = cnDB.OpenRecordset("tblEntry", dbOpenDynaset)
    xlsWST.Range("A2").Select

    cnt = 0
    Do While cnt < 2
        rst.AddNew
        rst!User_ID = Nz(xlsAPP.ActiveCell, 0)        
        rst!User_Code = & _
                   Nz(xlsAPP.ActiveCell.Offset(0, 1), 0)
        ''I attempted to iterate through columns here
        ''by deleting the previous 2 lines of rst!... and
        ''replacing them with what you see commented here.
        'rst.AddNew
        'For posC = 0 To rst.Fields.Count
        'rst.AbsolutePosition(x) = & _
                   Nz(xlsAPP.ActiveCell.Offset(0, posC), 0)
        'rst.Update
        'x = x + 1
        'Next
        rst.Update
        xlsAPP.ActiveCell.Offset(1, 0).Select
        If IsEmpty(xlsAPP.ActiveCell) Then cnt = cnt + 1
    Loop

    xlsAPP.Quit
    rst.Close
    Set rst = Nothing

End Sub

mmcdonal · December 5th, 2005, 11:07 AM

I am not sure I understand what you are trying to do or why.

Are you using Access forms for Excel?

mmcdonal

Tethys · December 5th, 2005, 01:22 PM

I am wanting to take data from a spreadsheet and populate the access db table with its contents. In the future I want the user to have freer reign on what columns are updated in the table, but at this juncture I just want the application to loop through from column 0 to column 52 of a record, storing in each column of a record what is located in the spreadsheet from column 0 to 52.

This is intended to replace the specifying of a particular recordset, such as Column ID in the table...52 times (or 52 lines of code).

Anyhow, if you follow what I am referring to would you have an idea? Thanks.

-Tethys

mmcdonal · December 5th, 2005, 01:37 PM

Questions I still have:

1. Why are you maintaining this data in an Excel spreadsheet? Why not in an Access table, and then export it to the spreadsheet as needed for reporting.

2. Why not transfer the spreadsheet into a temporary table to do your look ups as in DoCmd.TransferSpreadsheet? This is such a small set of data that just bringing it all in and using forms on it is not a burden.

3. I don't understand your statement: "This is intended to replace the specifying of a particular recordset, such as Column ID in the table...52 times (or 52 lines of code)." Why 52 lines of code? "SELECT * FROM tblYourTable WHERE [ColumnID] =" etc brings over one record. Or opening the recordset and then looping through it from MoveFirst to EOF.

I guess I don't understand... "storing in each column of a record what is located in the spreadsheet from column 0 to 52." either. Sorry. I know we can help you with this.

mmcdonal

Tethys · December 5th, 2005, 02:57 PM

1. An excel spreadsheet is by user preference. The data is stored this way - I have no control over its format. However, I am storing the spreadsheet once I receive it into an access DB.

I'll explain again with a couple of visuals =) in hopes that things are less confusing.

a. I have an access DB w/ table "tblEntry"
b. I am given a spreadsheet in .xls format
c. With a button click on a VBA form the contents of the spreadsheet are transferred into tblEntry.

What I currently do is read column for column of each row in the excel table (or each cell per row) and transfer that into its sister cell in the current recordset of tblEntry like so:

Code:

    'Format:
    '<Access Table>     =     <Excel Spreadsheet>
    rst!GIS_Location_System_ID = Nz(xlsAPP.ActiveCell, 0)
    rst!Site_Area_Code = Nz(xlsAPP.ActiveCell.Offset(0, 1), 0)
    rst!Primary_Site_Type = Nz(xlsAPP.ActiveCell.Offset(0, 2), 0)
    'etc...

What I am trying to get around is specifying the exact field name of the table. These will never be reordered. However, since I will not be updating the code in the future I will add the ability to append new fields to the table in the future. But at this time I am only concerned with being able to loop through the list of fields without specifying those field names, similar to:

Code:

    For i = 0 to 51
         rst!i = Nz(xlsAPP.ActiveCell(i), 0)
    Next

Where I got hung up originally was a loop similar to above, but with a .MoveFirst, .MoveNext that was breaking. I didn't have it set up appropriately.

Any clearer?

Thanks.
-Tethys

Tethys · December 5th, 2005, 03:23 PM

mmcdonal,

Thanks for the reference to DoCmd, I am able to replace that lengthy list of code with 1 line. I'm still interested in seeing how to loop through the various fields of a table in access if you happened to know, staying with similar format to what I've posted up above.

Much appreciated.
-Tethys

mmcdonal · December 5th, 2005, 03:28 PM

It depends on what you want to do with the list.

Are you using it to look up a value? Are you using it as a parameter in another function? etc.

mmcdonal

Tethys · December 5th, 2005, 04:47 PM

I would be using the loop to locate a particular position in a recordset, and then assigning it data.

For instance in the access table there might be:

Field1 Field2 Field3
1 somevalue somevalue
2 morevalues morevalues

In the spreadsheet I would have:

Column1 Column2 Column3
12 18 27

And the entire row in the spreadsheet needs added to the table as a new record.

I can do a Fields.Count to determine how many fields are in the table, so this aids in setting up the loop. The assumption will be that the spreadsheet mirrors the table structure - so the .xls file has a similar column count to Fields.Count of the table.

What I don't want to do is have a long list of code similar to

Code:

    rst!Field1 = Nz(xlsAPP.ActiveCell, 0)
    rst!Field2 = Nz(xlsAPP.ActiveCell.Offset(0, 1), 0)
    rst!Field3 = Nz(xlsAPP.ActiveCell.Offset(0, 2), 0)

but instead be able to have a loop that iterates through every field in the table similar to my last post, or:

Code:

    For i = 0 to rst.Fields.Count
         rst!i = Nz(xlsAPP.ActiveCell(i), 0)
    Next

DoCmd works great for now. Perhaps it doesn't present any hangups down the road, either. But I forsee the need to be able to insert via a loop which isn't cooperating atm =).

-Tethys