File open dialog box not displayed

J

john.mctigue

Hello group,

I have been asked (as a non-programmer!) to develop a database of
blood cancers. As a guide I have a similar cancer registry database I
can refer to. I see this as forming the basis for what I am to
develop and have been going through it, including the code to try and
'decipher' it. Unfortunately the original developer has left the
organisation.

The 'model' database has a front end containing queries, forms and
reports and a back end containing other queries and the tables. There
is a button on the front end main switchboard with the caption 'reLink
Tables'. I am trying to understand the purpose of this button.
Having looked at the code behind this (see below) I simplistically
thought that it would open a file open dialog (line 00289) to allow
the user to locate the database backend and relink the tables, at the
user's discretion. However, the file open dialog is not displayed.
Instead I get the message "Sorry, you must locate the database backend
to open Cancer Registry." If I click this button a second time, oh
dear, on occassion (but not always) I get "Microsoft Office Access has
encountered a problem and needs to close". Something isn't quite
right, I think.

The front end and back end database are in the same directory. The
'reLink Tables' button executes a macro named linkage with the single
action RunCode and argument function name 'relinktables()'.

The following may be relevant:

Access 2003 SP3

00001 Option Compare Database
00002
00003 Option Explicit
00004
00005
00006
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
00007 '
RefreshTableLinks '
00008
'
'
00009 ' This module contains functions that refresh the
links '
00010 ' to the database tables if they aren't
available. '
00011
'
'
00012
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
00013
00014
00015 Global Const APPLICATIONTITLE = "Cancer Registry"
00016 Global Const BACKEND_DATABASE = "CaRegData.mdb"
00017
00018
00019 Type OPENFILENAME
00020 lStructSize As Long
00021 hwndOwner As Long
00022 hInstance As Long
00023 lpstrFilter As String
00024 lpstrCustomFilter As String
00025 nMaxCustomFilter As Long
00026 nFilterIndex As Long
00027 lpstrFile As String
00028 nMaxFile As String
00029 lpstrFileTitle As String
00030 nMaxFileTitle As String
00031 lpstrInitialDir As String
00032 lpstrTitle As String
00033 flags As Long
00034 nFileOffset As Integer
00035 nFileExtension As Integer
00036 lpstrDefExt As String
00037 lCustData As Long
00038 lpfnHook As Long
00039 lpTemplateName As String
00040 End Type
00041
00042 Declare Function GetOpenFileName Lib "comdlg32.dll" Alias
"GetOpenFileNameA" (pOpenFileName As OPENFILENAME) As Long
00043 '
00044

00164 Function RelinkTables() As Integer
00165
00166 ' Tries to refresh the links to the database. Returns True
if successful.
00167
00168 Dim StrFileName As String, StrError As String
00169
00170 Const MaxTables = 11
00171 Const NonExistentTable = 3011
00172 Const NotData = 3078
00173 Const NotFound = 3024
00174 Const AccessDenied = 3051
00175 Const ReadOnlyDatabase = 3027
00176 Const AppTitle = APPLICATIONTITLE
00177
00178 StrFileName = FetchDatabase()
00179 If StrFileName = "" Then
00180 DoCmd.Beep
00181 StrError = "Sorry, you must locate the database backend
to open " & AppTitle & "."
00182 GoTo Exit_Failed
00183 End If
00184
00185 ' Fix the links.
00186 If RefreshLinks(StrFileName) Then
00187 RelinkTables = True
00188 DoCmd.Beep
00189 MsgBox "Front end linked to back end database
successfully"
00190 Exit Function
00191 End If
00192
00193 ' If it failed, display an error.
00194 DoCmd.Beep
00195 Select Case Err
00196 Case NonExistentTable, NotData
00197 StrError = "File '" & StrFileName & "' does not contain
the required database tables."
00198 Case Err = NotFound
00199 StrError = "You can't run " & AppTitle & " until you
locate the database."
00200 Case Err = AccessDenied
00201 StrError = "Couldn't open " & StrFileName & " because it
is read-only or located on a read-only share."
00202 Case Err = ReadOnlyDatabase
00203 StrError = "Can't relink tables because " & AppTitle & "
is read-only or is located on a read-only share."
00204 Case Else
00205 StrError = Error
00206 End Select
00207
00208 Exit_Failed:
00209 MsgBox StrError
00210 RelinkTables = False
00211
00212 End Function
00213 '
00214

