CommonDialog not working with Access runtime

[tommeekers]

I found some code that makes my application check the link to the backend database at startup. When the link is broken, it displays a pop-up form where I can specify the location of the database file. This pop-up form contains a CommonDialog control to browse to the file.
I'm running the Access developer edition and the code works fine for me. However, user running the Access Runtime version get an error when they try to browse:

[tt]There is no object in this control[/tt]

Public Function Browse()
On Error GoTo Err_Browse

    Dim strFilename As String
    Dim oDialog As Object
    Set oDialog = [Forms]![frm_connectie]!xDialog.Object

    With oDialog
        .DialogTitle = "Select database file"
        .Filter = "Access Database (*.mdb;*.mda;*.mde;*.mdw)|" & _
        "*.mdb; *.mda; *.mde; *.mdw|Alle bestanden (*.*)|*.*"
        .FilterIndex = 1
        .ShowOpen
        If Len(.FileName) > 0 Then
           [Forms]![frm_connectie]![txtFileName] = .FileName
        End If
    End With

Exit_Browse:
    Exit Function
Err_Browse:
    Msgbox Err.Description
    Resume Exit_Browse
End Function

Anyone know how to fix this ?

 

[RoyVidar]

Probably the distribution of the ocx, dll or whatever it is called, which I don't remember anymore ... it would need to be in the install package...

What is usually recommended, is to use API, for instance check out this ready made wrapper

http://www.mvps.org/access/api/api0001.htm

Roy-Vidar

 

[ssatech]

Have you referenced: Microsoft Scripting Runtime in your application?

 

[Troyston]

TomTom,

Scrap the common dialog control and go with the API from Dev Asish Access Web on <

http://www.MVPS.org/access>

It's simple, standard and requires no controls, you can slip it into a module and call it from wherever passing headers, file types, etc etc...

Troyston

Troy Vinson
Trading as IT Supportman

http://www.itsupportman.co.uk

 

[tommeekers]

I tried API, which seems to be working. But how do I get it to return the location of the file instead of opening the file ? I need it to put the location in my textfield so that I can use this to relink my tables.

Also I want the filetype to be more than 1 option. In this example I can only choose filetype Access database (*.mdb;*.mda;*.mde;*.mdw). But I also want All files (*.*) to be an option.

 

[tommeekers]

Nevermind my last post. I didn't see there was an example included on that webpage. I figured it out now.

Just have to test it with the Access runtime now.
Thx for the help.

 

[tommeekers]

[tt]Execution of this application has stopped due to a run-time error.
The application can't continue and will be shut down.[/tt]

Still no luck with the Access runtime version.

 

[RoyVidar]

I'm not sure I'd blame this code for the error. As far as I know, it's been used successfully on all versions since the year it was written (94/95?). Just did a quick test here too (haven't used runtimes since 1998, if I recall correct ;-) ), and it worked.

Can you isolate the error? Could there be some corruption (try the /deompile option on a copy - see

http://www.granite.ab.ca/access/decompile.htm

for step by step instructions)? Or something else?

Roy-Vidar

 

[tommeekers]

I tried the /decompile option, but that didn't solve the problem. Compiling did return a few minor errors in my code, but I fixed all of them and still my problem isn't solved.

I have no idea how to isolate the error. All I can tell is that it doesn't even seem to get as far as the Form_Load code at my startup form. So something must be going wrong before it even opens a form. Any ideas ?

 

[tommeekers]

Ok, here's the code that gets called at startup. The error does seem to be in here somewhere, cause when I comment all if this code except for the DoCmd.OpenForm "frm_connectie" it does open the connection form without problems. Looks like it already returns an error on the very first line of this code.

