Solved: .ADP Detect Failed SQL connection

[Movian]

Hey,
as some of you may know from other posts. I am working on converting an access database from a single file solution to a front end back end SQL Express solution. This has gone very well and i have finished most of the code changes.

However i am now left with a new situation, mainly that of deployment. It appears that the Access DB hard codes the SQL connection string hidden away. I need a method to detect a failed connection attempt to the sql server and allow the user to enter new details for the SQL connection string. This will allow for the same DB to be distributed to multiple clients.

I have looked around the internet and found a function to alter the string however now i need a way to determine when there was a problem connecting.

"[DBNETLIB]ConnectionOpen (connect()).]SQL Server does not exist or access denied"

Any suggestions are appreciated.

[geekgirlau]

Can you post your connection code?

[Movian]

Sorry for the late reply. Its been a crazy month trying to get this implemented.

I am not manually connecting to the SQL server. I up-sized the DB using the built in system in access and the system automatically connects on start up to the Database. Should i change this to a manual solution ?

[hansup]

If the ADP connection is OK, CurrentProject.Connection returns the connection string. If the connection is not OK, CurrentProject.Connection results in error -2147467259 "Data provider could not be initialized".
Public Function ConnectionOk() As Boolean
Dim blnReturn As Boolean
Dim strMsg As String

On Error GoTo ErrorHandler

If Len(CurrentProject.Connection) > 0 Then
blnReturn = True
Else
blnReturn = False
End If

ExitHere:
On Error GoTo 0
ConnectionOk = blnReturn
Exit Function

ErrorHandler:
Select Case Err.Number
Case -2147467259 ' Data provider could not be initialized.
' pass
Case Else
strMsg = "Error " & Err.Number & " (" & Err.Description _
& ") in procedure ConnectionOk"
MsgBox strMsg
End Select
blnReturn = False
GoTo ExitHere
End Function
You can call that function from the On Open event of your startup form.

Private Sub Form_Open(Cancel As Integer)
If Not ConnectionOk() Then
MsgBox "connection not OK"
End If
End Sub
Instead of MsgBox, call your code which gathers information from the user and builds your new connection string.

Anyway, I hope that works for you. I tested with A2003 and SQL Server 2005, and it worked for me.

[Movian]

This does indeed detect the connection HOWEVER.

the on open and on Load code for my loading form does not appear to run if the database does not find the initial connection. It will give me the error that it cannot find the SQL server. Then it will open but not load my splash screen....

[Movian]

~ was no longer able to edit to post due to board problems so please excuse double post~

I was able to have a minor resolve on this. If i use an Autoexec macro it will still run even if there is a connection problem however the user will still be presented with a message box from the automated access atempt to connect before it runs the autoexec. What i ideally want to be able to do is BEFORE access automatically attempts the connection to detect the connection myself. On error read a text file to get connection information. Check connection with updated info. If there is still an error present a custom form asking for new server info.

So i should be able to do most of this myself im just having trouble doing it BEFORE access attempts the connection itself.

~Edit~

Perhaps somthing like the following? this dosn't work currently it complains about olddb.Connection

Public Function ConnectionOk() As Boolean
Dim blnReturn As Boolean
Dim strMsg As String
Dim olddb As Database

Set olddb = OpenDatabase(CurrentProject.Path & "\Database.adp")

On Error GoTo ErrorHandler

If Len(olddb.Connection) > 0 Then
blnReturn = True
Else
blnReturn = False
End If

ExitHere:
On Error GoTo 0
ConnectionOk = blnReturn

olddb.Close
Set olddb = Nothing

Exit Function

ErrorHandler:
Select Case Err.Number
Case -2147467259 ' Data provider could not be initialized.
' pass
Case Else
strMsg = "Error " & Err.Number & " (" & Err.Description _
& ") in procedure ConnectionOk"
MsgBox strMsg
End Select
blnReturn = False
GoTo ExitHere
End Function

[hansup]

We're not seeing the same thing. If if disable the call to ConnectionOk in my startup form's On Open event, I don't receive an error message when SQL Server is not available. The ADP takes longer to open in that case, but no error. My startup form is unbound.

Beyond that, I'm clueless on this one. Good luck.

[Movian]

This is the error i get.
I altered the code to read