00260 Function FetchDatabase() As String
00261
00262 ' Displays the Open dialog box for the user to locate the
database.
00263 ' Returns the full path/file to database.
00264
00265 Dim File As OPENFILENAME, X As Integer, nForm As Integer
00266 Dim temp As String, SearchPath As String
00267 Dim dbs As Database
00268
00269 temp = Space(256)
00270
00271 On Error GoTo FetchDatabaseError
00272
00273 Set dbs = CurrentDb()
00274 SearchPath = ExtractDir("" & dbs.Name)
00275
00276 nForm = Forms.Count - 1
00277
00278 File.lStructSize = Len(File)
00279 File.hwndOwner = Forms(nForm).Hwnd
00280 File.lpstrFilter = "MS Access Databases [*.mdb]" &
vbNullChar & BACKEND_DATABASE & vbNullChar & vbNullChar
00281 File.lpstrFile = BACKEND_DATABASE & Space(255 -
Len(BACKEND_DATABASE))
00282 File.nMaxFile = 255
00283 File.lpstrFileTitle = Space(255)
00284 File.nMaxFileTitle = 255
00285 File.lpstrInitialDir = SearchPath
00286 File.lpstrTitle = "Database Backend Data File Location"
00287 File.flags = 0
00288
00289 X = GetOpenFileName(File)
00290 If X = 0 Then Exit Function ' Abort on error or Cancel
00291
00292 ' Extract the filename
00293 temp = Trim(File.lpstrFile)
00294 FetchDatabase = Left(temp, Len(temp) - 1) ' Trim ending
vbNullChar
00295
00296 Exit Function
00297
00298 FetchDatabaseError:
00299 MsgBox "Error :" & Error
00300 Exit Function
00301
00302 End Function
00303 '
00304
00305 Function ExtractDir(StrIn As String) As String
00306
00307 Dim temp As String, I As Integer
00308
00309 temp = StrIn
00310
00311 For I = Len(StrIn) To 1 Step -1
00312 If Len(temp) <= 3 Then Exit For
00313
00314 If Mid(temp, I, 1) = "\" Then
00315 temp = Left(temp, I - 1)
00316 Exit For
00317 End If
00318 Next
00319
00320 ExtractDir = temp
00321
00322 End Function
00323 '
 
A

Allen Browne

Hi John

If the front end and back end will always be in the same folder, you could
use this code for the relinking:
http://allenbrowne.com/ser-13.html

If you need something more, Microsoft's solutions.mdb contains a module that
pops up the File Open dialog for the user to select the back end:
http://support.microsoft.com/kb/248674

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hello group,

I have been asked (as a non-programmer!) to develop a database of
blood cancers. As a guide I have a similar cancer registry database I
can refer to. I see this as forming the basis for what I am to
develop and have been going through it, including the code to try and
'decipher' it. Unfortunately the original developer has left the
organisation.

The 'model' database has a front end containing queries, forms and
reports and a back end containing other queries and the tables. There
is a button on the front end main switchboard with the caption 'reLink
Tables'. I am trying to understand the purpose of this button.
Having looked at the code behind this (see below) I simplistically
thought that it would open a file open dialog (line 00289) to allow
the user to locate the database backend and relink the tables, at the
user's discretion. However, the file open dialog is not displayed.
Instead I get the message "Sorry, you must locate the database backend
to open Cancer Registry." If I click this button a second time, oh
dear, on occassion (but not always) I get "Microsoft Office Access has
encountered a problem and needs to close". Something isn't quite
right, I think.

The front end and back end database are in the same directory. The
'reLink Tables' button executes a macro named linkage with the single
action RunCode and argument function name 'relinktables()'.

The following may be relevant:

Access 2003 SP3

