Old Access App

I've got a database created in Access 97 which just runs a bunch of action queries, I have brought it up to 2013 with what I know, but I still face one difficulty. The application which is designed to split off a database at a specified date to avoid overgrowth and archive previous years is functioning well at this point except for the button that is supposed to open up the navigation dialogue to locate the database i. It gives the error in the attached image. Same thing happens if I try to run the Code through a Macro. If I try stepping through the "Function SplitDirBtn()" I get the attached compile error – and the code is below.

Any input is appreciated.

Function SplitDirBtn()
' Called from Forms!Settings!SplitDirBtn
Const PROCNAME = "SplitDirBtn"
Dim TblSpec As String, sPath As String, sAppName As String
Dim iPos As Integer, i As Integer
Dim f As Form
On Error GoTo SplitDirBtn_Err
   Set f = Forms!Settings

  'TblSpec = GetAccdbName()
   TblSpec = FindMyAccdb("")
  'Debug.Print "TblSpec=" & TblSpec & "]" & Len(TblSpec)

   iPos = InStr(TblSpec, "CLERKTBL.ACCDB")
   If iPos < 4 Then
     'MsgBox "Accdb file not selected", 48, PROCNAME
      Exit Function
   End If
   sPath = Left$(Trim$(TblSpec), iPos = 2)
   sPath = Mid$(TblSpec, 1, iPos - 2)
   sPath = Proper(sPath)

   f!SplitDir = sPath

   For i = 1 To Len(sPath)
      If Mid$(sPath, i, 1) = "\" Then iPos = i
   f!PublicDir = Left$(sPath, iPos - 1)

   On Error Resume Next
   Exit Function
   MsgBox Error & " Err# " & Err, 16, PROCNAME
   Resume SplitDirBtn_Exit
End Function

Private Function StringFromSz(szTmp As String) As String
'  If string terminates with nulls, return a truncated string.
Dim ich As Integer, sTmp As String
   szTmp = Trim$(szTmp)
   ich = InStr(szTmp, Chr$(0))
   If ich <> 0 Then
      sTmp = Mid$(szTmp, 1, ich - 1)
      sTmp = szTmp
   End If
  'Debug.Print "ich=" & ich & " sTmp=" & sTmp & "]" & Len(sTmp)
   StringFromSz = sTmp
   If Len(sTmp) > 100 Then
      ' Breakpoint
   End If
End Function

Open in new window

dawber39Database Analyst / Application DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dawber39Database Analyst / Application DeveloperAuthor Commented:
This is the highlighted line with the compile error - so I think i  am missing a reference

Private Function GetAccdbName2(gfni As wlib_GetFileNameInfo, ByVal fOpen As Integer) As Long
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
You can replace the old function for getting the MDB name with the code here:


 The Office file dialog is now built into Office, which you can call to retrieve the file name.

dawber39Database Analyst / Application DeveloperAuthor Commented:
Do you know where I can get the 11.0 object library to use that code?
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
You don't need the 11 lib (that's 2007).   Just look for the current object lib, which for you should be 13.0.

The dialog is still part of Office.

dawber39Database Analyst / Application DeveloperAuthor Commented:
That would be 15.0 - but its still not functioning correctly. Maybe a manual ref to 15.0
Helen FeddemaCommented:
This is the Office object library, which for some reason is often not on the list of libraries, so you may have to browse for it.  Here is where it is on my system:

Office 2013 reference

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dawber39Database Analyst / Application DeveloperAuthor Commented:
As always - Experts up here either get me there or point me in the right direction. You people are awesome
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.