Excel & Access

[gnod]

Hi,

why is it the rst.EOF = true?
In my query in access, i'm trying to get all RefCode which starts in "AAF"
but if i change "AAF*" into "AAFGenCar", the rst.EOF = False..


Const strMdbQryName As String = "qrySPL_REV_AA"

Sub SPL_ImportFromAccessToExcelByADO()
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim strMdbPath As String, strMdbFilename As String
Dim intRow As Integer, intCol As Integer
Dim Cell As Range
Dim blnFound As Boolean

strMdbFilename = "Co-Wide Consol - Revenue.mdb"
strMdbPath = ThisWorkbook.Path & "\" & strMdbFilename
Set cnn = New ADODB.Connection
Set rst = New ADODB.Recordset

cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & strMdbPath & ";"

With rst
.Open strMdbQryName, cnn, adOpenForwardOnly, , adCmdTable

SPLLoop_REV:
Do Until rst.EOF
For Each Cell In Worksheets("SummaryPerLoc").Range("SPL_LocCode")
If rst.Fields("OriginCode") = Cell Then
intRow = Cell.Row
blnFound = True
End If
If blnFound = True Then
With Worksheets("SummaryPerLoc")
.Unprotect modConsolRevMisc.strPassword
.Cells(intRow, 3).Value = rst.Fields(1).Value
.Cells(intRow, 4).Value = rst.Fields(2).Value
.Cells(intRow, 5).Value = rst.Fields(3).Value
.Cells(intRow, 6).Value = rst.Fields(4).Value
blnFound = False
rst.MoveNext
.Protect modConsolRevMisc.strPassword
End With
GoTo SPLLoop_REV
End If
Next Cell
Loop
End With

rst.Close
Set rst = Nothing
cnn.Close
Set cnn = Nothing
End Sub


Can someone help me??

Thanks...

[Norie]

gnod

I'm confused by your post, and the attachment doesn't seem to clarify things.

In the code you are referring to the query qrySPL_REV_AA.

That query has the criteria Like ('AAF*') for the RefCode field but I don't see you changing that anywhere.

[gnod]

Hi Norie,

what i'm saying is if the criteria is "AAF*" the rst.EOF is equal to true which i think it shouldn't be equal to true because it has a records in the database and it will not execute my Do Loop

regarding to "AAFGenCar", I'm testing the criteria and found out that if i change the criteria into "AAFGenCar" or the whole word of every RefCode, the rst.EOF is equal to false which it will execute the Do Loop

but i want to query in RefCode that starts with "AAF"

thanks..

[Norie]

gnod

That's basically what I worked out after playing about a bit with the query/code.

I'm actually baffled by this and I'm still trying to find a fix.

I don't know too much about ADO (still using DAO :bug: ) but perhaps that's where the problem lies.

I'm going to try out a few ideas and I'll post back.

[gnod]

ok, thanks for helping me..

:joy::bouncy::offwall::2jump::mbounce2:

[Norie]

Right I think I might have found a fix.

Instead of using Like use Left.

That is put this expression instead of RefCode:

Left([RefCode],3)="AAF"

and put True for it's criteria.

When I change the query like that it seems to work, ie EOF is not True, though I've not tested the rest of the code.

[OBP]

Perhaps you would be better off using
rst.movelast
and
rst.recordcount

to establish whether there are any records and how many.
You can then use the recordcount value in a for/next instead of a do loop

[OBP]

Sorry Norie I didn't see your post I was busy typing (slowly).

[Norie]

OBP

My first thought was to use MoveFirst but that just immediately errored as rst.EOF was True.

The fix I posted appears to work, at least rst.EOF is False and the loop is entered.

I'd really like to know what the original problem was.

Does ADO just not like Like? :ack::hide::SHOCKED:

[gnod]

Norie,

It works!!! Thanks... :clap2: :peace:

i'd also like to know what is the problem.. is it the LIKE operator or is it using the wildcard (*)?? :think: