Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rating: Thread Rating: 2 votes, 5.00 average. Display Modes
Old 11-30-2012, 08:52 AM   #1
Tango
DB/Application Dev Newbie
 
Join Date: Jun 2011
Location: Sumter, South Carolina
Posts: 141
Thanks: 27
Thanked 1 Time in 1 Post
Tango is on a distinguished road
Check if ODBC linked table is "reachable"

I need to check (through VBA) if a linked table is online or not. The linked table is attached to a SQL server using an ODBC connection string. If the server is offline or unreachable is what I am using to trigger other code.


Anyone have ideas? I have been googling all morning with little luck.

I did find http://www.vbaexpress.com/forum/showthread.php?t=17777 which looks like what I need but I cant get it to work. I just get a
"Run time error ODBC driver manager data source name not found and no default driver specified"

Code:
Private Sub Form_Load()
 
Dim curtbl As String
Dim con
curtbl = CurrentDb.TableDefs("help table").Connect
Set con = CreateObject("ADODB.Connection")
con.Open curtbl
con.Close
Set con = Nothing
  
con_Exit:
    Exit Sub
    
con_error:
    MsgBox con
    'Run linkDB()
    Resume
End Sub

__________________
---------------
If I helped you, please click thanks or the balance scales on my post.
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Tango is offline   Reply With Quote
Old 11-30-2012, 08:59 AM   #2
Mr. B
"Doctor Access"
 
Join Date: May 2009
Location: Whitehouse, Texas
Posts: 1,932
Thanks: 7
Thanked 226 Times in 216 Posts
Mr. B has a spectacular aura about Mr. B has a spectacular aura about
Send a message via AIM to Mr. B Send a message via Yahoo to Mr. B
Re: Check if ODBC linked table is "reachable"

Check this link. It should help:
http://www.fmsinc.com/free/newtips/a...accesstip3.asp
__________________
Mr. B
Website:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Mr. B is offline   Reply With Quote
Old 11-30-2012, 09:01 AM   #3
Tango
DB/Application Dev Newbie
 
Join Date: Jun 2011
Location: Sumter, South Carolina
Posts: 141
Thanks: 27
Thanked 1 Time in 1 Post
Tango is on a distinguished road
Re: Check if ODBC linked table is "reachable"

I found that one too and frankly it confused the hell out of me.

__________________
---------------
If I helped you, please click thanks or the balance scales on my post.
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Tango is offline   Reply With Quote
Old 11-30-2012, 09:28 AM   #4
Rx_
Nothing In Moderation
 
Rx_'s Avatar
 
Join Date: Oct 2009
Location: Denver, Colorado
Posts: 2,803
Thanks: 636
Thanked 337 Times in 307 Posts
Rx_ has a spectacular aura about Rx_ has a spectacular aura about Rx_ has a spectacular aura about
Lightbulb Re: Check if ODBC linked table is "reachable"

Saw that you are connecting to SQL Server.
http://www.access-programmers.co.uk/...hlight=dsnless
http://www.access-programmers.co.uk/...hlight=dsnless
Sparkbyte added to this too.

My example is not the best code, but should get you started.
Basically, a local table list all of the tables to be linked. I use a couple of columns with checkboxes that can determine versions of linking (e.g. Developer, User, userLite...)
In my case, over 100 tables are linked.

Delete all connections, then re-connect.
Build a connection string then apply it in a function.
While mine are connecting, a pass/faill is indicated.

Granted, I am not a big fan of ADO. I use the SQL Server Native Client 10.0 The data types conversions have treated me well. The SQL Server Native Client is something to download from MS. DAO works really well. I am old and have been using DAO since the first version. you should let me know about your ADO experience.

Link to link for Native Client
http://www.access-programmers.co.uk/...hlight=dsnless
__________________
Were you lucky enough to get an answer? Please mark your question as [SOLVED] The original poster can go to Thread Tools to mark it as Solved.

Quotation Thomas Jefferson: "Peace is that brief glorious moment in history when everybody stands around reloading."

There are 2 Kinds of Countries on this Planet
1. Those that use the Metric System
2. Those that had a man walk on the moon

Denver, Colorado - The "Mile High City" - non-metric!
Rx_ is offline   Reply With Quote
Old 11-30-2012, 09:34 AM   #5
Tango
DB/Application Dev Newbie
 
