Select unique record SQL-VBA

Thanks in advance for your help.
I have an Access database and the record is like this:

Date Country Type Index
1.1 Canada A
2.1 Canada A
3.1 Canada A
4.1 Canada A
5.1 Canada A
6.1 Canada A
1.1 US A
2.1 US A
3.1 US A
4.1 US A
5.1 US A
6.1 US A
......
How can I select the unique country names from the database, If the select clause is correct as following, and I would like to fill an array with the country name, the codes are as following, but there is problem with "varArray(i) = rec("Country")"

strSELECT = "SELECT DISTINCT Country"
strFROM = " FROM tblIndex "
strSQL = strSELECT & strFROM
Set rec = db.OpenRecordset(strSQL, dbOpenDynaset)
rec.MoveLast
rec.MoveFirst

ReDim varArray(1 To rec.RecordCount)

i = 1
Do While Not rec.EOF
varArray(i) = rec("Country")
rec.MoveNext
i = i + 1
Loop

Can anyone give me some advice?

What exactly is the problem you are having? Are you generating an error? If so, what error is it?

The code itself looks OK to me. I would make slight changes

Code:

---

    Dim db As DAO.Database
    Dim rec As DAO.Recordset
    strSELECT = "SELECT DISTINCT Country "
    strFROM = "FROM tblIndex;"
    strSQL = strSELECT & strFROM
    Set db = CurrentDb()
    Set rec = db.OpenRecordset(strSQL, dbOpenSnapshot)
    rec.MoveLast
    rec.MoveFirst
    Debug.Print rec.RecordCount
    ReDim varArray(1 To rec.RecordCount)

    i = 1
    Do While Not rec.EOF
        varArray(i) = rec("Country")
        rec.MoveNext
        i = i + 1
    Loop

    rec.Close
    db.Close

---

Just a tip, you may want to use when posting VBA code in a thread.

with a million thanks, I changed my code into yours and it works!

Glad it worked for you!
:cool:

Next time please remember to put ";" at the end of any SQL statement.
I think it should be the main reason your code not working.

Actually I never put the ";" at the end - the SQL statement always works fine :devil2: