Help connecting to a db

  • Thread starter Thread starter max
  • Start date Start date
M

max

I am trying to write my first VBA app to populate a table
from a subset of another table. However, I keep getting a
"Run-time error '13': Type Mismatch" on the "Set
rstWeeks...." line below. (Marked with a "--->")

This is all the code that is written so far. The
db.openrecordset seems to be working...b/c if I remove the
"set rstweeks =", code runs fine. From looking at other
samples...it seems that this is pretty
straightforward...but am I missing something here? If
rstWeeks is a recordset and db.openrecordset returns a
recordset, then what is the problem?

Any help?

Public Sub cmdRunIteration_Click()

Dim db As Database
Set db = CurrentDb

Dim rstWeeks As Recordset
--->Set rstWeeks = db.OpenRecordset("Select Ending, SP,
YieldSP, Yield3mo, Yield10yr FROM Weeks WHERE Ending >
1/1/2003")

End Sub
 
I am trying to write my first VBA app to populate a table
from a subset of another table. However, I keep getting a
"Run-time error '13': Type Mismatch" on the "Set
rstWeeks...." line below. (Marked with a "--->")

This is all the code that is written so far. The
db.openrecordset seems to be working...b/c if I remove the
"set rstweeks =", code runs fine. From looking at other
samples...it seems that this is pretty
straightforward...but am I missing something here? If
rstWeeks is a recordset and db.openrecordset returns a
recordset, then what is the problem?

Any help?

Public Sub cmdRunIteration_Click()

Dim db As Database
Set db = CurrentDb

Dim rstWeeks As Recordset
--->Set rstWeeks = db.OpenRecordset("Select Ending, SP,
YieldSP, Yield3mo, Yield10yr FROM Weeks WHERE Ending >
1/1/2003")

End Sub
Click to expand...
You need to enclose date literals with #s

Set rstWeeks = db.OpenRecordset("Select Ending, SP, YieldSP, Yield3mo,
Yield10yr FROM Weeks WHERE Ending >#1/1/2003#")

- Jim
 
What is the Datatype of "Ending"? Is it text?

If it is text (which I would be concerned about) then you need to
enclose the "date" with single quotes.

Yield10yr FROM Weeks WHERE Ending >'#1/1/2003'")
 
[email protected] said:
I am trying to write my first VBA app to populate a table
from a subset of another table. However, I keep getting a
"Run-time error '13': Type Mismatch" on the "Set
rstWeeks...." line below. (Marked with a "--->")

This is all the code that is written so far. The
db.openrecordset seems to be working...b/c if I remove the
"set rstweeks =", code runs fine. From looking at other
samples...it seems that this is pretty
straightforward...but am I missing something here? If
rstWeeks is a recordset and db.openrecordset returns a
recordset, then what is the problem?

Any help?

Public Sub cmdRunIteration_Click()

Dim db As Database
Set db = CurrentDb

Dim rstWeeks As Recordset
--->Set rstWeeks = db.OpenRecordset("Select Ending, SP,
YieldSP, Yield3mo, Yield10yr FROM Weeks WHERE Ending >
1/1/2003")

End Sub
Click to expand...

Access 2000 or later, right? You are probably missing the required
reference to the Microsoft DAO 3.6 Object Library, and if you have that
reference and at the same time have a reference to the ActiveX Data
Objects 2.x Library you still need to disambiguate your declaration of
the rstWeeks (because the Recordset object is defined in both
libraries).

First, with any code module open, click Tools -> References..., locate
"Microsoft DAO 3.6 Object Library" in the list and put a check mark next
to it if there isn't one already. Then close the dialog.

Second, change this:
Dim rstWeeks As Recordset
Click to expand...

to this:

Dim rstWeeks As DAO.Recordset

Third, though this isn't the source of the current problem, change this:
... WHERE Ending > 1/1/2003")
Click to expand...

to this:

... WHERE Ending > #1/1/2003#")

That should do it.
 
You're the man! I had the DAO library in....but adding the
"DAO." did the trick.

Thanks!

III
 
Back
Top