August 19, 2014 at 7:49 am
I am using MS Access as a UI to a SQL Server 2008R2 database.
I want to populate a combo box list with values from a linked server. The name of my server has a backslash i.e. [PRDDWDB\DW1]. In the query builder I can supply the full four part name syntax with square bracket notation. i.e. [PRDDWDB\DW1].[ApplicationDataWarehouse].[dbo].[ADW_EMPLOYEE_AD_INFO].
When I run the SQL from the builder window it returns the results as expected. When I try to save the query I get a message which says that there's an error in the FROM statement.
I've tried to create the query as a view on the server and still get the same message. I've also tried creating the view as a pass-through query, same result. I've even tried creating it as a stored procedure - same result!
Is there any way to get around this problem? Interestingly this is only a problem when using an .accdb file. It's not a problem with a .adp file.
August 22, 2014 at 1:54 pm
Sorry about the late reply - been a BUSY few days. If you are building a front-end to SQL Server, then you need to use ODBC to link to the tables, unless you plan to use only unbound forms and write lots of VBA to use ADO or DAO. If that's the case, then you have to embed the server name in the connect string that you use to talk to SQL Server.
Stored procedures are only useful in specific circumstances where you want to process lots of data quickly, and most developers using .accdb (or .mdb) Access databases end up not using them. What is more useful is linking to a SQL Server view, which is viewed by Access as just another table. So long as you used linked tables, the server name is stored in the ODBC connection string stored in the table details, and should not be a problem. Now to your combo box issue, the data source can be a query as you suggest, but I would actually create a view in SQL Server, link to it and then make the linked table the data source for the combo. If you want to have a specific sequence for the options, then you will want to make it an Access SQL String (query) and specify that it sorts on the appropriate field in the correct order. I'm actually surprised that the builder will give you the correct results when putting in the fully qualified name, but that may come from the .ADP use of the builders.
Access .adp files are completely different from other Access database formats, and you do have to most of the work in SQL Server other than the forms and reports. And because of synchronization issues between Access versions and SQL Server versions, the .adp format is no longer supported in Access 2013. See the article Changes in Access 2013 for more details on that decision.
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
August 22, 2014 at 3:07 pm
Wendell;
Thank you for your response. I was wondering if I was going to get one. There were a bunch of views but yours is the first response.
A little more background and some questions;
By "...use ODBC..." you mean set up an ODBC datasource using the Windows ODBC Data Source Administrator utility and then using that in my .accdb file to connect to my database. Is that correct? If so, that's good - that's what I've done. To be clear. That's what I've done for the DB that I'm building the App for. The data for the look-up is actually coming from another database on another server which is linked to my DB through linked servers.
As I mentioned, I had tried creating a view on the server but still received a message indicating that it didn't like the FROM statement.
I didn't think of creating a link to the view in the .accdb file, rather, I was just calling the view directly. I will try that and report back.
With respect to using an .adp Vs. a .accdb I am aware that MS is dropping support for the .adp - which in my view, is regrettable. They're just so easy to use, and light weight - one database engine slowing things down rather than two etc....
The whole reason for the post was that, in consideration of the inevitable demise of the .adp I am trying to use a .accdb and ran into this roadblock.
The question;
Since the data that I'm using to populate my combo-box lookup comes from a different database than my app do I need to create a second ODBC datasource and use that or can I use the view (via a linked table in the .accdb)?
August 22, 2014 at 3:31 pm
I'm happy to report that by creating a view on the server (using OPENQUERY, which makes it nice and quick) and then creating a link to it in my .accdb file did work as a rowsource for my combo-box.
Just for the record - I still prefer a .adp to a .accdb. I just don't think the overhead of the Jet engine has any real value. At least I haven't seen any in my (admittedly limited) experience.
August 22, 2014 at 5:29 pm
I was pretty sure it would, and would be quick. On your question about needing two ODBC data sources, yes you would need one for each database that you are going to link tables from.
It seems that most developers who start with the .adp approach prefer it, although there are some limitations, such as not being able to store queries locally in the front-end. On the other hand developers who started with the .mdb approach usually prefer it, although you can run into performance problems with linked tables.
On the subject of performance with linked tables, as long as you are joining only two or three tables in a query, it will generally not be handled by Jet, but by the ODBC interface which transforms the SQL statement to what SQL Server needs to execute the query. If you get into a situation involving more tables, performance gets dicey as Jet must bring all of the data to Access and then execute the query. But with some care in design, we create complex forms and reports involving tables that contain several million rows, and get sub-second response time at the user workstation.
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply