Where does Access store the ODBC connection string?

[SodaMan]

Hi,
In my application the user enters password information in a form and this is passed into the ODBC connection string for each query. The thing is that after a correct password is passed in, it seems access no longer checks the connection string!

In other words, if I run a passthrough query with a correct connection string, and then modify the connection string to be INcorrect, the query will still work. I'd rather it didn't. Any advice on how to delete the stored/hidden connection string?

Thanks, Dan

 

[hwkranger]

Hi Dan,

I'm not really sure as to why this would work for you. I am able to change my connection strings programmatically, and if i change them to something wrong - they don't work.

Here is what I do:

Private Function fUpdatePassThroughQueryConnection(str_DbName As String, str_Server)

Dim i As Integer
Dim s As New clsStatusBar

s.Steps = CurrentDb.QueryDefs.Count
s.MeterText = "Linking Queries"
    
    For i = 0 To CurrentDb.QueryDefs.Count - 1
    s.MoveMeterBar
        If (Len(CurrentDb.QueryDefs(i).Connect) > 1) Then
            CurrentDb.QueryDefs(i).Connect = "ODBC;driver={SQL Server};server=" & str_Server & ";" & _
          "database=" & str_DbName & ";" & "Trusted_Connection=Yes;" '"ODBC;DSN=" & DSN & ";Description=Central Proof Databases;DATABASE=" & DbName & ";Network=DBMSSOCN;Trusted_Connection=Yes"
        End If
        CurrentDb.QueryDefs(i).ODBCTimeout = 600
    Next i

s.ClearBar
End Function

You don't need most of the code. The line you're interested in is: CurrentDb.QueryDefs(i).Connect = "your connection string here"

Hope this helps.

Randall Vollen
National City Bank Corp.

 

[vbajock]

The connection string is valid at the entry point of the connection, and as long as the connection stays open, changing the string has no effect since it is only read when the connection is established. Do a

conn.close
Set Conn=nothing

and then see if it still doesn't ask for a password.

 

[VBslammer]

[tt]
CurrentProject.Connection[/tt]

VBSlammer

"You just have to know which screws to turn." - Professor Bob

 

[SodaMan]

Thank you hwkranger, vbajock, and VBslammer.

Not surprisingly, I am working on 10 different projects. However, I will try your responses by tomorow night (latest) and let you know my success.

 

[SodaMan]

Hi,

I tried to close the CurrentProject.Connection, but that did not solve the problem. I am using the DAO object, so perhaps that's why it didn't work (although I don't think it should be). Any new suggestions?

Below is the basic structure of my code:

Public Function openPWD_DB_Reset()
Dim con As Connection

Debug.Print CurrentProject.IsConnected
Access.CurrentProject.Connection.Close
Debug.Print CurrentProject.IsConnected

'Open the form, then from the form calls function below
DoCmd.openForm "frmPWD_SERVER_Update"
DoCmd.SelectObject acForm, "frmPWD_SERVER_Update", True
End Function

Public Function resetPWD_DB_PSQry(Optional connectReset As String = "NONE")
Dim qryDef As QueryDef

For Each qryDef In CurrentDb.QueryDefs
If InStr(qryDef.Connect, "ODBC") <> 0 Then
If StrComp(connectReset, "NONE") = 0 Then
qryDef.Connect = "ODBC;"
Else
qryDef.Connect = connectReset
End If
End If
Next qryDef

End Function

Thank you, Dan