ODBCDirect Workspace

[Guest]

If I create a ODBCDirect Workspace object, and then a Connection object, and
then a QueryDefinition, I am unable to use the query that has been created as
the RowSource for a ComboBox. The ComboBox doesn't recognize that the query
exists. Am I doing something wrong, or is this just not possible? Thanks
for helping.

 

[Guest]

Rob,

Are you using one Access database to work inside of another Access database?

Are you using ADO to hit a SQL server?

I am trying to understand why you would be using ODBCDirectWorkspace...

The following is a simple DAO version of what I think you are trying to do.
____________________________________________________________
Private Sub LoadComboBox_DAO()
Dim daoDbs As DAO.Database
Dim daoQdf As DAO.QueryDef
Dim strSql As String
Set daoDbs = CodeDb
strSql = "SELECT EmployeeID, [FirstName] & SPACE(1) & [LastName] As NAME
FROM dbo_tblEmployees;"
Set daoQdf = daoDbs.CreateQueryDef("MyNewQry")
daoQdf.sql = strSql
strSql = ""
daoQdf.Close
daoDbs.Close
Set daoQdf = Nothing
Set daoDbs = Nothing
Me.cboMyBox.RowSource = "Select * from MyNewQry Order By Name;"
Me.Requery
Me.Repaint
Me.cboMyBox.SetFocus
End Sub
___________________________________________________________

However if you are going to be using this query again to populate the combo
box
you may not want to destroy and recreate it over and over. It may be better
to
just change the SQL of the query rather than create it repeatedly. And on
another thought do you realy need the query at all? What purpose does
creating this query serve? You can simply feed the results of your SQL
directly into the object.
________________________________________________________________
Private Sub LoadComboBox_SQL()
Me.cboMyBox.RowSource = "SELECT EmployeeID, [FirstName] & SPACE(1) &
[LastName] FROM dbo_tblEmployees Order By FirstName;"
Me.Requery
Me.Repaint
Me.cboMyBox.SetFocus
End Sub
________________________________________________________________

 

[Guest]

Afther such a thoughtful response I am embarassed to tell you that I have
forgotten the details of what my problem was. However, I now have what I
think is a very similar problem - and I can tell you the details. I hope that
you can help with this version.

Here is the background: I have just started trying to understand
Client/Server architecture. I am doing that by trying to re-write a
self-contained Access database application so that the back end is in SQL
Server Express and the front end is an Access 2003 adp file. I have
successfully ported my tables to SQL Server and have created an Access
"Project" that uses OLEDB to connect to that back end. Now I am working on
re-writing the VB code behind my forms.

The complication comes because occassionally I need to read the data in a
second SQL Server database. Using VB I am able to open a connection to that
secondary database and to open a recordset that has the data that I want.
However, I don't know how to tell a Combo Box on a Form to display that data.
Can you help?

Thanks again for such a thoughtful response to my original question.

Rob,

Are you using one Access database to work inside of another Access database?

Are you using ADO to hit a SQL server?

I am trying to understand why you would be using ODBCDirectWorkspace...

The following is a simple DAO version of what I think you are trying to do.
____________________________________________________________
Private Sub LoadComboBox_DAO()
Dim daoDbs As DAO.Database
Dim daoQdf As DAO.QueryDef
Dim strSql As String
Set daoDbs = CodeDb
strSql = "SELECT EmployeeID, [FirstName] & SPACE(1) & [LastName] As NAME
FROM dbo_tblEmployees;"
Set daoQdf = daoDbs.CreateQueryDef("MyNewQry")
daoQdf.sql = strSql
strSql = ""
daoQdf.Close
daoDbs.Close
Set daoQdf = Nothing
Set daoDbs = Nothing
Me.cboMyBox.RowSource = "Select * from MyNewQry Order By Name;"
Me.Requery
Me.Repaint
Me.cboMyBox.SetFocus
End Sub
___________________________________________________________

However if you are going to be using this query again to populate the combo
box
you may not want to destroy and recreate it over and over. It may be better
to
just change the SQL of the query rather than create it repeatedly. And on
another thought do you realy need the query at all? What purpose does
creating this query serve? You can simply feed the results of your SQL
directly into the object.
________________________________________________________________
Private Sub LoadComboBox_SQL()
Me.cboMyBox.RowSource = "SELECT EmployeeID, [FirstName] & SPACE(1) &
[LastName] FROM dbo_tblEmployees Order By FirstName;"
Me.Requery
Me.Repaint
Me.cboMyBox.SetFocus
End Sub
________________________________________________________________
--
~ SPARKER ~

If I create a ODBCDirect Workspace object, and then a Connection object, and
then a QueryDefinition, I am unable to use the query that has been created as
the RowSource for a ComboBox. The ComboBox doesn't recognize that the query
exists. Am I doing something wrong, or is this just not possible? Thanks
for helping.

 

[Guest]

Rob,

the quick solution for this would be to create a table to hold the values
for your combo box. Set the combo box's Row Source to feed of the table. Then
when your form loads truncate the table and fill it with the values from your
recordset.
--
~ SPARKER ~

Afther such a thoughtful response I am embarassed to tell you that I have
forgotten the details of what my problem was. However, I now have what I
think is a very similar problem - and I can tell you the details. I hope that
you can help with this version.

Here is the background: I have just started trying to understand
Client/Server architecture. I am doing that by trying to re-write a
self-contained Access database application so that the back end is in SQL
Server Express and the front end is an Access 2003 adp file. I have
successfully ported my tables to SQL Server and have created an Access
"Project" that uses OLEDB to connect to that back end. Now I am working on
re-writing the VB code behind my forms.

