Access 2013 - control.RowSource = Execute SQL statement result Little Help

Question

Hi

I like to bound data to a control straight from SQL server

dim ServerConnection As String = "Provider=SQLOLEDB; Data Source=Development; Initial Catalog=Production_LV; Integrated Security=SSPI;"

Dim rs As ADODB.Recordset

Dim conn As ADODB.Connection

Set conn = New ADODB.Connection

conn.Open ServerConnection      

Set rs = New ADODB.Recordset

Dim sql As String

sql = "SELECT Emp_Name, IDInt, Emp_Code, Mgt_Function FROM Emp_Mst WHERE([Emp_Log]='Cleaning' AND [Active]=1) ORDER BY Emp_Name"

 Set rs = conn.Execute(sql)

control.RowSource = rs.source       - Throw Error The name of the recordsource exist in a different database

This is does work when I had linked table to access ,I have requirements to  use it without linked table.

Can it be done? or Do I have to loop thru records and add each one ?

Thanks

Answer 1

If I understand you properly

Create query in access and use it- (that would probably  work) but,

My requirements are unbound Forms and any Data operation or Query does SQL Server.

No, that isn't what I meant.  What I am suggesting is:

1. Create a pass-through query in Access that works.
2. Open that query in design view.  Open its property sheet, copy the value of the "ODBC Connect Str" property (which is the .Connect property of the querydef) to the clipboard, then paste that between  the  brackets in the SQL string in your code.

3,  Delete the pass-through query.

I figure that Access will make a connect string for the query that it knows how to resolve, and that you should be able to use that same connect string.

Answer 2

You may be able to do something like this:

sql = "SELECT Emp_Name, IDInt, Emp_Code, Mgt_Function " & _

"FROM [ODBC;Provider=SQLOLEDB;Data Source=Development;Initial Catalog=Production_LV;Integrated Security=SSPI].Emp_Mst " & _

"WHERE([Emp_Log]='Cleaning' AND [Active]=1) ORDER BY Emp_Name"

control.RowSource = sql

That is, use the connection string, enclosed in square brackets, as a qualifier for the table name in a SQL string that you assign to the control's RowSource property.  I'm not completely sure this will work, but similar syntax works in other cases.

The connection string I suggested in my example may not be correct.  I suggest you create a pass-through query to this SQL Server database, make sure it works, and then open that query in design view and look at its Connect property.  That ought to give you a value that Access will accept.

Answer 3

Your solution works great  but access 2013 pop up Window 'Select Data Source

I choose  Machine Data Source , select user - click ok 

And  I get my Data to list box

any work around for that (it should verified automatically,because     -    Integrated Security=SSPI)

Answer 4

Well I think the  main problem is that

control.RowSource = rs.source   - This sourse should point to sql server instead it looks in  to tables in access

Answer 5

It is  looking for a table in access instead look in Sql server specified in ServerConnection ???????????????

I like to bound data to a control straight from SQL server

dim ServerConnection As String = "Provider=SQLOLEDB; Data Source=Development; Initial Catalog=Production_LV; Integrated Security=SSPI;"

Dim rs As ADODB.Recordset

Dim conn As ADODB.Connection

Set conn = New ADODB.Connection

conn.Open ServerConnection      

Set rs = New ADODB.Recordset

Dim sql As String

sql = "SELECT Emp_Name, IDInt, Emp_Code, Mgt_Function FROM Emp_Mst WHERE([Emp_Log]='Cleaning' AND [Active]=1) ORDER BY Emp_Name"

 Set rs = conn.Execute(sql)

control.RowSource = rs.source       - Throw Error The name of the recordsource exist in a different database