How to create reports in Access via ADO When data is in SQL Server?

Question

I have an Access 2003 project in which all data is stored in SQL Server 2008. I am using ADO to view/update data via forms that are completely unbound. For example, a form has several textboxes and combo boxes on it. When the form is loaded I use ADO to make a call to a stored procedure on SQL SQL, it returns a recordset and I populate the controls, via VBA, with the data from the recordset. I like this approach because only the VBA is stored within Access. No data (well actually connection strings are stored in Access, but that is it!).

My problem is what to do when it comes to reports. I want to create reports that are based off of views created within SQL Server, however I would like to avoid, if possible, static linking to the views directly from within Access. Is it possible to set the recordsource of a report dynamically at run-time to be the results of a SQL Server view? If it is, how does one go about designing the report id Access does not contain any data?

More info ... The reason I want to avoid linking to the view in Access is the environment in which the Access application could be run changes (Production, Development, Test). Currently whenever I make any calls to the database stored procedures, I look up the connection string (Active Directory based so no passwords are stored) in the only table that is stored in Access .

Thanks for any assistance.

Answer 1

First of all let's be clear: you don't have an Access 2003 "project." You have an Access 2003 database.

An actual Access Data Project cannot have local tables, and uses a SQL Server as the back end. When you view Tables you see the ones that exist on the server, and under Queries you see the views, functions, and stored procedures that exist on the server. You can use the "Upsize Wizard" to turn an Access database into an Access data project (or probably better, just create a new ADP (Access Data Project) and import all the forms, reports, macros, and modules.

Here are my ideas:

1. Convert the database to an actual Access Data Project and then just use regular old queries as if they were addressed to the local database. You can even bind forms to stored procedures and they can be updatable. To deal with Production, Development, and Test, you just change the connection string in the GUI or you change it through code like so:

   Application.CurrentProject.CloseConnection
   Application.CurrentProject.OpenConnection NewConnString
   

   If you want to read the connection string from a centralized database or from a text file on a share or from a common table you load in each environment (that has the connection information for every other environment), that is up to. I have one Access Data Project that has an toolbar with an Environment dropdown. When the environment is switched, a child database dropdown is then populated, and finally all open forms are notified by an event (though bound forms close when this occurs).

2. There's nothing wrong with using linked tables. Just write a procedure that loops through all the tables and updates them to point to the correct server when you want to change environments. The difference between "static" linking and "dynamic" linking is just a single VB procedure that rips through all the tables and relinks them--easy peasy.

3. Setting a report recordset dynamically at runtime is problematic. It MIGHT be possible in actual Access Data Projects, but definitely not in regular MDBs.

4. You CAN create pass-through queries in an Access MDB, but I'm not sure about passing parameters in. You'd probably have to set the query text dynamically with the parameters hard-coded and then run the report. This would be a problem for a multi-user database unless each person gets his own front-end to run from.

I recommend that you go with option 1 or 2. Option 1 seems simplest but there is some learning to do before you'll become facile with ADPs over MDBs. Let me know if you think you'll go down that route and I I'll share some of the gotchas with you. However, it's probably easier than what you're doing now which is everything manually. (Ouch!) The second option would be fastest for implementing right away and not throwing any wrenches into your current skill with MDBs.

UPDATE

So if you want to link tables, here's some code to get you started:

Sub TableRelink(MdbPath As String)
   Dim Table As DAO.TableDef
   Dim Tables As DAO.TableDefs
   Set Tables = CurrentDb.TableDefs
   For Each Table In Tables
      If Table.SourceTableName <> "" Then 'If a linked table
         Table.Connect = ";DATABASE=" & MdbPath 'Set the new source
         Table.RefreshLink
       End If
   Next
End Sub

This code is for MDB files, but some digging will quickly give you the correct properties and values to use for SQL Server linked tables.

Another Thought

I just thought of another possible way to handle just the problem you're experiencing: Use a session-keyed "temp" table in Access. Create a local table that has all the columns the view returns, plus a GUID column. When the report is run, insert the contents of the view to the local table, keyed by a new GUID value. Set the recordsource of the report to SELECT * FROM MyViewTempTable WHERE GUID = '{GUID}'. Simple problem solved. On report_close, delete from the table. Perhaps put in a date also and delete after 10 days in case any rows get left behind.