oreilly.com: Using Grids with VBA

Published on Windows DevCenter (http://www.windowsdevcenter.com/)
See this if you're having trouble printing code examples

Date: Jan 10 1999
From: Meredith Marshall
To: ron@oreilly.com
Subject: Using grids with VBA

Ron,

I'm developing a financial tool in the Office 97/Excel VBA enviroment. I would like to incorporate a grid into the interface that can read/write/edit to a database component (Access or Oracle). I tried the DBGrid (Apex software) without success and then learned that it was not VBA compatible, only VB compatible. Are you aware of any grid objects that can be used in the VBA development environment?

-Meredith

Hello Meredith,

I'm by no means an expert on data access from a hosted VBA environment, nor have I worked extensively with anything other than the controls provided with the MSForms library. So perhaps I should plead ignorance before I embarrass myself. However, that hasn't stopped me before, so I don't know why it should now...

The data grids are bound controls; that is, they require a data source that actually connects to a database and retrieves their data. And it seems to me that the root of the problem with DBGrid is that there's no data source that can be readily included along with the DBGrid control. In Visual Basic, the solution is simply to include the Data Control on the same form as DBGrid. But the Data Control is an intrinsic control; it's unavailable to anything outside of the Visual Basic environment itself. (Of course, there may -- and probably are -- third-party products available; I have no idea.)

Microsoft is now aggressively promoting OLE DB and ActiveX Data Objects, and VB 6.0 has a new set of data controls (DataGrid, DataList, DataCombo, MSHFlexGrid, MSFlexGrid) that once again are bound controls. Unlike DBGrid, though, they support OLE DB, and therefore rely on the an ADO Data Source (and in particular the ActiveX Data Objects Data Control, or ADO DC) for data access. Unlike the Data Control, the ADO DC is a custom control (that is, an .OCX) that can be added to any project. In short, if you add ADO DC to your project along with the DataGrid control, you should be able to use them without problem. At least, I did a few trivial tests, and had no difficulty at all.

By the way, Microsoft supplies OLE DB data providers for both Access and Oracle databases, so OLE DB/ADO should definitely be able to get at your data.

I hope that this helps.

--Ron

Return to: Ron's VB Forum