Access 2007 gettting VBA into Access Queries

  • Thread starter Thread starter JCIrish
  • Start date Start date
J

JCIrish

Here is this beginner again. I know how to write VBA code (usually in Excel)
but I can't get over the first hump of trying to access the database in order
to write the code for a select query. Here is what I tried (one of several
variations)

Sub TrySetUp()

Dim cnTryVBA As ADODB.Connection
Set cnTryVBA = CurrentProjet.Connection

Dim rsmyRecordSet As New ADODB.Recordset
Set rsmyRecordSet.ActiveConnection = cnTryVBA

rsmyRecordSet.ActiveConnection = cnTryVBA

rsmyRecordSet.Open "SELECT FROM [Copy Of Portfolio Holdings and Value]"

' VBA Code to select and order data goes here
End Sub

"Copy Of Portfolio Holdings and Value" is the table that contains the data

I don't know what is wrong with this. To make matters worse, I'm not even
sure where to put this code, even if it were correct. As it is, the code
generates a syntax error "Expected,Delete,select,etc."

I did the reference thing in the tools menu. I must be missing something
very basic at the outset of this process. Getting VBA code into a Form seems
to be easier, but I'm lost on the process for making a query. All help will
be greatly appreciated.
 
On Tue, 15 Apr 2008 18:56:01 -0700, JCIrish

Not sure where to begin. I guess you have to be able to walk before
you can run.

The immediate problem is that your SELECT query is incorrect; it
should be:
SELECT * FROM [Copy Of Portfolio Holdings and Value]

Additionally it is typically easier to use DAO rather than ADO.
Also that table name is totally unacceptable.
Furthermore you should find some online samples of code that do
similar things to yours (such as
http://support.microsoft.com/kb/168336), and learn from it. Or a book
teaching the first steps (sorry - walking metaphor again)

-Tom.
 
Back
Top