Solved Problems with ADO connection

[disgracept]

Hi guys,

I'm going to deploy a DB frontend to users that connects to a backend DB where the tables reside.
Since the backend will be in a shared file server folder, i need to check if the connection is possible (the server may be down for example) every time the frontend is launched.

The path for the backend will be, of course, lengthy and, probably, will contain spaces...

So i'm trying to make a connection to the backend using ADO to check if it's all ok, with this code that i found after researching online:

Dim cnnConnection As ADODB.Connection
Set cnnConnection = New ADODB.Connection

With cnnConnection
    .CursorLocation = adUseServer
    .Open "PProvider=Microsoft.Jet.OLEDB.4.0;" & "Data Source='C:\Users\Carlos Revés\OneDrive\Ambiente de Trabalho\ImóveisDH_be.accdb'"
End With

But when i try to run it, it gives me this error:
Error: -2147467259. [Microsoft][ODBC Driver Manager] Data source name too long

My references are these:

Can't figure out why and my online search gave no results...

Can anyone help me please?
Thanks in advance.

 

[AHeyne]

How about the double 'P' in 'PProvider'?

Edit: I can't check it myself any further because I don't have the provider 'Microsoft.Jet.OLEDB.4.0'.

 

[disgracept]

How about the double 'P' in 'PProvider'?

Edit: I can't check it myself any further because I don't have the provider 'Microsoft.Jet.OLEDB.4.0'.

thanks @AHeyne,

in fact there was that typo there... When i removed it it gave another error concerning the provider itself.
After research foud out that i need to use the Microsoft.ACE.OLEDB.16.0 provider...

It worked with this one but now the result is diferent... It gives me always as if there is no connection to the backend, eventhough the file is there and the table exists and is linked.

The full code is:

Public Function ADOXTestLinkedTable(strTable As String) As Boolean
  ' Comments : Tests the specified linked table to see if its link is valid
  ' Params   : cnnConnection - open ADODB connection to the Jet Database
  '            strTable - name of the table to test
  ' Returns  : True if the link is valid, False otherwise
  ' Source   : Total Visual SourceBook

  Dim catTmp As New ADOX.Catalog
  Dim tblTmp As New ADOX.Table
  Dim strTmp As String
  Dim lngSaveErr As Long
  Dim cnnConnection As ADODB.Connection

  Set cnnConnection = New ADODB.Connection
 
  On Error GoTo PROC_ERR
 
  With cnnConnection
    .CursorLocation = adUseServer
    .Open "Provider=Microsoft.ACE.OLEDB.16.0;" & "Data Source=C:\Users\Carlos Revés\OneDrive\Ambiente de Trabalho\ImóveisDH_be.accdb"
  End With
    

  ' Open the catalog by setting its ActiveConnection property
  catTmp.ActiveConnection = cnnConnection

  ' Set a pointer to the table
  Set tblTmp = catTmp.Tables("tbl_Utilizadores") 'should be strTable here but i put the table name directly for test purposes
 
  Debug.Print tblTmp.Properties("Jet OLEDB:Create Link") 'this property should be True, since its a linked table but is False

  If tblTmp.Properties("Jet OLEDB:Create Link") = True Then
    ' It is a linked table so try to open it by getting the name property of one it its fields.
    ' If the table's link is not valid (for example, the database it is pointing to is moved, deleted, or renamed) this call fails.
    ' We disable error handling to handle a potential failure.
    On Error Resume Next
    strTmp = tblTmp.Columns(0).Name
 
    ' Save the error number
    lngSaveErr = Err.Number
 
    ' Re-enable error handling
    On Error GoTo PROC_ERR

    ' If the save error number is not 0, an error occurred and we can assume that the link is invalid
    ADOXTestLinkedTable = (lngSaveErr = 0)
  End If
 
  ' Close the catalog to release resources
  Set catTmp = Nothing

PROC_EXIT:
  Exit Function

PROC_ERR:
  Debug.Print "Error: " & Err.Number & ". " & Err.Description, , "ADOXTestLinkedTable"
  Resume PROC_EXIT
End Function

Is it a problem of ADOX? the property refers to Jet OLEDB and i'm using ACE OLEDB... But there is no property for ACE only JETs there...

 

[Isaac]

Why are you using ADO in this case instead of just using regular Access vba object model stuff (DoCmd.TransferDatabase, etc), to connect to a linked table ?? Isn't that a much harder way

 

[disgracept]

Why are you using ADO in this case instead of just using regular Access vba object model stuff (DoCmd.TransferDatabase, etc), to connect to a linked table ?? Isn't that a much harder way

Thanks @Isaac

You're right! I just need to try to read something from One table with error handling... Then if there's an error it means that i can't connect for some reason...

I was definitely going the hard way...

Thanks!!

 

[disgracept]

Good morning guys...

Solved the problem in a very simple way!!! Thanks @Isaac for the eye opening comment!!!

Here is the code i will use:

Private Sub test()
    Dim rsTable As Recordset
    
    On Error GoTo errorHandler
    Set rsTable = CurrentDb.OpenRecordset("SELECT tbl_Utilizadores.* FROM tbl_Utilizadores WHERE (((tbl_Utilizadores.UserID)=1));")
    
exitSub:
    Exit Sub
    
errorHandler:
    Debug.Print "erro"
    Resume exitSub
End Sub

And it works (the best part)!

So this thread is solved!

Thanks all!