Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Mar 2004
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Accessing a table cell from VBA (2000)

    Hello folks,

    I need to dump to a file the contents of any given cell from VBA. The cell is defined by the following coordinates: database name, table name, column name, row number. In particular, the cell can be of "long binary data" type. Sorry if the question is too trivial, but please understand that I am very new to Access programming.

    After reading the (awful) help, I was able to come up with something like this:

    <pre>Function GetCell(TblName As String, ColName As String)
    GetCell = DLookup("[" & ColName & "]", TblName, "[ID] = 0")
    End Function
    Sub TestGetCell()
    v = GetCell("MSysAccessObjects", "Data")
    Open "C:WorkDUMP" For Binary Access Write As #1
    Put #1, , v
    Close #1
    End Sub</pre>

    Note, however, that there are several problems with the above code:

    1) It uses the current database - not a database specified by name.

    2) It does not use row number to locate the cell. Instead, it uses a condition (=1) imposed on another column ("ID") of the table.

    3) The output file seems bogus. In particular, it seems that something has stripped the highest bit of all bytes.

    Any ideas how to fix these problems? Thanks in advance.

    Regards,
    Vesselin

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Accessing a table cell from VBA (2000)

    Hello Vesselin,

    There are ways to retrieve data from another database, but there is a fundamental problem with your question: the concept of a "row number" is alien to Access. The records in a table are not stored in any particular order. You must use a unique key (such as the ID field in your example) to specify the record to retrieve.

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Accessing a table cell from VBA (2000)

    As Hans pointed out, you're making some unwarranted assumptions about Access tables. In Access, there is no concept of a "cell". There *are* rows and columns, but they lack the inherent relationship you find in an Excel spreadsheet. The order of columns is arbitrary and can be changed in the table design or through a query without making any difference, since a "column" can oly be referenced by name or index (ordinal) number in the table's columns collection. Rows have no inherent relationship with one another. Each is a distinct record and can only be uniquely identified by its primary or a unique key. You can resort the rows on any given column, and you would use the same forumla to return a specific value from a specific record. There is no equivalent to VLookup or LLookup in Access, but if you explain what you're trying to accomplish with this, someone will be able to suggest an alternative.
    Charlotte

  4. #4
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Accessing a table cell from VBA (2000)

    Hans and Charlotte have already covered the issues with your request -- one more thing that may be helpful towards getting to a solution to your problem is that to uniquely identify a "cell" in an Access table, you need a field name (the column - analogous to the alphabetic column index in Excel) and a unique record identifier as Hans mentioned (the value of a "key" field for the row/record of interest - analogous to the numeric row index in Excel, except that it's a field value). If you have these two values then you can use Access's DLookup function to extract the value of interest.

    If the table does not have a "key" field with no duplicate values across all records, then you'd have to resort to some sort of "FindFirst" search which may not locate the precise cell you're looking for.

    Hope this helps.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •