Call query using Excel from an Access query?

[MottoK]

Hi, I have the following code written in Excel which calls several queries from Access.

It works fine until I call a query which contains a criteria - and by this I mean a criteria which is pre written in to the query not one which requires the user to input anything.

Can someone show me where and what to change the code to in order to be able to run and import from a query with criteria present in them?

______________________________________________________

Code:


Function ADOImportFromAccessTable(DBFullName As String, _
strQ As String, TargetRange As Range)
Dim cn As ADODB.Connection, rs As ADODB.Recordset, intColIndex As Integer
Set TargetRange = TargetRange.Cells(1, 1)
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
DBFullName & ";"
Set rs = New ADODB.Recordset
With rs
.Open strQ, cn, adOpenForwardOnly, adLockReadOnly, adCmdStoredProc
For intColIndex = 0 To rs.Fields.Count - 1
TargetRange.Offset(0, intColIndex).Value = rs.Fields(intColIndex).Name
Next
TargetRange.Offset(1, 0).CopyFromRecordset rs
End With
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Function

____________________________________________________

Many thanks!

 

[mikej336]

What is strQ. You dim it, you open it but you did not define it.

Uncle Mike

 

[MottoK]

Yeh, sorry - this is just the code that does the importing (the important stuff). The rest of the code is split up over a couple of modules.

strQ is just the name of the query in the database.

Thanks.

 

[SkipVought]

What is the CODE that's in the query????

Help us out! No crystal ball here!

Skip,

_[red][/red]

 

[MottoK]

What's the difference?! hehe

They are just simple select queries - the ones with criteria e.g. a specific telephone number in the criteria field do not work but the same query without the criteria DO work.

These are simple select queries from an access db.

Thanks again.

 

[SkipVought]

Ahhhhhhhh.

Is the phone "number" really a NUMBER?

The field ought to be CHARACTER, since you will NEVER do math on it AND it is an IDENTIFIER.

Check the Design and the Query BOTH!!!

Skip,

_[red][/red]

 

[SkipVought]

Ahhhhhhhh.

Is the phone "number" really a NUMBER?

The field ought to be CHARACTER, since you will NEVER do math on it AND it is an IDENTIFIER.

Check the TABLE Design and the QUERY -- BOTH!!!

Skip,

_[red][/red]

 

[MottoK]

I have tested this on LOADS of queries, most don't even use numbers - just plain text with one simple text criteria, this has nothing to do with the query itself, it must be something to do with way the VBA is handling the query i.e. the "adCmdStoredProc" part.

Don't worry about the query design, I've spent too many years, with too many bad memories over wrong field types to make that mistake!!

Cheers..

 

[SkipVought]

"It works fine [red]until[/red] I call a query which contains a criteria "

"this has nothing to do with the query itself, it must be something to do with way the VBA is handling the query i.e. the "adCmdStoredProc" part."

Two contradictory statements!

Help us (and yourself) out, and lets see the WHOLE picture that you stubbornly want to ignore.

Skip,

_[red][/red]

 

[MottoK]

Sorry guys - I'm just confusing you. Obviously it has something to do with query but what I meant was from the code side of things i.e. the way the code is handeling the query - as these query's have been running fine for years on their own.

I think I have narrowed it down to one type of criteria in these queries - the "like" operator.

Here is an example:

This works:


SELECT WeekID.WeekID, TA_Historical_tbl.Date,
TA_Historical_tbl.USER_ID FROM WeekID INNER JOIN (Calendar INNER
JOIN TA_Historical_tbl ON Calendar.Date = TA_Historical_tbl.Date) ON
WeekID.WeekID = Calendar.WeekID GROUP BY WeekID.WeekID,
TA_Historical_tbl.Date, TA_Historical_tbl.USER_ID HAVING
(((TA_Historical_tbl.USER_ID)="7ah23"));



This doesn't:


SELECT WeekID.WeekID, TA_Historical_tbl.Date,
TA_Historical_tbl.USER_ID FROM WeekID INNER JOIN (Calendar INNER
JOIN TA_Historical_tbl ON Calendar.Date = TA_Historical_tbl.Date)
ON WeekID.WeekID = Calendar.WeekID GROUP BY WeekID.WeekID,
TA_Historical_tbl.Date, TA_Historical_tbl.USER_ID HAVING(((TA_Historical_tbl.USER_ID) Like "7*"));



Again, how can I write the code so it handles this query - in particular the "like" operator?

Really appreciate the help, thanks.

 

[PHV]

As in ADO the wild character is % you may try this:
Like "7%"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[MottoK]

Wow! it worked!!

Many thanks for that. The one problem I'm left with is that these queries are also used on their own from within Access i.e. not through the ADO code above - which means using the % doesn't work!

So I can get one working with % and one working with * - any ideas how I can get both working using just one operator?

Many thanks everyone.

 

[PHV]

You may try this:
SELECT WeekID.WeekID, TA_Historical_tbl.Date,
TA_Historical_tbl.USER_ID
FROM WeekID
INNER JOIN (Calendar
INNER JOIN TA_Historical_tbl ON Calendar.Date = TA_Historical_tbl.Date
) ON WeekID.WeekID = Calendar.WeekID
WHERE Left(TA_Historical_tbl.USER_ID,1)='7'
GROUP BY WeekID.WeekID, TA_Historical_tbl.Date, TA_Historical_tbl.USER_ID

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[MottoK]

PHV, many thanks for your help.

Problem is I am running lots of different queries which use the * operator and there for need a way to deal with this from the VBA (and ADO) side of things in Excel.

Anyone? I'm so close!!

Thanks again.

 

[SkipVought]

no SPACE after HAVING

 HAVING(((TA_Historical_tbl.USER_ID) Like "7*"));

Skip,

_[red][/red]

 

[SkipVought]

I'd actually CHANGE the Having Clause to a Where Clause

SELECT
  WeekID.WeekID
, TA_Historical_tbl.Date
, TA_Historical_tbl.USER_ID 

FROM        WeekID 
INNER JOIN (Calendar 
INNER JOIN TA_Historical_tbl
   ON Calendar.Date = TA_Historical_tbl.Date) 
   ON WeekID.WeekID = Calendar.WeekID 

WHERE TA_Historical_tbl.USER_ID Like "7*"

GROUP BY
  WeekID.WeekID
, TA_Historical_tbl.Date
, TA_Historical_tbl.USER_ID 

;

Skip,

_[red][/red]

 

[PHV]

any ideas how I can get both working using just one operator?
Use DAO instead of ADODB.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[MottoK]

Ok thanks everyone for their help, will give these a go.

MottoK.