How you can I test availability of a database before creating report?

[sgursahaney]

I am developing an Access Report that pulls information from two tables located on different SQL Server systems. I have been successfully able to link the two tables and develop a query that displays the data in both tables using a UNION ALL query.

However, there will be situations where one (or both) of the databases will be unavailable. During these instances, I want to simply return the data from the available database. I figured I could create 3 queries (1 with the UNION ALL, 1 for the first table, and 1 for the second table) and specify the appropriate query in the Report RecordSource depending on the availability of the databases. But, I am stuck on trying to figure out whether the databases are available. Does anybody have any suggestions?

-- Suresh

 

[Hap007]

Dear Suresh,

I can think of several ways to handle this, but here is how I would approach:

1) Create a Temporary Working table.
2) In VBA code, test to see if table one exists, and if so, open an append query, and read and copy the records returned from table 1 to the temporary Work table.
3) In VBA code, test to see if table two exists, and if so, open an append query, and read and copy the records returned from table 2 to the temporary Work table.
4) Run your report from the temporary Work table.
5) At close (or open) of report, run a delete query to remove all temporary records from the temporary Work table.

Good luck,
Hap


Access Developer

http://www.fcs-software.com/fcsaccessdev.html

-
Specializing in Access based Add-on Solutions for the Developer

 

[sgursahaney]

Hap:
I would prefer to stay away from temporary tables since I may not have much control over how much space or access the user has on their harddrive. And, I can't create temporary tables in the SQL Server databases since the user will only have read permission to the tables. What were your other solutions?

--Suresh

 

[Hap007]

Dear Suresh,

I can not imagine that a temporay table would take that much room, but here is another idea:

1) Create a query to table 1 only
2) Create a query to table 2 only
3) Use your existing Union query. (for both tables)

Given these 3 options:

When you run your report:
1) Check to see which tables are available, then set the recordsource of the report based on what tables are available.

Good Luck,
Hap...


Access Developer

http://www.fcs-software.com/fcsaccessdev.html

-
Specializing in Access based Add-on Solutions for the Developer

 

[sgursahaney]

Hap:
That is exactly the approach I would like to take, however I don't know how to check the availability of the databases. How would I check to see if each of the databases are available?

--Suresh

 

[cmmrfrds]

You could ask your dba if they would do a linkserver so that the one sql server table could be viewed from the other sql server and then write a cross server query.

A cross server query.
select a.* from bigtuna.northwind.dbo.customers a inner join
greatwhite.northwind.dbo.customers b on a.customerid = b.customerid

From the sql server side you can use the openrowset to read from another server without the need to link the server. Here is an example from sql server help.

D. Use OPENROWSET and another table in an INNER JOIN
This example selects all data from the customers table from the local SQL Server Northwind database and from the orders table from the Access Northwind database stored on the same computer.

Note This example assumes that Access is installed.

USE pubs
GO
SELECT c.*, o.*
FROM Northwind.dbo.Customers AS c INNER JOIN
OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'c:\MSOffice\Access\Samples\northwind.mdb';'admin';'mypwd', Orders)
AS o
ON c.CustomerID = o.CustomerID
GO

If you are interested in this approach, then post on the sql server Forum as how to check if the server/database is available. You could then write a stored procedure that does all the error checking and returns a resultset of either one or both tables. A stored procedure can be executed through a pass-through query in Access and be the rowsource of a report or form.

 

[sgursahaney]

Unfortunately, using linked servers via SQL Server defeats my purpose. If the main SQL Server with the link definitions fails, then I cannot retrieve data from either table. I need to access the tables independently directly from MS Access. I think the multiple query approach will work if only I can find out how I can have Access check the availability of the database(s) before I open the Recordset. What VBA code can I use from within Access to check the availability of databases?

--Suresh

 

[cmmrfrds]

You can try the connection and trap for error. It's not elegant but will work.


Function TestAdoConnSQL()
On Error GoTo ErrorExit:

Dim cn As New Connection
Dim rs As Recordset, connString As String
connString = "provider=SQLOLEDB.1;" & _
"User ID=sa;Initial Catalog=pubs;" & _
"Data Source=bigtuna;" & _
"Persist Security Info=False"
cn.ConnectionString = connString
cn.Open connString
Set rs = cn.OpenSchema(adSchemaTables)

While Not rs.EOF
If rs!table_type = "table" Then
Debug.Print rs!table_name
End If
rs.MoveNext
Wend
rs.Close
Set cn = Nothing

ErrorExit:
MsgBox "no server"

End Function