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

I prefer the DSN -less connection but didn't have luck yet

My example was of a DSN-less connection, so presumably you'll be able to determine an appropriate connection string.  Again, http://connectionstrings.com/ can be very helpful.

Answer 4

My Requirements are unbound forms and any query or Data manipulation on SQL Server side without linking tables.

Ok: one question.

WHY!?

What real-life valid reason do you have to reject the basic, standard common way to use Access databases with SQL backends?

Sorry if this sounds like an attack; it isn't - I'm just not very experienced with DSN-less connections (a little more with unbound forms) and I'm trying to understand why they are essential in your situation.

Answer 5

That is my next step.

I can connect to sql server on the same computer.

Now I need to connect to sql server on different machine so DSN- less sample I guess would be the best

Another possibility what I am thinking is using one query in access and override it in run time.

(So I don't have to create a lot of hundred queries and use only one)

I prefer the DSN -less connection but didn't have luck yet

I am working rewriting acces 2003 to 2013

There is a lot of hundred queries.

My Requirements are unbound forms and any query or Data manipulation on SQL Server side without linking tables.

Appreciate your input