Prev: Add a wait time.
Next: Error communicating with the OLE server or ActiveX Control
From: Mark VII on 9 Sep 2009 12:16
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
From: Ron Weiner on 9 Sep 2009 22:30
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


"Mark VII" <MarkVII(a)discussions.microsoft.com> wrote in message
news:AC77ED59-7E0F-437C-A773-59A26CB7F5C9(a)microsoft.com...
> 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


 | 
Pages: 1
Prev: Add a wait time.
Next: Error communicating with the OLE server or ActiveX Control