Newbie Question 1

[iamchemist]

OK, so I have abandon my previous attempts to modify an existing database and have bought the book, Absolute Beginner's Guide to VBA", by Paul McFedries to start from scratch. For the Access VBA part of this book it starts out telling me how to program via an ADO Object. However, I can not get the simplest code to run:

Sub RecordsetOpen()

Dim rs As Recordset
rs.Open "Employees", "Northwind"
rs.Close
Set rs = Nothing

When I try to run this code from within the Northwind example database (reference has been set to allow ADO), it chokes at the statement:

rs.Open "Employees", "Northwind"

Would someone please tell me how to get this very, very simple code snipet to run, or what I am doing wrong in trying to get it to run?

Thanks,

Ron

 

[boblarson]

You would use:

Sub RecordsetOpen()

Dim rs As ADODB.Recordset
Set rs As New ADODDB.Recordset
rs.Open "Employees", CurrentProject.Connection
rs.Close
Set rs = Nothing

Bob Larson
Free Access Tutorials and Samples:

http://www.btabdevelopment.com

 

[boblarson]

Oops, got an extra D in there:
should be:

Sub RecordsetOpen()

Dim rs As ADODB.Recordset
Set rs As New ADODB.Recordset
rs.Open "Employees", CurrentProject.Connection
rs.Close
Set rs = Nothing

Bob Larson
Free Access Tutorials and Samples:

http://www.btabdevelopment.com

 

[iamchemist]

Hi Bob,

I appreciate your reply, but I'm afraid I didn't ask my question very well. The code should have said:

Sub RecordsetOpen()

Dim rs As Recordset
Set rs = CreateObject("ADODB.Recordset")
rs.Open "Employees", "Northwind"
rs.Close
Set rs = Nothing

The code still chokes at the:

rs.open "Employees", "Northwind"

Does getting the code right change your answer any?

Sorry for the confusion.

Ron

 

[boblarson]

No confusion - Use the code I gave you. Unless you don't have a reference set to ADO, and in that case it would be:

Sub RecordsetOpen()
Dim rs As OBJECT

Set rs = CreateObject("ADODB.Recordset")

rs.Open "Employees", CurrentProject.Connection

rs.Close

Set rs = Nothing

You still need to use CurrentProject.Connection EXACTLY as shown, not "Northwind." And you either deckare the recordset as

Dim rs As ADODB.Recordset

or

Dim rs As Object

NOT as Dim rs As Recordset.

Bob Larson
Free Access Tutorials and Samples:

http://www.btabdevelopment.com

 

[iamchemist]

Hi Bob,

The code that you suggested worked fine. Interesting that it took me a couple of days and a lot of help from you to get code to run, which essentially does nothing.

That line of code:

Dim rs As Recordset

Is verbatim out of the book "Absolute Beginner's Guide to VBA". It would seem that I need to get another VBA book!

Thanks again for your help. I at least now have a working stub to start from.

Ron

 

[boblarson]

Well, some people tended to just use As Recordset because they had EITHER ADO or DAO references and not BOTH, which is totally possible. And, with the way that the default references have changed over the years it is best to just be explicit.

97 - DAO default
2000 - ADO default
2002 - ADO default
2003 - ADO AND DAO default
2007 - DAO (ACE) default

Bob Larson
Free Access Tutorials and Samples:

http://www.btabdevelopment.com

 

[iamchemist]

The thing that would probably help a Newbie like me the most in learning Access VBA would be a good VBA Command Reference. Microsoft says they have one, but most command you try to go and look up for a definition, it can't be found.

Is there a good VBA Command Reference someplace for free, or do I have to buy one?

Thanks,

Ron