Excel VBA ODBC connection failed

Excel VBA ODBC connection failed

I'm using Excel 2010 VBA to retrieve data from MS Access 2010 with ODBC
connection to SQL Server R2 Express, in my previous machine there is no
issue (32bit platform) but when I got new machine it's always said "ODBC
connection to (odbc name) failed -2147467259".
From Access 2010 to SQL there is no issue, but when I retrieve data from
Excel always trigger this error.
I have check the permission for the user (DBO), all the app in the same
machine, all the services are on, ODBC setting is correct. During the
execution of the script all OK except when the line is opening the table.
Function RetrieveProjectList()
Dim strConn As String
Dim conn As New ADODB.Connection
Dim rec As New ADODB.Recordset
Dim intColCount As Integer
Dim strName As String
Dim strSQL As String
On Error GoTo Error_Trap
strName = ThisWorkbook.path & "\DBSource V0.1.accdb"
Set conn = New ADODB.Connection
strConn = "Provider=microsoft.ACE.oledb.12.0;"
strConn = strConn & "Data Source=" & strName & ";"
conn.Open ConnectionString:=strConn
Set rec = New ADODB.Recordset
strSQL = "SELECT qryProjectList.* " & _
"FROM qryProjectList ORDER BY tblArea.AreaName,tblProject.ProjectName;"
rec.Open strSQL, conn, adOpenDynamic, adLockOptimistic
'Retrieve data from Access
rec.MoveFirst
If rec.Fields.count <> 0 Then
After syntax "rec.open ...." the error pop up. In other machince are OK.
Question:
Is this related to OS version 64bit? What did I miss out here? Any advice
will be highly appreciated.
Thanks, seageath