Passthrough Query and Parameters

G

Guest

I have Access 2000. I am trying to create a passthrough query that will
reference a form for the parameters. There are two tables on a SQL server
linked with an inner join (DATAWSQL.dbo.INVOICE_HEADER and
DATAWSQL.dbo.BUSINESS_DATE_CONVERSIONS).

I am a beginner & have been reading the various postings on this, but
continue to get errors and such when using the ideas I find. Here is what I
have tried so far. FYI, when I tried to define db as a dao.database & qdf as
dao.querydef I got compilation errors. When I try it the following way I get
Error 3265 - Item not found in this collection.

Please help?

Thank you - Jennifer

Private Sub cmdRunQuery_Click()

On Error GoTo ProcError

Set qdf = CurrentDb.QueryDefs(ShipmentsPassthrough)

qdf.SQL = "SELECT * FROM DATAWSQL.dbo.BUSINESS_DATE_CONVERSIONS bdc with
(nolock)" & _
"INNER JOIN DATAWSQL.dbo.INVOICE_HEADER ih with (nolock)ON bdc.CYMDDT =
ih.ShipDate " & _
"WHERE bdc.usadat >= '" & Me.txtMonthStartDate.Value & _
"AND ih.[ShipWhse#] Not In('f','w','x','y','z')" & _
"GROUP BY bdc.USADAT, ih.[ShipWhse#],ih.ShipVia, ih.[Order#],
Abs([InvcTotal]), Abs([InvcSubTot]), Abs([InvFrtAmt]), Abs([FreightStdCost]),
bdc.MTHNM, bdc.MONTH, ih.shipdate" & _
"ORDER BY bdc.usadat desc;"


ExitProc: 'Clean up
On Error Resume Next
qdf.Close
Set qdf = Nothing
db.Close
Set db = Nothing
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure cmdRunQuery_Click..."
Resume ExitProc
End Sub
 
G

Guest

In the VB editor, click [Tools ] and [references...]
Make sure you have the option
[] Microsoft DAO 3.6 Object Library checked.
 
B

Bongard

Thanks for your response. I took a look at what references I had and
that DAO 3.6 Object library is already checked.

Any other ideas?
Thanks,
 
G

Guest

The only thing I can think of, is I didn't see this entry in your code snippet:
Dim qdf As DAO.QueryDef
 
G

Guest

Thank you! I actually didn't have it checked so that fixes my compilation
errors. But I'm still getting the 3265: Item not found in this collection
error. Thoughts?

Appelq said:
In the VB editor, click [Tools ] and [references...]
Make sure you have the option
[] Microsoft DAO 3.6 Object Library checked.


Jen said:
I have Access 2000. I am trying to create a passthrough query that will
reference a form for the parameters. There are two tables on a SQL server
linked with an inner join (DATAWSQL.dbo.INVOICE_HEADER and
DATAWSQL.dbo.BUSINESS_DATE_CONVERSIONS).

I am a beginner & have been reading the various postings on this, but
continue to get errors and such when using the ideas I find. Here is what I
have tried so far. FYI, when I tried to define db as a dao.database & qdf as
dao.querydef I got compilation errors. When I try it the following way I get
Error 3265 - Item not found in this collection.

Please help?

Thank you - Jennifer

Private Sub cmdRunQuery_Click()

On Error GoTo ProcError

Set qdf = CurrentDb.QueryDefs(ShipmentsPassthrough)

qdf.SQL = "SELECT * FROM DATAWSQL.dbo.BUSINESS_DATE_CONVERSIONS bdc with
(nolock)" & _
"INNER JOIN DATAWSQL.dbo.INVOICE_HEADER ih with (nolock)ON bdc.CYMDDT =
ih.ShipDate " & _
"WHERE bdc.usadat >= '" & Me.txtMonthStartDate.Value & _
"AND ih.[ShipWhse#] Not In('f','w','x','y','z')" & _
"GROUP BY bdc.USADAT, ih.[ShipWhse#],ih.ShipVia, ih.[Order#],
Abs([InvcTotal]), Abs([InvcSubTot]), Abs([InvFrtAmt]), Abs([FreightStdCost]),
bdc.MTHNM, bdc.MONTH, ih.shipdate" & _
"ORDER BY bdc.usadat desc;"