00001 Option Compare Database
00002
00003 Option Explicit
00004
00005
00006
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
00007 '
RefreshTableLinks '
00008
'
'
00009 ' This module contains functions that refresh the
links '
00010 ' to the database tables if they aren't
available. '
00011
'
'
00012
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
00013
00014
00015 Global Const APPLICATIONTITLE = "Cancer Registry"
00016 Global Const BACKEND_DATABASE = "CaRegData.mdb"
00017
00018
00019 Type OPENFILENAME
00020 lStructSize As Long
00021 hwndOwner As Long
00022 hInstance As Long
00023 lpstrFilter As String
00024 lpstrCustomFilter As String
00025 nMaxCustomFilter As Long
00026 nFilterIndex As Long
00027 lpstrFile As String
00028 nMaxFile As String
00029 lpstrFileTitle As String
00030 nMaxFileTitle As String
00031 lpstrInitialDir As String
00032 lpstrTitle As String
00033 flags As Long
00034 nFileOffset As Integer
00035 nFileExtension As Integer
00036 lpstrDefExt As String
00037 lCustData As Long
00038 lpfnHook As Long
00039 lpTemplateName As String
00040 End Type
00041
00042 Declare Function GetOpenFileName Lib "comdlg32.dll" Alias
"GetOpenFileNameA" (pOpenFileName As OPENFILENAME) As Long
00043 '
00044

00164 Function RelinkTables() As Integer
00165
00166 ' Tries to refresh the links to the database. Returns True
if successful.
00167
00168 Dim StrFileName As String, StrError As String
00169
00170 Const MaxTables = 11
00171 Const NonExistentTable = 3011
00172 Const NotData = 3078
00173 Const NotFound = 3024
00174 Const AccessDenied = 3051
00175 Const ReadOnlyDatabase = 3027
00176 Const AppTitle = APPLICATIONTITLE
00177
00178 StrFileName = FetchDatabase()
00179 If StrFileName = "" Then
00180 DoCmd.Beep
00181 StrError = "Sorry, you must locate the database backend
to open " & AppTitle & "."
00182 GoTo Exit_Failed
00183 End If
00184
00185 ' Fix the links.
00186 If RefreshLinks(StrFileName) Then
00187 RelinkTables = True
00188 DoCmd.Beep
00189 MsgBox "Front end linked to back end database
successfully"
00190 Exit Function
00191 End If
00192
00193 ' If it failed, display an error.
00194 DoCmd.Beep
00195 Select Case Err
00196 Case NonExistentTable, NotData
00197 StrError = "File '" & StrFileName & "' does not contain
the required database tables."
00198 Case Err = NotFound
00199 StrError = "You can't run " & AppTitle & " until you
locate the database."
00200 Case Err = AccessDenied
00201 StrError = "Couldn't open " & StrFileName & " because it
is read-only or located on a read-only share."
00202 Case Err = ReadOnlyDatabase
00203 StrError = "Can't relink tables because " & AppTitle & "
is read-only or is located on a read-only share."
00204 Case Else
00205 StrError = Error
00206 End Select
00207
00208 Exit_Failed:
00209 MsgBox StrError
00210 RelinkTables = False
00211
00212 End Function
00213 '
00214

