Connection to open database

[James Garner]

Hi,

I am just starting out with trying to program Access via VBA and ADO. I am
having some real difficulties with the connection object and would really
appreciate any advice that anyone could offer.

I have a database of stock quotes with the fields: Ticker, Date, Open, High,
Low, Close & Volume. I am trying to write code which will generate a
recordset of all the price records for a given ticker. The module that I am
writing the function sits in the same project which holds the table itself.

What I have come up with is:-

Dim db As New ADODB.Connection

db.Open "Driver={Microsoft Access Driver (*.mdb)};" & _
"Dbq=" & DataFilename & ";" & _
"DefaultDir=" & DataPath & ";"

SQLcode = "SELECT Ticker,Date,Open,High,Low,Close,Volume " & _
"FROM Prices " & _
"WHERE Ticker = '" & Ticker & "' " & _
"ORDER BY Date"

Dim rs As New ADODB.Recordset
rs.CursorType = adOpenDynamic

Set rs = db.Execute(SQLcode, NumRecs)

All this works fine from Excel, but using the same approach in Access itself
causes problems at the db.Open command. I think it is because the database
is already open (the module itself sits in it). So, what I am wondering is:
how do I get my function to generate this recordset from the database that
it is currently a part of?

Any advice much appreciated!

James.

 

[Michel Walsh]

Hi,




Set ADODBRecordset = CurrentProject.Connection.Execute(strSQL)


Note that this recordset is read only, forward only, but the object
CurrentProject.Connection can also be used as argument for the OPEN method
of a ADODB recordset.


Hoping it may help,
Vanderghast, Access MVP

 

[Randy Harris]

If you're already in the project with the data, creating a recordset is
rather easy.

Dim rs as New ADODB.Recordset

SQLcode = "SELECT Ticker,Date,Open,High,Low,Close,Volume " & _
"FROM Prices " & _
"WHERE Ticker = '" & Ticker & "' " & _
"ORDER BY Date"

' this will create the recordset that is not updateable
' add the arguments if you need to update it
rs.Open SQLcode, CurrentProject.Connection

 

[James Garner]

Thanks, Randy & Michel

I still seem to be having difficulties! I have tried the code exactly as
Randy has written it and I get a run-time error message that "Method 'Open'
of object 'Recordset' has failed".

It's difficult to understand when almost identical code works fine in Excel!

James.

 

[Randy Harris]

James, start with a very simple command:

Dim rs as New ADODB.Recordset
rs.Open "SELECT * From Prices", CurrentProject.Connection
rs.Close
Exit Sub

If it will create that recordset then you can look further into your SQL
string. If not, then you have problems elsewhere. Perhaps with references,
such as a reference both to DAO and ADO.

Randy