openRowSet connection string

Question

I have an openrowset connection issue. The  Access accdb file links to sql server by DSN. Here's the string I used:

Function ReturnConn()

  ReturnConn = "SELECT * FROM OPENROWSET('Driver={SQL Server}; Server = myServername; Database=myDataBase;    Trusted_Connection=Yes;','exec sp_StoredProcedure''" & APIFunctionCalls.FindUser & "''')"

end Function

I checked my ODBC driver list, there are two: SQL Server, and SQL Server Native Client.

There are several related posts here and tried the following too but doesn't work.

ReturnConn = "SELECT * FROM OPENROWSET('MSDASQL', 'DSN=VacancyDatabase','select * from  myDatabase.dbo.mytable) "

Both got 'Syntax error in FROM clause'

Is it really syntax error? Can I use OpenRowset here? There're so many versions online, but I haven't found one that works for me. Please let me know if you need more information.. Thanks a lot!

Answer 1

OpenRowset is a T-SQL Server function, NOT Access so it will not be recognized by Access. 

Besides, you cannot assign an SQL String to the return of a Function and get a Recordset as the return of the function.  In this case, the function simply returns the SQL String since the data-type of the Function is not declared. 

 

If you want to use OpenRowSet, use a Pass-Through Query since the SQL of the Pass-THrough Query is passed directly to the "server" database engine for execution without involvement by the Access database Engine.

Check you Access Help on Pass-Through Queries for more info.

 

---

Van Dinh

Answer 2

I don't quite understand the function neither.  But it works in Access 2010. The original select statement is very similar to the one I posted. It just use OpenRowSet to get the records. Now I'm upgrading it to 2013. And it doesn't work there. I try to avoid big change, but if it doesn't work, I think I'll try your suggestion, using pass through query.  thank you.

Answer 3

I don't quite understand the function neither.  But it works in Access 2010. The original select statement is very similar to the one I posted. It just use OpenRowSet to get the records. Now I'm upgrading it to 2013. And it doesn't work there.

 

If it works in Access 2010 for you, you must have used the Access Project format (ADP), not the database format (MDB/ACCDB).  With Access Project (ADP), there is a Connection Property that points directly to the SQL Server Database. Consequently,  the SQL Server Database Engine processes ALL SQLa in your Access Project, NOT the Access Engine.  Clearly with the Access Project, the SQL Strings must conform to Transact SQL (T-SQL) syntax, NOT ACE/JET syntax.

If you convert from ADP to ACCDB/MDB, the links to the articles/thread I posted (as well as links posted by other respondents) in

Recent MSDN Thread: Converting to a SQL back-end?

will provide much information on how to get your ACCDB/MDB Front-End to work with SQL Server Back-End and how to avoid a number of gotchas.  The thread involves converting an Access Back-End to SQL Server Back-End but the final result (ACCDB/MDB Front-End + SQL Server Back-End) is the same with your final configuation that you are trying to achieve! 

---

Van Dinh

Answer 4

Thank you very much, Van Dinh. Those links are very helpful to me. I'm reading them...I may have more questions later. This is my first time doing the Access conversion, so a lot to learn and try. Thanks again.

Answer 5

Made it work after reading one of the articles you recommended, not using OpenRowSet of course, but ado recordset. thanks a lot!