IF ConnectionOK then
msgbox "OK"
else
msgbox "FAIL"
end if.

After seeing this message i then get the fail dialog. I guess il keep trying to figure this out :(

[hansup]

OK. Looks like Access 2007. I can fire up another machine to test that. Another difference occurred to me. I'm using Windows authentication for my connection to SQL Server, and I suspect you're not. I don't know how that difference affects the error messages.

All my ADP experience has been with Windows authentication. That made it really simple --- the connection properties are the same for every user. But I quit using ADP altogether when I read that Microsoft's Access team discourages ADP for new development.

[HiTechCoach]

I have also dropped using an ADP. I now just use an MDB/ACCDB.

My friends that deploy an ADP have purchased an installer. They create an install package that setups up there own instance of the SQL server so that the can always have the same Server name.

[hansup]

I tested my ADP with Access 2007 and got the same response as with A2003 --- no error message, but long delay, when SQL Server not available. So perhaps the differences we're seeing are a result of authentication methods.

[Movian]

Im using mixed mode with a sql account.
NOT windows authentication.

il try and test a couple things. Alternatively is there a way to disable the automatic access connection and for me to initiate the connection manually ? That way i can just set it all up myself.

To convert back to an accdb would i just import all the information and initiate the currentproject.connection in code on start up?

If so could you give me an example on how to initiate the connection? i appreciate all the help guys :)

[hansup]

I have also dropped using an ADP. I now just use an MDB/ACCDB.

Hi Coach,

I've been waiting to see how the ADP situation plays out. It doesn't sound like ADP will be officially deprecated soon. Still, if they're discouraging it, I'll avoid it.

Hans

[hansup]

Alternatively is there a way to disable the automatic access connection and for me to initiate the connection manually ? That way i can just set it all up myself.CurrentProject has a CloseConnection method. However, I suspect you've already found that. And I don't know whether you can call
CurrentProject.CloseConnection before Access attempts the connection when it starts your ADP.

Maybe if you can find a way to save your ADP without a connection property defined, you could use
CurrentProject.OpenConnection to open your own customized connection. I don't know if that's possible.

[hansup]

To convert back to an accdb would i just import all the information and initiate the currentproject.connection in code on start up?

If you go back to ACCDB, I would use ODBC to link the SQL Server tables and views. I wouldn't care about CurrentProject.Connection in that case because it is an ADO object; I use DAO for ODBC-linked tables.

[Movian]

So how would I initiate a database wide connection. So that it can pickup the linked tables bound to the forms with the ODBC connection ?

[hansup]

So how would I initiate a database wide connection. So that it can pickup the linked tables bound to the forms with the ODBC connection ? With ODBC, there is not a single database wide connection to the server. Instead the connection information for each linked table is stored as the Connect property for the TableDef.

? CurrentDb.TableDefs("dbo_People").Connect
ODBC;DSN=vm2003;Trusted_Connection=Yes;APP=Microsoft Office 2003;WSID=WIN7;DATABASE=Inventory
That example used a DSN. However, you can create table links with DSN-less connections. See this article by Doug Steele: http://www.accessmvp.com/djsteele/DSNLessLinks.html

[Movian]

Ok what i have done is the following

I now have a form that sets currentproject.baseconnectionstring = ""

This means that when the project next starts up it will not attempt to auto reconnect. The system will then read in an encrypted txt file with the connection settings. I then attempt to connect with these settings. If that does not work then i ask the user to enter new settings. I then modify the connection string and attempt another connection. if the new connection works then i export the new settings to the encrypted txt file. IF not it quits and asks them to confirm the settings.


Thanks for the help :)

[HiTechCoach]

Hi Coach,

I've been waiting to see how the ADP situation plays out. It doesn't sound like ADP will be officially deprecated soon. Still, if they're discouraging it, I'll avoid it.

Hans As far as I know, Microsoft has not upgraded ADP since the A2003 timeframe. They do still support it in Access 2007/2010.

[hansup]

As far as I know, Microsoft has not upgraded ADP since the A2003 timeframe. They do still support it in Access 2007/2010.
That's what I've heard, too, Coach. Smells to me like ADP could go the way of DAP. So for me, it's wait and see.

[HiTechCoach]

I agree.

I have not user an ADP since Access 2002. I find the not using an ADP has had some advantages.