Select unique record SQL-VBA

[yn19832]

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?

[Cosmos75]

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

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.

[yn19832]

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

[Cosmos75]

Glad it worked for you!
:cool:

[ttam]

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.

[geekgirlau]

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