Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Youngstown, Ohio, USA
    Thanked 0 Times in 0 Posts

    Need to click 'OK' (A2K SP3)

    I am trying to automate a procedure to create an ODBC link to an AS400 database. So far, the code I have will automatically delete the existing link, calculate the filename to link to (the file is archived using MMDD as part of the filename), and get most of the way through the link creation process. The stumbling block now is that when the link is created, Access displays a 'Select Unique Record Identifier' dialog box that I want to bypass. What command would be necessary to do this?

    <pre>Option Compare Database

    Function macLinkARDEDT()
    On Error GoTo macLinkARDEDT_Err
    Dim MM As String
    Dim DD As String
    Dim MMDD As String

    'Calculate month/day value for week just ended
    If Month(Date - Weekday(Date)) < 10 Then
    MM = "0" & Month(Date - Weekday(Date))
    Else: MM = Month(Date - Weekday(Date))
    End If
    If Day(Date - Weekday(Date)) < 10 Then
    DD = "0" & Day(Date - Weekday(Date))
    Else: DD = Day(Date - Weekday(Date))
    End If
    MMDD = MM & DD

    'Delete existing linked table
    DoCmd.DeleteObject acTable, "tblTest"

    'Create new linked table using variable 'MMDD' to dynamically
    'link to most recent week's AR Daily Edit data

    DoCmd.TransferDatabase acLink, "ODBC", _
    & MMDD & " ", acTable, "ARBACKUP.ARDEDT" & MMDD, _
    "tblTest", False

    'Insert command to click "OK" button on 'Select Unique Record
    ' Identifier' dialog box. Select no fields, exclude no
    ' records

    'Insert command to run append query to copy linked data to
    ' permanent table

    Exit Function

    MsgBox Error$
    Resume macLinkARDEDT_Exit

    End Function</pre>

    Attached Images Attached Images

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Evergreen, CO, USA
    Thanked 65 Times in 64 Posts

    Re: Need to click 'OK' (A2K SP3)

    I can't find anything that lets you specify a parameter that describes what field should be set as the primary key, so the only thing I can suggest is to use SendKeys to complete the dialog box. If you don't need to update the data, you can just send the {ENTER} key. You might turn off the screen echo before you do that so the user doesn't see the nonsense going on. Hopefully someone else will have a better answer.

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 31 Times in 31 Posts

    Re: Need to click 'OK' (A2K SP3)

    Does it help if you set DoCmd.SetWarnings False before, and DoCmd.SetWarnings True after the DoCmd.TransferDatabase line?

  4. #4
    5 Star Lounger
    Join Date
    Feb 2001
    Youngstown, Ohio, USA
    Thanked 0 Times in 0 Posts

    Re: Need to click 'OK' (A2K SP3)

    I've tried SendKeys before and after, with and without 'wait' set, and unfortunately nothing helped. The same held true for SetWarnings. <img src=/S/sigh.gif border=0 alt=sigh width=15 height=15>

    I've even asked about the file definition on the AS400. It has some keys built in, but not enough to satisfy Access that each record is unique. One of our programmers suggested that a logical view of the file might be adequate, if we could get one to survive the archiving and renaming process. Ah well, another project for another day. In the interim I guess I can live with a mostly-automated process and accept a single button click if no other solutions turn up.

    Thanks. <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  5. #5
    Bronze Lounger
    Join Date
    Nov 2001
    Arlington, Virginia, USA
    Thanked 3 Times in 3 Posts

    Re: Need to click 'OK' - Link ODBC Table (A2K SP3)

    Instead of TransferDatabase, try linking tables via ADOX (ADO Extensibility library). I tested this with linked Visual FoxPro (VFP) tables (which must be linked via ODBC in A2K or later); using TransferDatabase method (or linking "manually"), for some tables the "Select Unique Record Identifier" dialog pops up. Using ADOX as shown in example, the table is linked via specified ODBC DSN w/no dialog popping up (this assumes a DSN already has been created for the record source). Example:

    Public Function adoLinkODBCTable(strTbl As String)
    On Error GoTo Err_Handler

    Dim cat As ADOX.Catalog
    Dim tbl As ADOX.Table
    Dim strConn As String
    Dim strMsg As String

    Set cat = New ADOX.Catalog
    cat.ActiveConnection = CurrentProject.Connection
    Set tbl = New ADOX.Table
    tbl.Name = strTbl
    Set tbl.ParentCatalog = cat

    ' DSN for ODBC table defined:
    strConn = "ODBC;DSN=Visual FoxPro Tables;" & _
    "SourceDB=C:VFPDBFVFP_APP.DBC;" & _
    "SourceType=DBC;Exclusive=No;BackgroundFetch=Y es;" & _
    "Collate=Machine;;TABLE=" & strTbl
    With tbl
    ' Linked .MDB tables:
    ' tbl.Properties("Jet OLEDB:Link Datasource") = strConn
    ' Linked ODBC tables:
    .Properties("Jet OLEDB:Link Provider String") = strConn
    ' For NEW linked tables only:
    .Properties("Jet OLEDB:Remote Table Name") = strTbl
    .Properties("Jet OLEDB:Create Link") = True
    End With

    ' New linked tables only:
    cat.Tables.Append tbl

    Set cat = Nothing
    Set tbl = Nothing
    Exit Function
    strMsg = "Error No " & Err.Number & ": " & Err.Description
    MsgBox strMsg, vbInformation, "ADO LINK ODBC TABLE ERROR"
    Resume Exit_SubName

    End Function

    I don't use AS400 but this works OK with VFP tables. Note that tables linked using this method are NOT updatable; to be updatable, the "Unique Record Identifier" has to be specified; as noted in other replies, there doesn't seem to be any way to specify these fields in connection string or other property, AFAIK. To use ADOX, set a reference to Microsoft ADO Ext. 2.x for DDL and Security.

    PS: One drawback using ADOX to link new tables (or refresh existing links), the database will bloat by several MB's, even for a single table; a small db can double in size. If using this method, be sure to provide for compacting db after linked tables are created/refreshed.


  6. #6
    4 Star Lounger
    Join Date
    Aug 2002
    Dallas, Texas, USA
    Thanked 0 Times in 0 Posts

    Re: Need to click 'OK' (A2K SP3)

    Are you ready for the good news? It is possible to do what you want, it's just not possible to do it in the actual application that you are running.

    Here's why. Access is single threaded, which means that it can only run one 'thread' of code at a time. When your code runs into the 'prompt' window that you are getting, that window is dialog, which means that any code that caused that window to open is going to 'pause' until that window is closed. Thus SendKeys, and any other code, is NOT going to run until AFTER that window is closed. Not good, if you are trying to close a window you can't avoid.

    More good news, I have written a VB program that can actually show you how to get around this. Quick run down on what this VB program does. First, when you run it, there is a text box, that you need to put Access' hWnd value into. That's VERY easy to get. In code, Application.hWndAccessApp will return the current hWnd value for your Access session. Create a form, put a textbox on it, and then put in this following code behind the form:

    Private Sub Form_Open(Cancel As Integer)
    Me.Text0 = Application.hWndAccessApp
    End Sub

    Now when you open the form, that text box will have the hWnd value of the Access window, which you can copy and paste into my VB program. Now, a little info about 'windows'. Every window has an hWnd value. It is the unique identifier for that window, however it changes whenever that window is 'created', so every time you open Access, it will have a new hWnd value. (Opening, closing and opening Access will result in a new hWnd value 99.9999% of the time). That makes hWnd's useful to identify a window, but virtually useless to 'search' for a window, since a window will get a new value whenever it is opened. However, the Window Text, and Window Class are usually constant. Guess what? You are in luck when it comes to the Window Class and Access dialog windows. EVERY dialog window within Access has the same class name. (My code looks for this.). Windows also are 'stored' in the OS in a sort of 'tree', so you can see what windows 'belong' to other windows, etc.

    So what does the program do (that I am attaching to this post)? Simple, when you give it the hWnd value of an Access session, and start it's timer, it starts 'watching' for any dialog windows to appear. When a dialog window appears, it 'recreates' it in VB. Not only does it recreate the look of the dialog window (including Password character boxes), but it also let's you USE the VB 'recreation' to interact with Access. (thus, you can fill in the VB 'copy', and click the appropriate button, and the VB program will fill in the appropriate data to the REAL window, and also click the appropriate button.).

    This 'program' is actually a small portion of a project I have in the works. This project is something I have 'named' WolfWeb. WolfWeb currently let's you run any Access report through a web page. The Access report is physically run on any server (running the WolfWeb service), and then the IIS server communicates with the WolfWeb service, to run the report. Any dialog prompts are picked up by the WolfWeb service, and sent through to the IIS server, which displays them to the end user as HTML pages (that was fun to fact I think this test project has some of the code to do that.....). The User is then able to fill in the prompts, through the HTML page, and when the report is done, the WoflWeb service sends a link to the finished report (in HTML, Snapshot...etc.). It was a lot of fun, but one of the first obstacles to overcome was the actual 'watching' and interaction with Access Dialog windows.

    This project was my 'test' platform to get that working. (There are a lot of other fun techniques used to create the ASP/Web interaction, so I am not too worried about posting the source on the list here).

    So, how do you use this project for your problem? Simple, (well relatively simple... <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>) first run the project with your Access db. Make sure that it picks up the 'dialog' window that you want to 'skip' past. Make sure that it let's you click OK from the VB 'recreation'. I am 99.99% sure it will do this just fine. Then do the same thing, but this time in debug mode. Watch what the code is doing. Watch what Class it is looking for, and add a 'check' for the 'text' of the window, so that it ONLY catches that particular window. Once you have set the VB program to only catch that window, watch the code when you click the 'ok' button. Now just rig that code to fire as soon as it 'sees' that window, so that as soon as that window appears, the VB program 'bypasses' it.

    Finally, you don't want to manually put in the hWnd value for your final process. Instead, 'fill' the hWnd value in the .exe with a command line argument. Then have your Access program shell the VB program, with the hWndAccessApp value, so when the .exe starts running, it is looking for that particular window, for that particular instance of Access.

    Have I confused anyone yet?

    <img src=/S/bash.gif border=0 alt=bash width=35 height=39>

    P.S. -- Anyone else reading this, let me know what you think of my little VB 'watcher'.

    Anyhow David...sorry for the long post, but if you decide to use this 'trick' and you are having trouble using my little 'sample' project, give a holler, and I will see if I can customize this sample project to do exactly what you are looking for.

    Have fun! <img src=/S/sailing.gif border=0 alt=sailing width=25 height=25>
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts