Access to SQL Server connection mystery

[JGT]

Our FE/BE Access (mdb)/SQL Server application is doing very well in a corporative environment for months. However last week, when we needed to introduce an upgrade to the system, adding new functions, the swapping of the FE links between the SQL Servers (2000) for testing, homologation and production (easily done by menu push buttons starting VB programmed functions) stopped working.
As in the mean time my desktop was renewed, the suspect felt first on the PC configuration.
After a bunch of testing we finally ended up with this simple basic operation scenario which should be easy going but doesn’t:
1. creating a new empty access database
2. creating a dsn file for ODBC linking for SQL Server access with SQL login authentication
3. creating the links with a SQL Server manually through the Access menus (external data).

As expected, this works perfect, all tables appear within the Access FE, and one can open and see all SQL Server data stored.
The problem appears after closing and re-opening the app: the links are still there, but do not longer access! When clicking on a table you get a “Connection failed” error message. There is no way to get the connection fixed, whatever data filled in the dialog pop up masks, you always get the “Connection failed” error message.

On the other hand, repeating the manual menu link routine once more will connect (the same tables with suffix “1”) and the tables again become accessible (both the former links and the new ones).
Our Help Desk people tried this on another machine (in other building) and got the same results.
Concluding: although SQL Server access is available and Access is able to establish connections, the established connections fail when restarting the app.
This loss of connection looks even stranger if you consider that the original app, linked to the production SQL Server, keeps running normally and can be freely opened and closed (but not swapped to another Server – i.e. connecting through VB programming also fails)
One hint: after the app re-opening failure msg, the current logged in user appears in one dialog mask i.e. it looks like Access trying to link through Windows Authentication, ignoring the pre-established SQL Authentication.

Does anybody have a clue to this mystery? We are lost!

Thank you very much for any help.
http://www.access-programmers.co.uk/forums

 

[FoFa]

I have the same problem. I coded a DSNless connection that relink the ODBC tables each time the Db is opened by someone.
Delays opening the main form a few seconds, but works like a charm actually.

 

[JGT]

Thank you for answering FoFa. It is always nice to meet someone who has the same problem, especially when he has a solution!

As nobody seems to have a solution for the ODBC problem I face, I would like to try your ODBC-less solution, it might be a way out or even a better way to do things. But I have no idea how to do this in ACCESS.mdb (not .adp), can you give me a hint or some piece of code?

Thanks in advance.

 

[FoFa]

Global ODBC1 As String
Global ODBC2 As String
Global ODBC3 As String
Global ODBC4 As String

Function InitApp()
ODBC1 = "SERVERNAME"
ODBC2 = "DATABASE NAME"
ODBC3 = "SQL USER ID"
ODBC4 = "SQL USER PASSWORD"
End Function

Function RelinkTables() As Boolean
Call RefreshODBCLinks("ODBC;Description=Optional Description Here;DRIVER=SQL Server;" & _
"SERVER=" & ODBC1 & ";UID=" & ODBC3 & ";" & _
"PWD=" & ODBC4 & ";DATABASE=" & ODBC2 & ";LANGUAGE=us_english")

End Function

Public Sub RefreshODBCLinks(newConnectionString As String)
On Error GoTo RODBCL_ERR
Dim db As DAO.Database
Dim tb As DAO.TableDef
Set db = CurrentDb
For Each tb In db.TableDefs
If Left(tb.Connect, 4) = "ODBC" Then
tb.Connect = newConnectionString
tb.RefreshLink
Debug.Print "Refreshed ODBC table " & tb.Name
End If
Next tb
Set db = Nothing
Exit Sub
RODBCL_ERR:
MsgBox "Error during table link occured", vbCritical, "Error Quitting"
Quit
End Sub


I call the InitApp and RelinkTables first thing when the application opens. Not super fancy, but does what i need.

 

[JGT]

Hi FoFa,

I didn´t got right your former return, I understood you were using a ODBC-less connection, but you wrote a DSN-less connection. A lack on attention, probably wishfull thinking (may be because some time ago, someone was criticizing ODBC links to SQL saying .adp can do without ODBC.)

So, for resfreshing links I use the same procedure you described. But, it is strange that you need to go through this each time when a user opens the app, all tables should remain linked. That’s the way it works in our corporate environment.

Today, I can refresh links in the actual App version – linked to the production SQL Server - I can also swap back and forth with an Access BE, but I cannot swap to a different SQL Server.
The same way, I cannot link my updated version of the App to any SQL Server through VB in the corporate environment.
I do can connect through DSN, but all links are lost closing the app.
I tried the latter with a brand new empty FE, same result.

Testing things in my home office environment, I observed that once the BE tables are linked to the SQL Server, you can even delete de DSN file, all links stay connected. This means Access stores the connection data somewhere. I suspected such because the DSN file doesn’t contain any pwd (necessary for the SQL Authentication). Probably there is something wrong with this storage procedure.

When I started with SQL, substituting the ACCESS BE with a SQL SERVER BE, I stumbled somewhat to get the connection fit, to begin with our Corporate Security guys limiting user access to our PCs, so I needed Help Desk to set up the DSN file. After that I learned how to switch connections through VBA.
But for some reason, my PC was the only one allowing swapping between servers. Now that I got a new PC, me too cannot swap anymore!

To connect to a different SQL Servers I use this procedure:

Obs: y_MasterTables is a FE Table, listing all BE Tables that should be linked to SQL Server.

Connect_SQL_="ODBC; Driver=SQL Server; Server= ServerX; Database= DatabaseX; UID= UIDX; PWD= PWDX"
ODBCDb_ = "ODBC Database"

Set db_ = CurrentDb()
sql_ = "SELECT TableName FROM y_MasterTables ORDER BY TableName;"
Set Qd_ = db_.CreateQueryDef("", sql_)
Set Rs_ = Qd_.OpenRecordset
' all tables of y_MasterTables
ii_ = 1
Do While Not Rs_.EOF
TdN_ = Rs_!TableName
Origem_ = "dbo." & TdN_
Destination_ = "dbo_" & TdN_
DoCmd.TransferDatabase acLink, ODBCDb_, Connect_SQL_, acTable, Origem_, Destination_, , True
ii_ = ii_ + 1
Rs_.MoveNext
Loop
Rs_.Close

 

[FoFa]

I agree with you, the tables should remain linked, but for some reason, using a 2003 MDB file, with 2007, the links seem to break on a regular basis. I am not sure why that is, so refreshing the links for each connection works. Maybe because our FE is running on Citrix, I don't know for sure. Plus getting our security to setup a DSN for the Citrix farm, is a problem, so this route seems more problem free in the long run.