The complication comes because occassionally I need to read the data in a
second SQL Server database. Using VB I am able to open a connection to that
secondary database and to open a recordset that has the data that I want.
However, I don't know how to tell a Combo Box on a Form to display that data.
Can you help?

Thanks again for such a thoughtful response to my original question.

Rob,

Are you using one Access database to work inside of another Access database?

Are you using ADO to hit a SQL server?

I am trying to understand why you would be using ODBCDirectWorkspace...

The following is a simple DAO version of what I think you are trying to do.
____________________________________________________________
Private Sub LoadComboBox_DAO()
Dim daoDbs As DAO.Database
Dim daoQdf As DAO.QueryDef
Dim strSql As String
Set daoDbs = CodeDb
strSql = "SELECT EmployeeID, [FirstName] & SPACE(1) & [LastName] As NAME
FROM dbo_tblEmployees;"
Set daoQdf = daoDbs.CreateQueryDef("MyNewQry")
daoQdf.sql = strSql
strSql = ""
daoQdf.Close
daoDbs.Close
Set daoQdf = Nothing
Set daoDbs = Nothing
Me.cboMyBox.RowSource = "Select * from MyNewQry Order By Name;"
Me.Requery
Me.Repaint
Me.cboMyBox.SetFocus
End Sub
___________________________________________________________

However if you are going to be using this query again to populate the combo
box
you may not want to destroy and recreate it over and over. It may be better
to
just change the SQL of the query rather than create it repeatedly. And on
another thought do you realy need the query at all? What purpose does
creating this query serve? You can simply feed the results of your SQL
directly into the object.
________________________________________________________________
Private Sub LoadComboBox_SQL()
Me.cboMyBox.RowSource = "SELECT EmployeeID, [FirstName] & SPACE(1) &
[LastName] FROM dbo_tblEmployees Order By FirstName;"
Me.Requery
Me.Repaint
Me.cboMyBox.SetFocus
End Sub
________________________________________________________________
--
~ SPARKER ~

If I create a ODBCDirect Workspace object, and then a Connection object, and
then a QueryDefinition, I am unable to use the query that has been created as
the RowSource for a ComboBox. The ComboBox doesn't recognize that the query
exists. Am I doing something wrong, or is this just not possible? Thanks
for helping.

 

[Guest]

Thank you, Sparker. I suppose I can do that, but it seems like there should
be a more direct solution.

Rob,

the quick solution for this would be to create a table to hold the values
for your combo box. Set the combo box's Row Source to feed of the table. Then
when your form loads truncate the table and fill it with the values from your
recordset.
--
~ SPARKER ~

Afther such a thoughtful response I am embarassed to tell you that I have
forgotten the details of what my problem was. However, I now have what I
think is a very similar problem - and I can tell you the details. I hope that
you can help with this version.

Here is the background: I have just started trying to understand
Client/Server architecture. I am doing that by trying to re-write a
self-contained Access database application so that the back end is in SQL
Server Express and the front end is an Access 2003 adp file. I have
successfully ported my tables to SQL Server and have created an Access
"Project" that uses OLEDB to connect to that back end. Now I am working on
re-writing the VB code behind my forms.

The complication comes because occassionally I need to read the data in a
second SQL Server database. Using VB I am able to open a connection to that
secondary database and to open a recordset that has the data that I want.
However, I don't know how to tell a Combo Box on a Form to display that data.
Can you help?

Thanks again for such a thoughtful response to my original question.

Rob,

Are you using one Access database to work inside of another Access database?

Are you using ADO to hit a SQL server?

I am trying to understand why you would be using ODBCDirectWorkspace...

The following is a simple DAO version of what I think you are trying to do.
____________________________________________________________
Private Sub LoadComboBox_DAO()
Dim daoDbs As DAO.Database
Dim daoQdf As DAO.QueryDef
Dim strSql As String
Set daoDbs = CodeDb
strSql = "SELECT EmployeeID, [FirstName] & SPACE(1) & [LastName] As NAME
FROM dbo_tblEmployees;"
Set daoQdf = daoDbs.CreateQueryDef("MyNewQry")
daoQdf.sql = strSql
strSql = ""
daoQdf.Close
daoDbs.Close
Set daoQdf = Nothing
Set daoDbs = Nothing
Me.cboMyBox.RowSource = "Select * from MyNewQry Order By Name;"
Me.Requery
Me.Repaint
Me.cboMyBox.SetFocus
End Sub
___________________________________________________________

However if you are going to be using this query again to populate the combo
box
you may not want to destroy and recreate it over and over. It may be better
to
just change the SQL of the query rather than create it repeatedly. And on
another thought do you realy need the query at all? What purpose does
creating this query serve? You can simply feed the results of your SQL
directly into the object.
________________________________________________________________
Private Sub LoadComboBox_SQL()
Me.cboMyBox.RowSource = "SELECT EmployeeID, [FirstName] & SPACE(1) &
[LastName] FROM dbo_tblEmployees Order By FirstName;"
Me.Requery
Me.Repaint
Me.cboMyBox.SetFocus
End Sub
________________________________________________________________
--
~ SPARKER ~


:

If I create a ODBCDirect Workspace object, and then a Connection object, and
then a QueryDefinition, I am unable to use the query that has been created as
the RowSource for a ComboBox. The ComboBox doesn't recognize that the query
exists. Am I doing something wrong, or is this just not possible? Thanks
for helping.