00260 Function FetchDatabase() As String
00261
00262 ' Displays the Open dialog box for the user to locate the
database.
00263 ' Returns the full path/file to database.
00264
00265 Dim File As OPENFILENAME, X As Integer, nForm As Integer
00266 Dim temp As String, SearchPath As String
00267 Dim dbs As Database
00268
00269 temp = Space(256)
00270
00271 On Error GoTo FetchDatabaseError
00272
00273 Set dbs = CurrentDb()
00274 SearchPath = ExtractDir("" & dbs.Name)
00275
00276 nForm = Forms.Count - 1
00277
00278 File.lStructSize = Len(File)
00279 File.hwndOwner = Forms(nForm).Hwnd
00280 File.lpstrFilter = "MS Access Databases [*.mdb]" &
vbNullChar & BACKEND_DATABASE & vbNullChar & vbNullChar
00281 File.lpstrFile = BACKEND_DATABASE & Space(255 -
Len(BACKEND_DATABASE))
00282 File.nMaxFile = 255
00283 File.lpstrFileTitle = Space(255)
00284 File.nMaxFileTitle = 255
00285 File.lpstrInitialDir = SearchPath
00286 File.lpstrTitle = "Database Backend Data File Location"
00287 File.flags = 0
00288
00289 X = GetOpenFileName(File)
00290 If X = 0 Then Exit Function ' Abort on error or Cancel
00291
00292 ' Extract the filename
00293 temp = Trim(File.lpstrFile)
00294 FetchDatabase = Left(temp, Len(temp) - 1) ' Trim ending
vbNullChar
00295
00296 Exit Function
00297
00298 FetchDatabaseError:
00299 MsgBox "Error :" & Error
00300 Exit Function
00301
00302 End Function
00303 '
00304
00305 Function ExtractDir(StrIn As String) As String
00306
00307 Dim temp As String, I As Integer
00308
00309 temp = StrIn
00310
00311 For I = Len(StrIn) To 1 Step -1
00312 If Len(temp) <= 3 Then Exit For
00313
00314 If Mid(temp, I, 1) = "\" Then
00315 temp = Left(temp, I - 1)
00316 Exit For
00317 End If
00318 Next
00319
00320 ExtractDir = temp
00321
00322 End Function
00323 '
Click to expand...
 
A

Albert D. Kallal

I am trying to understand the purpose of this button.
Click to expand...

It sounds like the code is attempting to get the path name to a database
that the user selects

The line of code:

00178 StrFileName = FetchDatabase()

it seems to me that there's a function likely called FetchDatabase() in
application. That function ill place/return the value chosen by the user
into strFileName. However if your successfully linked to a backend database
already, then when you press the button it might assume that nothing's
supposed to happen.

You could test this by making a copy of both the front end and back and into
a different directory, and then running the code or running the button and
see what happens. (but remember this means that the button was still think
the link is OK, because the front end is in fact pointing to the Reginald
backend in the other directory, and the fact that you make a test to move
this will not change this fact!!!).

You could also go into the code editor window and type in the following in
the debug window

? FetchDatabase()

it's just a bit of an educated guess, but the above if typed in should
launch the file open/browse dialog....

I am somewhat guessing here, but I think that buttion when pressed does
NOTHING if the link is ok, and ONLY pops up a file dialogue if there's a
problem.

Having looked at the code behind this (see below) I simplistically
thought that it would open a file open dialog (line 00289) to allow
the user to locate the database backend and relink the tables, at the
user's discretion.
Click to expand...

I think you're assuming is correct, however it also looks like if the links
are currently correct and operational, then I believe nothing will happen.

I would try moving the backend on purpose to some other location, and then
launching the application and pressing the button to see what happens.
Remember making a copy of both into another directory and clicking on the
button to test it is an incorrect test, since the front end is still
correctly and linked to the original location where the regional backend
is....
 
J

john.mctigue

Hi John

If the front end and back end will always be in the same folder, you could
use this code for the relinking:
   http://allenbrowne.com/ser-13.html

If you need something more, Microsoft's solutions.mdb contains a module that
pops up the File Open dialog for the user to select the back end:
   http://support.microsoft.com/kb/248674
Click to expand...
Thank you, Allen, for the quick response.

I will investigate your code. By the way, the knowledgebase article
you cite points to http://msdn2.microsoft.com/en-us/library/aa188219.aspx
but unfortunately the download icon on that page doesn't work!

Enjoy the rain if we get any this weekend.

KInd regards,
John
 
J

john.mctigue

It sounds like the code is attempting to get the path name to a database
that the user selects

The line of code:

00178      StrFileName = FetchDatabase()

it seems to me that there's a function likely called FetchDatabase() in
application. That function ill place/return the value chosen by the user
into strFileName. However if your successfully linked to a backend database
already, then when you press the button it might assume that nothing's
supposed to happen.

You could test this by making a copy of both the front end and back and into
a different directory, and then running the code or running the button and
see what happens. (but remember this means that the button was still think
the link is OK, because the front end is in fact pointing to the Reginald
backend in the other directory, and the fact that you make a test to move
this will not change this fact!!!).

