bypass odbc connection

[chrisgia]

I have a MS Access 2007 database that contains a union query that merges 5 different odbc connections. If one connection is down I receive an 'ODBC--call failed' error and the query is unusable. Is there a way to have the database verify connection and if it cannot connect to one or more odbc, to simply by-pass the odbc(s) and use the others?

[NeoPa]

Test each connection first with a very simple query on each. When you know the available connections build the UNION query up from that in code.

[chrisgia]

I have 70+ users using this query on a daily basis. Most days all the connections are good. The connections are to each of my locations across the east coast. With weather and/or equipment issues there are times that one or more locations are down. I do not want to rebuild the query each time something happens.

[NeoPa]

ChrisGia:
I do not want to rebuild the query each time something happens.

You read the bit where I suggested that is done in code right?

[chrisgia]

Hadn't caught that but now I see it. This is exactly what I want to do but have no idea how to.

[NeoPa]

Perhaps if I laid out some pseudo-code for you that would help you develop it for your requirements

1. Look at the SQL of the UNION query you have that covers all the various sources.
   

   SELECT *
   FROM   [Source1]
   UNION ALL
   SELECT *
   FROM   [Source2]
   UNION ALL
   SELECT *
   FROM   [Source3]

2. Split up the SQL string into its various parts such that the bit that pertains to each source is kept together.
   

   str1 = "SELECT * FROM [Source1]"
   str2 = "SELECT * FROM [Source2]"
   str3 = "SELECT * FROM [Source3]"

3. Check each source and add each string into the main SQL string, with a "UNION ALL " if, and only if, the source is good.
   

   If Source1 Is Good Then strSQL = strSQL & " UNION ALL " & str1
   If Source2 Is Good Then strSQL = strSQL & " UNION ALL " & str2
   If Source3 Is Good Then strSQL = strSQL & " UNION ALL " & str3

4. If strSQL is empty then throw an error message or something. Don't forget to handle this possibility.
5. Otherwise, ensure you skip the first occurrence of " UNION ALL " from the string and use it.
   

   strSQL = Mid(strSQL, 12)

Assuming Source2 is bad but all others are good then, strSQL is built up in steps :

1. Check Source1 and add str1.
   

   " UNION ALL SELECT * FROM [Source1]"

2. Check Source2, find it fails and do nothing with str2.
3. Check Source3 and add str3.
   

   " UNION ALL SELECT * FROM [Source1] UNION ALL SELECT * FROM [Source3]"

4. Strip the first occurrence of " UNION ALL ".
   

   "SELECT * FROM [Source1] UNION ALL SELECT * FROM [Source3]"

   This is equivalent to :
   

   SELECT *
   FROM   [Source1]
   UNION ALL
   SELECT *
   FROM   [Source3]"

[chrisgia]

thank you. I will give it a try.