ExitProc: 'Clean up
On Error Resume Next
qdf.Close
Set qdf = Nothing
db.Close
Set db = Nothing
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure cmdRunQuery_Click..."
Resume ExitProc
End Sub
Click to expand...
Click to expand...
 
G

Guest

Check that you have a line like this in your code:

Dim qdf As DAO.QueryDef

I didn't see that in your original code snippet.

Chuck

Jen said:
Thank you! I actually didn't have it checked so that fixes my compilation
errors. But I'm still getting the 3265: Item not found in this collection
error. Thoughts?

Appelq said:
In the VB editor, click [Tools ] and [references...]
Make sure you have the option
[] Microsoft DAO 3.6 Object Library checked.


Jen said:
I have Access 2000. I am trying to create a passthrough query that will
reference a form for the parameters. There are two tables on a SQL server
linked with an inner join (DATAWSQL.dbo.INVOICE_HEADER and
DATAWSQL.dbo.BUSINESS_DATE_CONVERSIONS).

I am a beginner & have been reading the various postings on this, but
continue to get errors and such when using the ideas I find. Here is what I
have tried so far. FYI, when I tried to define db as a dao.database & qdf as
dao.querydef I got compilation errors. When I try it the following way I get
Error 3265 - Item not found in this collection.

Please help?

Thank you - Jennifer

Private Sub cmdRunQuery_Click()

On Error GoTo ProcError

Set qdf = CurrentDb.QueryDefs(ShipmentsPassthrough)

qdf.SQL = "SELECT * FROM DATAWSQL.dbo.BUSINESS_DATE_CONVERSIONS bdc with
(nolock)" & _
"INNER JOIN DATAWSQL.dbo.INVOICE_HEADER ih with (nolock)ON bdc.CYMDDT =
ih.ShipDate " & _
"WHERE bdc.usadat >= '" & Me.txtMonthStartDate.Value & _
"AND ih.[ShipWhse#] Not In('f','w','x','y','z')" & _
"GROUP BY bdc.USADAT, ih.[ShipWhse#],ih.ShipVia, ih.[Order#],
Abs([InvcTotal]), Abs([InvcSubTot]), Abs([InvFrtAmt]), Abs([FreightStdCost]),
bdc.MTHNM, bdc.MONTH, ih.shipdate" & _
"ORDER BY bdc.usadat desc;"


ExitProc: 'Clean up
On Error Resume Next
qdf.Close
Set qdf = Nothing
db.Close
Set db = Nothing
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure cmdRunQuery_Click..."
Resume ExitProc
End Sub
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

I do actually - I added it when you told me how to get the DAO set included.
Here is the new code. Thank you again for your help!

Private Sub cmdRunQuery_Click()

On Error GoTo ProcError

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Set qdf = CurrentDb.QueryDefs(ShipmentsPassthrough)

qdf.SQL = "SELECT * FROM DATAWSQL.dbo.BUSINESS_DATE_CONVERSIONS bdc with
(nolock)" & _
"INNER JOIN DATAWSQL.dbo.INVOICE_HEADER ih with (nolock)ON bdc.CYMDDT =
ih.ShipDate " & _
"WHERE bdc.usadat >= '" & Me.txtMonthStartDate.Value & _
"AND ih.[ShipWhse#] Not In('f','w','x','y','z')" & _
"GROUP BY bdc.USADAT, ih.[ShipWhse#],ih.ShipVia, ih.[Order#],
Abs([InvcTotal]), Abs([InvcSubTot]), Abs([InvFrtAmt]), Abs([FreightStdCost]),
bdc.MTHNM, bdc.MONTH, ih.shipdate" & _
"ORDER BY bdc.usadat desc;"

ExitProc: 'Clean up
On Error Resume Next
qdf.Close
Set qdf = Nothing
db.Close
Set db = Nothing
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure cmdRunQuery_Click..."
Resume ExitProc
End Sub


Appelq said:
Check that you have a line like this in your code:

Dim qdf As DAO.QueryDef

I didn't see that in your original code snippet.

Chuck

