ms-access: using getrows method

Question

i am new to access and i am debugging someone's database

1. how do i know if i am using ADO?
2. i have stuff in a listbox, how do i use the .GetRows method to put the recordset into a two-dimensional array?

Answer 1

Do you have a reference to the ADO libraries (In a code window, under Tools->References: Microsoft ADO Ext x.x For DLL and Security, Microsoft ActiveX Data Objects x.x Library) Have you set the recordset to an ADO recordset?

You will find details of GetRows here: http://www.w3schools.com/ado/met%5Frs%5Fgetrows.asp

You can use it like GetString, as illustrated in a reply to your previous post.

EDIT

You do not have to have a reference to the ADO libraries to use ADO, but it can be easier. To use ADO without the libraries (late binding), try something like:

Dim cn As Object
Dim rs As Object

Set cn=CreateObject("ADODB.Connection")
Set rs=CreateObject("ADODB.Recordset")

cn.Open strConnect ''Any suitable connection string
rs.Open strSQL, cn ''Any suitable SQL string

Answer 2

As others mentioned, to check if the using ADO, you as a general rate ruled can look in your code. However what's probably confusing you and not has been explained is that it's ONLY in your coding practices do you choose ADO or DAO.

For the rest of the application (ie: BUILT IN OBJECTS) you will use the concept of what's called linked tables. These linked tables handle all of the connection strings and all of the internal workings for you. There's no additional effort required on the user's part when they're developing the application. For all of these internal objects, you don't choose a particular data object type (ADO or DAO).

What this means is that for filling data into forms, data into reports, data into listbox, data into combox boxes etc. you DO NOT write code, and YOU DO NOT use connection strings. And you DO NOT HAVE a choice of ADO or DAO. What this means is that when you look at a combo box or list box or form, you'll get off the confused really fast if you start looking for some type of connection string or asking if that combo box is based on ADO or DAO (hint: you as a genera rule don't have a choice in this matter: all of these object and how they work are handled automatically for you internally inside of a MS access). What this means is all you have to do supply the given object with some SQL, and you're done.

So, all of these objects will as a rule work from your linked tables in access. Once these linked tables are set up, then you build forms, or reports, or list box is based on these linked tables. As a result a as mentioned, you'll not be choosing ( or even have to bother) with DAO or ADO when working with these objects.

The choice of ADO or DAO We'll generally only come into play in your actual code that opens a record set or query directly. Most of the code in a form can be based on referencing the controls or objects such as an combo box that exist on that form. Once again the choice of ADO or DAO it not relevant, and you don't have control over that aspect.

For example, if you wanted to use code to setup the values for a combo box, you would use the following code: (we assume the combo box is a two column combo box, 1st column is id (hidden) and 2nd column company name.

The VBA code to fill or setup the combo box via sql is:

Me.Mycombobox.RowSource = "select id, CompnayName from tblCustomers"

That is it! Notice how there's no connection strings, no record sets, you just shoved some clean SQL right into the combo box row source and away you go.

Now of course the above assumes you have a linked table called "tblCustomers". You can see/view those tables in the tables tab.

( By the way our combo boxes and listbox is allow more than two columns, to hide columns you set the format lengthy equal to zero for that column. That way you can still access the data in your code, but not have that column displayed in the list box).

So the choice of ADO/ADO is often not an issue. Note that even when you do choose ADO or DAO in your code, you as a general rule should use the built in table objects and this again will not have to bother with an connection string built in code. There are some exceptions here, but let's keep this general for the time being.

The above should clear up a lot of your questions about how come you can't see or tell how the form is using DAO or ADO ( the answer is you don't bother with this, and the answers you don't have a choice, and the answer is this is all automatically handled internally for you by MS access)

2.i have stuff in a listbox, how do i use the .GetRows method to put the recordset into a two-dimensional array?

For what reason do you want to do the above? There is VERY little reason to pull data from a listbox into a array. You have the list box ALREADY loaded up with data, so why then waste time loading this data into an array? For what reason would you do this?

You can reference the data in that listbox, but you might want explain what you're trying to accomplish here. There's no need to pull the data out of the listbox, but you can use code in a form to look at value(s) selected in the listbox, and do a useful things with those selections made. Again, to work with the selected data from that listbox, you don't need an array, you don't need connection strings, and you don't need to write a whole bunch of code to repeat what is our data currying and is available inside of that form (hint: the data inside of that list box is available for you as a collection).

So you don't need a record set, you don't need to write a whole bunch of code nor do you need some connection strings. And you don't need nor want to pull that data from that list box into an array, it's simply not required and is completely overkill. Simply stay what you're trying to do with that listbox, and we'll come up with a solution that's likely only a couple of lines of code.

As mentioned, look at that data tab of a listbox, and take a look at the SQL used to fill that listbox. Does that sql run correctly, and furthermore check of the number of columns specified in the listbox matches the number of columns you have in your SQL that set for that list box in the data tab. Funny things can happen if the number of columns don't match the settings for the number of columns in the listbox.