Hijack ODBC dialog and Use my Own 2

[MacroAlan]

Is it possible in VBA to “Hijack” the ODBC dialog and use my own form?

I want my users to use my form and pass the info to Access/ODBC before the dialog pops up.

My project builds the SQL then I do

 varStatus = SysCmd(acSysCmdSetStatus, strStatus)    'Update
    ConnectionsEtc
    DoCmd.RunSQL MakeQuesReslt      'Updates UNIQUE ids

And it calls this code, but it does not seem to work. Am I missing something?

Public Sub ConnectionsEtc()
    'ODBC connection String
    Dim conN As ADODB.Connection
    Dim ConnectionStringer As String
    Set conN = New ADODB.Connection
    ConnectionStringer = "ODBC;DATABASE=HSDBCS00;UID=" & gbl_DUserID & _
        ";PWD=" & gbl_Pswrd & ";DSN=SHDBCS00"
    conN.ConnectionString = ConnectionStringer
    conN.Open
    Set conN = Nothing
End Sub

My form captures the UserID and Password to send for multiple updates. The ID goes to a history file and the Password is zapped.


Alan

 

[CautionMP]

MacroAlan,
Does [tt]DoCmd.RunSQL MakeQuesReslt[/tt] require the ODBC connection?

If so I don't think you want to [tt]Set conN = Nothing[/tt] at the end of [tt]ConnectionsEtc[/tt] and a better method might be to use the [tt]Execute[/tt] method of the ADODB.Connection object to run your SQL string.

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[MacroAlan]

I'm a lot cloer than I used to be. At least now it gives me an error when it fails.

Public Sub ConnectionsEtc(GetRecs As Variant)
    [red]GetRecs is SQL string passed from builder[/red]
    Dim conN As ADODB.Connection
    Dim ConnectionStringer As String
    Set conN = New ADODB.Connection             [red]'Set Connection[/red]
    ConnectionStringer = "ODBC;DATABASE=HSDBCS00;UID=" & gbl_DUserID & _
        ";PWD=" & gbl_Pswrd & ";DSN=HSDBCS00"   [red]'Errors on this string[/red]
    conN.Open ConnectionStringer                [red]'Open ODBC connection [b]?[/b][/red]
    conN.Execute GetRecs                        [red]'Send SQL string[/red]
End Sub

My workaround is to bypass this and force the user to use the built-in dialog.


Alan

 

[JoeAtWork]

Your connection string looks strange to me in that you are both specifying the database name and a DSN.

Perhaps you will find this link useful:

http://www.connectionstrings.com/