Join Date: Jun 2011
Location: Sumter, South Carolina
Posts: 141
Thanks: 27
Thanked 1 Time in 1 Post
Tango is on a distinguished road
Re: Check if ODBC linked table is "reachable"

Both of those appear to go WAY beyond what I need to do. I don't need to re-link them or do anything to them. I just need to find out of the linked table is online. I have no preference between DAO or ADO.

Essentially this is a check that runs when the front end is opened to see if the SQL server is where it is supposed to be.
__________________
---------------
If I helped you, please click thanks or the balance scales on my post.
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Tango is offline   Reply With Quote
Old 11-30-2012, 09:48 AM   #6
Rx_
Nothing In Moderation
 
Rx_'s Avatar
 
Join Date: Oct 2009
Location: Denver, Colorado
Posts: 2,803
Thanks: 636
Thanked 337 Times in 307 Posts
Rx_ has a spectacular aura about Rx_ has a spectacular aura about Rx_ has a spectacular aura about
Thumbs up Re: Check if ODBC linked table is "reachable"

Leave it to me to make something simple too complex. I am really good at it too!

So maybe you put your code in a Function and call the function from the same place.

Have the function return a boolean (i.e. pass fail)
Instead of a message box in the error, set the function return value to false. Actually, I set these kinds of functions to False at the beginning. Run your query in the function, if it returns a value then set the function to True. Otherwise, it is already at false. (assume false until proven it connects).
If you need help with that function, just let us know.

I use functions all the time. If during the form open, it returns a false, - go to some other path to let the user know. If it passes, go on with the program.

Just to add unneeded complexity... LOL
This is an excellent time to create a local Log table and only record the connection faiures. Just the standard time stamp, error message, error number...
this way you might get some clues of why it failed.
__________________
Were you lucky enough to get an answer? Please mark your question as [SOLVED] The original poster can go to Thread Tools to mark it as Solved.

Quotation Thomas Jefferson: "Peace is that brief glorious moment in history when everybody stands around reloading."

There are 2 Kinds of Countries on this Planet
1. Those that use the Metric System
2. Those that had a man walk on the moon

Denver, Colorado - The "Mile High City" - non-metric!
Rx_ is offline   Reply With Quote
Old 11-30-2012, 10:07 AM   #7
Rx_
Nothing In Moderation
 
Rx_'s Avatar
 
Join Date: Oct 2009
Location: Denver, Colorado
Posts: 2,803
Thanks: 636
Thanked 337 Times in 307 Posts
Rx_ has a spectacular aura about Rx_ has a spectacular aura about Rx_ has a spectacular aura about
Re: Check if ODBC linked table is "reachable"

See if this might work

Create a TLogUsage table with autocounter and the field names listed below (UseDate, StrFormName, CallingProc, UserName, ..)
If your opening form has a connection - it will populate your table with who and where.

Call this with
dim PassFailOpen as boolean
PassFailOpen = LogUsage("Splash_Screen", "Form_Open")
If it passes, you have a nice log table of all the users, time stamp and form opened.
If it fails, you get a False returned

in the form open use

if LogUsage("Splash_Screen", "Form_Open") then
' its all good and recorded
else
' message box to user then close down gracefully
' you could modify the code below to write error to a local table
end if

Code:
Function LogUsage(ByVal strFormName As String, _
    strCallingProc As String, Optional ControlName) As Boolean
10       On Error GoTo Err_LogUsage
          Dim rst As DAO.Recordset  ' The tLogUsage table
          LogUsage = False  ' false until we prove it passed
20            Set rst = CurrentDb.OpenRecordset("tLogUsage", 2, dbAppendOnly + dbSeeChanges)
30            rst.AddNew
40                rst![UseDate] = Now()
50                rst![strFormName] = strFormName
60                rst![CallingProc] = strCallingProc
70                rst![UserName] = Environ("username")
80                If Not IsMissing(ControlName) Then
90                    rst![ControlName] = Left(ControlName, 75)
100               End If
110           rst.Update
120           rst.Close
130           LogUsage = True ' hey we connected and logged event
Exit_LogUsage_GIS:
140       Set rst = Nothing
150       Exit Function
Err_LogUsage:
          ' just resume next and the usage is not logged
  'MsgBox Err.Number & Err.Description ' OPTIONAL
160       Err.Clear
170       Resume Exit_LogUsage_GIS
End Function

__________________
Were you lucky enough to get an answer? Please mark your question as [SOLVED] The original poster can go to Thread Tools to mark it as Solved.

