Using ADO recordset as combo box row source gives "id is invalid"

[Mark VII]

Greetings --

I need to access SQL Server 2005 tables from Access, and to work around the
incompatilibility between the bigint data type and the ODBC driver. Instead
of using ODBC, I'm creating SQL that casts the bigint field to varchar,
opening an ADO Recordset, then setting the Recordset property of the combo
box to the recordset. The code runs without error, but when I try to open
the combo box, I get a "column id is invalid" error.

Here's my code:

Private Sub Form_Open(Cancel As Integer)
On Error GoTo Err_Form_Open

Dim conDatabase As ADODB.Connection
Dim rstEntityTypes As ADODB.Recordset

Dim strSqlServerConnect As String
Dim strEntityTypeSql As String

strSqlServerConnect = _
"Provider=SQLNCLI;Server=<server>;Database=<db name>;Uid=<userid>;
Pwd=<password>;"

Set conDatabase = New ADODB.Connection
conDatabase.ConnectionString = strSqlServerConnect
conDatabase.Open

strEntityTypeSql = _
"SELECT Cast(EntityTypeId as varchar) as strEntityType,
EntityTypeName " & _
"FROM dbo.EntityType"
Set rstEntityTypes = New ADODB.Recordset
rstEntityTypes.Open strEntityTypeSql, conDatabase, adOpenStatic,
adLockReadOnly

'Do Until rstEntityTypes.EOF
' Debug.Print rstEntityTypes.Fields("strEntityType"),
rstEntityTypes.Fields("entitytypename")
' rstEntityTypes.MoveNext
'Loop

Set Me.cmbEntityType.Recordset = rstEntityTypes

Exit_Form_Open:
Exit Sub

Err_Form_Open:
MsgBox Me.Name & ", Form_Open, error # " & Err.Number & " " &
Err.Description, _
vbExclamation + vbOKOnly
Resume Exit_Form_Open

End Sub

The Do loop with debug.print is showing me that the expected data from the
recordset. I've got the combo box properties set in the usual fashion for a
multi column combo box with the prime key column hidden. I've tried just
returning the EntityTypeName field (which is string data), and I still get
the "id is invalid" error.

Any suggestions? Thanks...
Mark

 

[Ron Weiner]

I am not 100% sure about this, but I always thought you could NOT use an ADO
recordset as the row source of a combo or list box. You can however create
a PassThru query using the legal Sql sysntax, and bind the combos RowSource
to it.

Rdub