Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Hijack ODBC dialog and Use my Own 2

Status
Not open for further replies.

MacroAlan

Programmer
Joined
Dec 4, 2006
Messages
137
Location
US
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
Code:
 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?
Code:
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

 
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)
 
I'm a lot cloer than I used to be. At least now it gives me an error when it fails.
Code:
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

 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top