Quotation Thomas Jefferson: "Peace is that brief glorious moment in history when everybody stands around reloading."

There are 2 Kinds of Countries on this Planet
1. Those that use the Metric System
2. Those that had a man walk on the moon

Denver, Colorado - The "Mile High City" - non-metric!
Rx_ is offline   Reply With Quote
The Following User Says Thank You to Rx_ For This Useful Post:
Tango (11-30-2012)
Old 11-30-2012, 11:20 AM   #8
Tango
DB/Application Dev Newbie
 
Join Date: Jun 2011
Location: Sumter, South Carolina
Posts: 141
Thanks: 27
Thanked 1 Time in 1 Post
Tango is on a distinguished road
Re: Check if ODBC linked table is "reachable"

Jackpot, I cracked it and without adding or editing tables!


Code:
curtbl = CurrentDb.TableDefs("help table").Connect
 
  On Error GoTo con_error
 
  Dim cnn As ADODB.Connection
   Set cnn = New ADODB.Connection
   'Set the provider property to the OLE DB Provider for ODBC.
   cnn.Provider = "MSDASQL"
   ' Open a connection using an ODBC DSN.
   cnn.ConnectionString = "driver={SQL Server};" & _
      curtbl
   cnn.Open
   ' Find out if the attempt to connect worked.
   If cnn.State = adStateOpen Then
      MsgBox "Server online!"
   Else
      MsgBox "Sorry. The server is not responding."
   End If
   ' Close the connection.
   cnn.Close
 
con_Exit:
    Exit Sub
 
con_error:
  Dim resp
  resp = MsgBox("Server Connection Failed!" & vbCrLf & vbCrLf & "Would you like to change the name of your base server?", vbYesNo)
If resp = vbYes Then
    Run linkDB()
    Else
    Application.Quit
End If
__________________
---------------
If I helped you, please click thanks or the balance scales on my post.
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Tango is offline   Reply With Quote
Old 11-30-2012, 12:05 PM   #9
Rx_
Nothing In Moderation
 
Rx_'s Avatar
 
Join Date: Oct 2009
Location: Denver, Colorado
Posts: 2,803
Thanks: 636
Thanked 337 Times in 307 Posts
Rx_ has a spectacular aura about Rx_ has a spectacular aura about Rx_ has a spectacular aura about
Re: Check if ODBC linked table is "reachable"

way to go! Hopefully, you are expanding your programming horizons!
__________________
Were you lucky enough to get an answer? Please mark your question as [SOLVED] The original poster can go to Thread Tools to mark it as Solved.

Quotation Thomas Jefferson: "Peace is that brief glorious moment in history when everybody stands around reloading."

There are 2 Kinds of Countries on this Planet
1. Those that use the Metric System
2. Those that had a man walk on the moon

Denver, Colorado - The "Mile High City" - non-metric!
Rx_ is offline   Reply With Quote
Old 05-11-2014, 06:54 AM   #10
GanzPopp
Newly Registered User
 
Join Date: Jan 2013
Posts: 37
Thanks: 1
Thanked 15 Times in 14 Posts
GanzPopp is on a distinguished road
Re: Check if ODBC linked table is "reachable"

If you want to keep it simple, just try to open the table recordset:

Code:
'Check if an ODBC connected table is reachable
Function IsODBCConnected(TableName As String) As Boolean
    If Not TableExists(TableName, True) Then Exit Function
    
    Dim rst As DAO.Recordset
    
    On Error Resume Next
    Set rst = CurrentDb.OpenRecordset(TableName)
    IsODBCConnected = (Err.Number <> 3151)
End Function
Maybe not the most likeable and complete solution, but works for simple situations.

GanzPopp is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Question How to "requery" tables linked SQL Server (ODBC) lake_tuna General 11 12-29-2015 11:42 PM
How to check if record is in "update" or "insert" mode scheeps Forms 3 04-25-2011 01:55 PM
"Group by" causes "ODBC--Call Failed" error kaledev Queries 2 03-10-2011 12:47 PM
"This Recordset is not updatable" Using ODBC and Multi-Table SQL Query keirnus Modules & VBA 6 10-19-2009 09:40 PM
"ODBC Connection failed" in DoCMd.TransferDatabase "ODBC Database" Alika Modules & VBA 2 05-03-2004 07:37 AM




All times are GMT -8. The time now is 04:10 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World