Read MS Access Table Contents Into Variables (2 Viewers)

[lhooker]

Can someone provide me an example on VBA code that will allow me to read an existing table in MS Access (with 2 columns) and store the first column contents into variables ?

 

[Pat Hartman]

You have made a very specific request but there is no context and so there is no answer. For starters variables don't store anything. Tables store data. Are you talking about variables defined in a VBA procedure? Are you talking about controls on a form or a report? What is the procedure going to do with the variables? Which record from the table are you trying to get data from? Are you only interested in a single record?

 

[cheekybuddha]

Dim recs As Variant, lRecCount As Long, lRow As Long

With CurrentDb.OpenRecordset("SELECT FirstColumnName FROM yourTable;")
  recs = .GetRows
  .Close
End With

lRecCount = UBound(recs, 2) + 1

For lRow = 0 To lRecCount - 1
  Debug.Print recs(0, lRow)
Next lRow

Reference: DAO.Recordset.GetRows

 

[Pat Hartman]

So, you've posted code that loads one array into a different array in answer to this nebulous question. Not sure what purpose substituting one array for a different array serves. I guess you read something into the question that never even crossed my mind.

 

[cheekybuddha]

You can access an array differently from a recordset.

No need to call .MoveNext methods to access each record etc ....

Sure, more of a picture of what the final purpose is would enable a perhaps better solution, but this solves the question with the limited info we have

 

[lhooker]

Dim recs As Variant, lRecCount As Long, lRow As Long

With CurrentDb.OpenRecordset("SELECT FirstColumnName FROM yourTable;")
  recs = .GetRows
  .Close
End With

lRecCount = UBound(recs, 2) + 1

For lRow = 0 To lRecCount - 1
  Debug.Print recs(0, lRow)
Next lRow

Reference: DAO.Recordset.GetRows

Thank you ! ! ! . . . but "lRecCount" returns zero records

 

[lhooker]

You have made a very specific request but there is no context and so there is no answer. For starters variables don't store anything. Tables store data. Are you talking about variables defined in a VBA procedure? Are you talking about controls on a form or a report? What is the procedure going to do with the variables? Which record from the table are you trying to get data from? Are you only interested in a single record?

No, I want all contents from the first column saved into variables.

 

[lhooker]

You can access an array differently from a recordset.

No need to call .MoveNext methods to access each record etc ....

Sure, more of a picture of what the final purpose is would enable a perhaps better solution, but this solves the question with the limited info we have

I want to retrieve values in one table (column 1) and write the content into another table (horizontally). I currently do not have any workable code.

 

[cheekybuddha]

Thank you ! ! ! . . . but "lRecCount" returns zero records

Oops! I forgot that GetRows() is a bit different for DAO recordsets compared with ADODB recordsets.

You ought to be able to use:

Dim recs As Variant, lRecCount As Long, lRow As Long

With CurrentDb.OpenRecordset("SELECT FirstColumnName FROM yourTable;")
  .MoveLast
  .MoveFirst
  recs = .GetRows(.RecordCount)
  .Close
End With

lRecCount = UBound(recs, 2) + 1

For lRow = 0 To lRecCount - 1
  Debug.Print recs(0, lRow)
Next lRow

But, as Pat points out, this is most likely an unnecessary operation.

I want to retrieve values in one table (column 1) and write the content into another table (horizontally).

This is quite unusual - why do you want to do this?

There may be easier or alternative solutions (eg Crosstab query perhaps)

 

[The_Doc_Man]

@lhooker - there are considerations here that need to examined. First and foremost, how many records are we talking about?

There is a subtle effect here that needs to be considered. When you store something in an array variable, it takes up space in the virtual memory of the Windows process (also called a task). BUT for Access, that data came from a table that was already in virtual memory as part of the data portion of the DB. In a split DB, the table is in the high part of memory. In a non-split database, your guess is as good as mine as to where it goes. The arrays (which would HAVE to be created dynamically) would be in the low half of your memory space, in the front-end area and occupying space shared with the stack and the program heap plus program libraries and add-ins. As it happens, you also have a 2 GB limit on that part of your 32-bit process memory. Unlike BE files that allow you to split and remap tables to a second BE to get more space, the FE is single-file, everything fits or NOTHING fits. I have NO CLUE as to where things go when you have the 64-bit version, but that version is NOT the panacea to storage problems. MS didn't fully expand the virtual space. You COULD get some breathing room from something called "Large Address Aware" which involves a registry setting. You can look that up.

If you are doing this on a table with a few dozen up to a couple of hundred records, it should fit OK even on 32-bit Office. When you start to get into thousands of records (or more), you start running into the problem that you have MSACCESS.EXE in the same space as the vital structures called the HEAP and the STACK, plus all of the libraries (.DLL files) that you named in the VBA >> Tools >> References list.

You have an "instant bloat" problem as well. You can dynamically grow and shrink an array, but you have remember that it will have a deleterious effect on the internal layout of virtual memory. Therefore, if you are going to do a ReDim, it would pay in terms of stability to do your best to do a single ReDim to the correct size immediately. It would be possible to ReDim an array to a size that cannot be accommodated in the FE due to the other things that are being shared, and you have no other place to put them.

Above, I expressed concern for the HEAP and STACK structures. Since Access has already been compiled and link-edited into an executable image, you can't use the linker /HEAP:reserve,commit option to assure that you have enough HEAP space. (It's too late to change it.) The same is true for the /STACK:reserve,commit option. Again, it is too late. By linker default, both of those critical items are 1 MB in size. It is possible for both HEAP and STACK to expand dynamically (by default, in 4Kb chunks) but once you reach the actual limit, you will face... the Terminator. He'll be back - but you won't.

More specifically: A static array allocation in a SUB/FUNCTION declaration will go on the STACK. A dynamic array allocation such as a ReDim will go on the HEAP. If you ever get the dreaded "STACK overflows HEAP" message or its brother, "HEAP overflows STACK", you are done. Which one you get depends on what you were about to allocate. The (32-bit) stack starts from (I think) 0h7FFFFFFF and works down towards lower addresses. The problem is that the HEAP starts at the first place above the addresses of all that other crud I mentioned in the FE portion but that starting point is not a fixed location. The HEAP grows upwards from its base which is below the STACK. When the HEAP top-pointer and the STACK bottom-pointer pass each other, you are done.

For that complex reason explained above, I would suggest that putting something into variables might not be your best option. I sincerely doubt you would get anywhere NEAR 2 GB of virtual space in the FE file, which is where this has to go.

EDIT for technical accuracy: In a monolithic i.e. unsplit DB, the stack might start at virtual adddress 0hFFFFFFFF. It is whatever is the highest address that can be used in the task's memory range holding MSACCESS.EXE and the library files. In a non-split DB, that is the top of the 4 GB address space. In a split DB, it is the top of the low 2 GB address space.

 

[GPGeorge]

The good news is that "Large Address Aware" has been implemented for 32 bit Access in current versions.

Microsoft 365 Roadmap | Microsoft 365

The Microsoft 365 Roadmap lists updates that are currently planned for applicable subscribers. Check here for more information on the status of new features and updates.

www.microsoft.com

 

[CJ_London]

I want to retrieve values in one table (column 1) and write the content into another table (horizontally)

Any reason for not doing this in sql using an xtab as your source?