Linked tables

[tkaplan]

I have a database that is really just a bunch of reports based off of one excel sheet. I am using the excel sheet as a linked table.
My problem is that there is going to be a number of copies of this database in use, and i want the user to be able to say where this excel sheet is located, but not every time the database starts up, only when they change location.

the database is not opening up with the database window displaying so i cant teach them how to go to the properties and linked table manager.

what can i do that will detect if the database cant find the table, the linked table manager will open up automatically?

 

[llkhoutx]

Hold the shift key down at Access startup to have the database window displayed, unless the by pass has been programmatically disabled.

If disable, create a blank database and import all objects and menus. The database window should them be visible.

 

[tkaplan]

i want it to pop up automatically. I know how to open it in design mode but my users dont, and i dont want to teach them.

i am trying to figure out a way to make it come up on it's own and prompt the user.

 

[llkhoutx]

By definition, someone has to know where the Excel file is located. That information has to be made available to all.

Build a CommonDialog which with the click of a button allows your users to navigate to and select the requisite file.

If only the name of the file is known, one could write code to search all possible folders of the file. The link in.

Search this site for commonDialog, there's code for it probaby posted. The code's significant for A2k and below. I've seen A03 code which is relatively simple; see Microsoft Knowledge Base Article No. 198974.

 

[tkaplan]

to open the find file dialog box, i know how to do.
here's what i dont know how to do:

when user opens access database, access is looking for the excel file. if access does not find it, set off code which will do whatever. (the whatever part i know how to do, i dont know how to write for the condition on if excel cannot find the file.)

one option is for me to say of the linked table is that it is always in the source data file is always in the same directory as the database.
is there any way to say this???

 

[llkhoutx]

When Access opens, check to see if the Excel file is properly linked. If not, open the CommonDialog to allow the user to navigate to the requisite file. When the file has been selected, link it and proceed.

Just imagine the necessary steps and code them.

 

[tkaplan]

When Access opens, check to see if the Excel file is properly linked. If not, open the CommonDialog to allow the user to navigate to the requisite file. When the file has been selected, link it and proceed.

Just imagine the necessary steps and code them.

this is exactly what i am asking for help with. How do i code to check if the excel file is linked?

 

[nateobot]

You could combine these two great functions(below) from Dev Ashish:

Like so

Dim iLinkIsGood as Integer
iLinkIsGood = fIsFileDIR(fGetLinkPath("PUT YOUR TABLE NAME HERE"))
If iLinkIsGood = 0 Then
  'Not good do your prompting here
End If

'***************** Code Start *******************
'This code was originally written by Dev Ashish
'It is not to be altered or distributed,
'except as part of an application.
'You are free to use it in any application,
'provided the copyright notice is left unchanged.
'
'Code Courtesy of
'Dev Ashish
'
Function fIsFileDIR(stPath As String, _
                    Optional lngType As Long) _
                    As Integer
    On Error Resume Next
    fIsFileDIR = Len(Dir(stPath, lngType)) > 0
End Function
'***************** Code End *********************


'*************** Code Start **************
' This code was originally written by Dev Ashish.
' It is not to be altered or distributed,
' except as part of an application.
' You are free to use it in any application,
' provided the copyright notice is left unchanged.
'
' Code Courtesy of
' Dev Ashish
'
'
Function fGetLinkPath(strTable As String) As String
Dim dbs As Database, stPath As String

    Set dbs = CurrentDb()
    On Error Resume Next
    stPath = dbs.TableDefs(strTable).Connect
    If stPath = "" Then
        fGetLinkPath = vbNullString
        'can change this to currentdb.name
    Else
        fGetLinkPath = Right(stPath, Len(stPath) _
                        - (InStr(1, stPath, "DATABASE=") + 8))
    End If
    Set dbs = Nothing
End Function

Sub sListPath()
    Dim loTd As TableDef
    CurrentDb.TableDefs.Refresh
    For Each loTd In CurrentDb.TableDefs
        Debug.Print fGetLinkPath(loTd.Name)
    Next loTd
    Set loTd = Nothing
End Sub

'*************** Code End **************

 

[alastair69]

When Access opens, check to see if the Excel file is properly linked. If not, open the CommonDialog to allow the user to navigate to the requisite file. When the file has been selected, link it and proceed.

Just imagine the necessary steps and code them.

**********************************************************
That is not helpful to other users, whom are tring to get answers in this forum, you are acting as if you do not know the answer to the question. If this is the case you should not have voiced an opinion.

Alastair