You could also go into the code editor window and type in the following in
the debug window

?  FetchDatabase()

it's just a bit of an educated guess, but the above if typed in should
launch the file open/browse dialog....

I am somewhat guessing here, but I think that buttion when pressed does
NOTHING if the link is ok, and ONLY pops up a file dialogue if there's a
problem.


I think you're assuming is correct, however it also looks like if the links
are currently correct and operational, then I believe nothing will happen.

I would try moving the backend on purpose to some other location, and then
launching the application and pressing the button to see what happens.
Remember making a copy of both into another directory and clicking on the
button to test it is an incorrect test, since the front end is still
correctly and linked to the original location where the regional backend
is....
Click to expand...

Thank you for the prompt response, Albert.

I tried moving the database front end and back end as you suggested.
Essentially there was no difference in behaviour ie same message
displayed on first attempt at relinking and usually the database
crashed at the second attempt.

Calling FetchDatabase() from the Immediate window did not display a
dialog box, but moved the cursor in the window on two lines which I
assume was due to the return of an empty string.

Alas, I think something may be very amiss with the code or with other
corruption and this is one button I will avoid pressing! I may,
however, try copying the code to a 'clean' database and see if it
behaves any better there, but it will have to wait for the moment.

Kind regards,
John
 
T

Tom Wickerath

Hi John,

There appears to be a problem with the File variable. I copied your code
into a new Access app., and created one form and opened it (your code
requires an open form). I am consistently getting the result X=0 at the line
of code that reads:

X = GetOpenFileName(File)

The next line of code causes the FetchDatabase function to return a zero
length string to the RelinkTables function, with the message box thrown up as
a result.

If you are willing to send me a compacted and zipped copy of your database,
I'm certainly willing to help out more, since this looks like a very worthy
project. It's also personal for me, since my father died of complications due
to Hodgkins Lymphoma when I was only 16-years-old.

If you'd like to take me up on my offer, you can find my e-mail address at
the bottom of the Contributor's page indicated in my signature, below.

By the way, it is not a good idea to expose your e-mail address in any
postings to a newsgroup. Doing so will only attract the unwanted attention of
spammers.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
 
D

duanemoore

J

john.mctigue

Hi John,

There appears to be a problem with the File variable. I copied your code
into a new Access app., and created one form and opened it (your code
requires an open form). I am consistently getting the result X=0 at the line
of code that reads:

        X = GetOpenFileName(File)

The next line of code causes the FetchDatabase function to return a zero
length string to the RelinkTables function, with the message box thrown upas
a result.

If you are willing to send me a compacted and zipped copy of your database,
I'm certainly willing to help out more, since this looks like a very worthy
project. It's also personal for me, since my father died of complications due
to Hodgkins Lymphoma when I was only 16-years-old.

If you'd like to take me up on my offer, you can find my e-mail address at
the bottom of the Contributor's page indicated in my signature, below.

By the way, it is not a good idea to expose your e-mail address in any
postings to a newsgroup. Doing so will only attract the unwanted attentionof
spammers.

Tom Wickerath
Microsoft Access MVPhttp://www.accessmvp.com/TWickerath/http://www.access.qbuilt.com/html/expert_contributors.html
Click to expand...

Hi Tom,

Thank you for taking the time to reply. I appreciate the offer of
help.

I remained dissatisified with not getting to the root of the problem
so I returned to the code. Commenting out lines 00278 to 00287 and
then uncommenting them one at a time narrowed the problem down to line
00281. Googling lpsrtrFile and looking at some example code led me to
believe that the string should be 254 characters, not 255. I amended
line 00281 accordingly and things seemed to work OK.

I still don't know if I have 'solved' the problem or merely applied a
sticking plaster though! All this API stuff gives me a headache...
Does anyone have plain english guide to the getopenfilename function?

Kind regards,
John
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Closing programs on remote PC - help please 1
It's working by hand, but not in Access VBA code 0
export vba not working 0
use vba to open pdf then save as xml 6
upgrade a program to Access 2003 6
Table Linking Code Problem 9
Editing saved UID and PWD through relinking ODBC tables 0
Error number: 3326 0

Top