Private Sub Form_Open(Cancel As Integer)
On Error GoTo Err_Form_Open

      Set db = CurrentDb
      For Each td In db.TableDefs
         If Len(td.Connect) > 0 Then
            strTest = Dir(Mid(td.Connect, 11))
            If Len(strTest) = 0 Then
               DoCmd.OpenForm "frm_connectie"
               DoCmd.Close acForm, Me.Name
               Exit Sub  
            End If
         End If
      Next
      DoCmd.Close acForm, Me.Name
      stDocName = "frm_index"
      DoCmd.OpenForm stDocName, , , stLinkCirteria
      
Exit_Form_Open:
    Exit Sub
Err_Form_Open:
    Msgbox Err.Description
    Resume Exit_Form_Open
End Sub

 

[tommeekers]

Now I'm confused ... I compiled the exact same application that keeps returning the error into a MDE and it runs just fine with the Access Runtime ?!
I thought an MDE was just a compiled and encrypted version of the MDB ? If I just compile the MDB and try to run it on the machine with the runtime version, it still returns the error. So what's the difference between a compiled MDB and a MDE, except for the VBA code being invisible ?

 

[Troyston]

There are 2 close me's, not sure if you need the first one.

About unidentifiable fatal errors:

Try checking that all required references are in place, from memory this can be fatal.

Alternatively, the fatal error could be elsewhere?

Troyston

Troy Vinson
Trading as IT Supportman

http://www.itsupportman.co.uk

 

[RoyVidar]

Yes, references, could perhaps have something to do with it, but I don't understand this. I would expect the mdb and mde to behave similar, and perhaps that the mde failed, and the mdb not. So, the below comments and ideas might not relate to what might be the actual error at all, but hopefully assist in troubleshooting.

What's your errorhandling set to? In VBE - Tools | Options - Generalt tab -> should be "Break on Unhandled Errors".

Some other comments, none of the variables used are declared within the sub. I'd expect

[tt] dim db as dao.database
dim td as dao.tabledef
dim strTest as string[/tt]

Through the years, there's beens some errors with the DAO object model, which makes some recommend to explicitly close whatever is opened, then also release explicitly. Here, nothing is opened, but neither td nor db is released

[tt] set td = nothing
set db = nothing[/tt]

You told us about a couple of compile errors. Now - I hope you have the line

[tt]Option Explicit[/tt]

at the top of each module. This will enforce variable declaration, and is usually recommended to avoid strange errors caused by typing errors, scope ...

I think the main difference between mdb and mde with regards to code, isn't that the code isn't visible, the textual representation of the code simply isn't there. The mde only contains the compiled code.

Now, the next might really sound like "mumbo jumbo", but sometimes it seems Access remembers stuff you've deleted. (I assume you've deleted the Common Dialog thingie, and removed references, if there where any) So, say there really was an error with this common dialog thingie, sometimes it might seem the form or code might remember, and error out on it even if it's not there. Two ways of ensuring (or trying to) this won't happen, is either recreate the form from scratch (you can copy paste a lot, but really need to recreate it) or try the hidden methods saveastext/loadfromtext on it.

This method can also be used to resolve form code corruption, as it completely recreates the form. Be sure to debug | compile and save afterwards.

[tt]application.saveastext acform, "frmYourForm", "c:\frmYourForm.txt"
' then load it again with
application.loadfromtext acform, "frmYourForm.txt", "c:\frmYourForm"[/tt]

Are you testing these on the same machine, or different machines? If it's on different machines, could you try testing other apps on the machine where it fails. Stuff similar code as the above in there, and try both as mdb, mde both alone and under the runtime. Perhaps theres something wrong on the computer?

Roy-Vidar

 

[Troyston]

That release thing is a pain and Roy is right, it's best to close everything you can think of even if it wasn't open in the first place.

I use this at the end of most sub just for compelteness really:

Set dbs = DBEngine(0)(0)
For Each r In dbs.Recordsets
r.Close
Next r
Set dbs = Nothing
DoCmd.Hourglass False
Application.Echo True
SysMsg = SysCmd(acSysCmdClearStatus)
Exit Sub

Otherwise my money's on the missing reference.

Troyston

Troy Vinson
Trading as IT Supportman

http://www.itsupportman.co.uk