Jen said:
Thank you! I actually didn't have it checked so that fixes my compilation
errors. But I'm still getting the 3265: Item not found in this collection
error. Thoughts?

Appelq said:
In the VB editor, click [Tools ] and [references...]
Make sure you have the option
[] Microsoft DAO 3.6 Object Library checked.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

I do actually - I added it when you explained about the DAO tool set.

Here is the new code if it helps. Thank you!

Private Sub cmdRunQuery_Click()

On Error GoTo ProcError

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Set qdf = CurrentDb.QueryDefs(ShipmentsPassthrough)

qdf.SQL = "SELECT * FROM DATAWSQL.dbo.BUSINESS_DATE_CONVERSIONS bdc with
(nolock)" & _
"INNER JOIN DATAWSQL.dbo.INVOICE_HEADER ih with (nolock)ON bdc.CYMDDT =
ih.ShipDate " & _
"WHERE bdc.usadat >= '" & Me.txtMonthStartDate.Value & _
"AND ih.[ShipWhse#] Not In('f','w','x','y','z')" & _
"GROUP BY bdc.USADAT, ih.[ShipWhse#],ih.ShipVia, ih.[Order#],
Abs([InvcTotal]), Abs([InvcSubTot]), Abs([InvFrtAmt]), Abs([FreightStdCost]),
bdc.MTHNM, bdc.MONTH, ih.shipdate" & _
"ORDER BY bdc.usadat desc;"

ExitProc: 'Clean up
On Error Resume Next
qdf.Close
Set qdf = Nothing
db.Close
Set db = Nothing
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure cmdRunQuery_Click..."
Resume ExitProc
End Sub


Appelq said:
Check that you have a line like this in your code:

Dim qdf As DAO.QueryDef

I didn't see that in your original code snippet.

Chuck

Jen said:
Thank you! I actually didn't have it checked so that fixes my compilation
errors. But I'm still getting the 3265: Item not found in this collection
error. Thoughts?

Appelq said:
In the VB editor, click [Tools ] and [references...]
Make sure you have the option
[] Microsoft DAO 3.6 Object Library checked.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

I am about out of ideas, but two last things:
I assume that the query "ShipmentsPassthrough" already exists and that you
are not creating it in this code. If it doesn't exist already, create a
passthrough query by that name with a very basic SQL statement and save it.
Then retry the code.

The only other thing I suggest is to run Debug on you code and see if that
picks up anything.



Jen said:
I do actually - I added it when you explained about the DAO tool set.

Here is the new code if it helps. Thank you!

Private Sub cmdRunQuery_Click()

On Error GoTo ProcError

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Set qdf = CurrentDb.QueryDefs(ShipmentsPassthrough)

qdf.SQL = "SELECT * FROM DATAWSQL.dbo.BUSINESS_DATE_CONVERSIONS bdc with
(nolock)" & _
"INNER JOIN DATAWSQL.dbo.INVOICE_HEADER ih with (nolock)ON bdc.CYMDDT =
ih.ShipDate " & _
"WHERE bdc.usadat >= '" & Me.txtMonthStartDate.Value & _
"AND ih.[ShipWhse#] Not In('f','w','x','y','z')" & _
"GROUP BY bdc.USADAT, ih.[ShipWhse#],ih.ShipVia, ih.[Order#],
Abs([InvcTotal]), Abs([InvcSubTot]), Abs([InvFrtAmt]), Abs([FreightStdCost]),
bdc.MTHNM, bdc.MONTH, ih.shipdate" & _
"ORDER BY bdc.usadat desc;"

ExitProc: 'Clean up
On Error Resume Next
qdf.Close
Set qdf = Nothing
db.Close
Set db = Nothing
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure cmdRunQuery_Click..."
Resume ExitProc
End Sub


Appelq said:
Check that you have a line like this in your code:

Dim qdf As DAO.QueryDef

I didn't see that in your original code snippet.

Chuck

Jen said:
Thank you! I actually didn't have it checked so that fixes my compilation
errors. But I'm still getting the 3265: Item not found in this collection
error. Thoughts?

:

In the VB editor, click [Tools ] and [references...]
Make sure you have the option
[] Microsoft DAO 3.6 Object Library checked.
Click to expand...
Click to expand...
Click to expand...
 
Top