Newbie: Dim for database 
Author Message
 Newbie: Dim for database

My years of delphi programming do not seem to help as I am trying to
learn Access VBA :-)!

I need to run through all of the records in a table and I believe that I
need a variable, dbs.  However, the following is not accepted:

Dim dbs Database

What should I use?  Or am I off on the wrong foot with the skeleton below?

Also, how do I reference the field, "FullName"?

Many thanks for your patience (the book, "Access 2000 VBA Handbook",
does not seem to have examples of what I want to do.

Todd

Function ProcessNames()

Dim rst As Recordset
Dim nameList As Recordset
Dim cQuery As String
Dim dbs ????????

Set dbs = CurrentDb
cQuery = "select * from AddrList "
Set nameList = dbs.OpenRecordset(cQuery)
DoCmd.SetWarnings True

Do While Not nameList.EOF
  MsgBox nameList.Fields("FullName")
Loop
MsgBox("Finished");
rst.Close
nameList.Close

End Function



Wed, 13 Jul 2005 07:25:56 GMT  
 Newbie: Dim for database
A2K or AXP?

You need to add the Microsoft Data Access Object (DAO) 3.6 Library into the
References Collection of your database since Database is an object of DAO.
In the VBE (code) window, use the Menu Tools / References ... to get the
References Dialog.

While you are in this, remove ActiveX Data Object (ADO) Library from the
Refernces if you are not using it (if you don't know it, you are not using
it) since ADO and DAO have objects of the same class name but not
compatible, most notably Recordset.

If you don't remove ADO, you need to give the DAO Library higher priority
than ADO so that Access VBA defaults your Recordset declaration to DAO
rather than ADO.  Alternatively, you can declare with full reference like:

    Dim rs As DAO.Recordset

For safe coding, I always renove the ADO References AND declare with full
reference.

HTH
Van T. Dinh
MVP (Access)


Quote:
> My years of Delphi programming do not seem to help as I am trying to
> learn Access VBA :-)!

> I need to run through all of the records in a table and I believe that I
> need a variable, dbs.  However, the following is not accepted:

> Dim dbs Database

> What should I use?  Or am I off on the wrong foot with the skeleton below?

> Also, how do I reference the field, "FullName"?

> Many thanks for your patience (the book, "Access 2000 VBA Handbook",
> does not seem to have examples of what I want to do.

> Todd

> Function ProcessNames()

> Dim rst As Recordset
> Dim nameList As Recordset
> Dim cQuery As String
> Dim dbs ????????

> Set dbs = CurrentDb
> cQuery = "select * from AddrList "
> Set nameList = dbs.OpenRecordset(cQuery)
> DoCmd.SetWarnings True

> Do While Not nameList.EOF
>   MsgBox nameList.Fields("FullName")
> Loop
> MsgBox("Finished");
> rst.Close
> nameList.Close

> End Function



Wed, 13 Jul 2005 08:00:11 GMT  
 Newbie: Dim for database
nameList.Fields("FullName") that you used works, as does nameList!FullName.
If your field was called Full Name (not recommended) then you could use
nameList![Full Name].

HTH
Paul Daniel



Wed, 13 Jul 2005 20:06:14 GMT  
 Newbie: Dim for database
Paul -

Many thanks!  It is these little items that make a BIG difference for
someone who is "speaks a different language"!  And speaking of field
names, my client is receiving daily data from a firm specializing in
voice recognition (they process 800 number calls) and not only are they
putting spaces into just about all of their field names, they put a " '
" in one of them.  I am sure you can imagine the problems that created
for me!  Especially with a 2.3 million record DB!!

Many thanks.........

Todd

Quote:

>nameList.Fields("FullName") that you used works, as does nameList!FullName.
>If your field was called Full Name (not recommended) then you could use
>nameList![Full Name].

>HTH
>Paul Daniel



Wed, 13 Jul 2005 22:18:38 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. newbie: Dim As Database?

2. newbie: Dim As Database?

3. Newbie Question: Dim A() as String /Dim A as String()

4. newbie, error in Dim statement

5. dim an array--newbie

6. Newbie: ASP dimming array problems

7. VB6 Professional Edition, can't DIM a ADODB connection (Newbie)

8. dim string to dim form

9. Dim obj As New Class crt Dim Obj As Class = New Class

10. dynamically dim a variable ie dim "variable"&n

11. dim myObject as Object versus dim myObject as myProject.class1

12. dim myObject as Object versus dim myObject as myProject.class1

 

 
Powered by phpBB® Forum Software