Checking Linked Tables via VBA (1 Viewer)

[cstickman]

Hello Everyone,

So I have a project where I wrote a macro and saved it as AutoExec. I want it to run a few items before the database opens. The first part of the macro is to check if the user has macros enabled so the code works. The second part is to check if the user has permissions to the database. The final step is where I need the help. I searched Google and found numerous ways to do this and I picked this one as it was recommended, but I am getting an error. So on the macro I have it run code to the function and below is the function

 Public Function IsLink(linktable As String) As Boolean
 IsLinked = Nz(DLookup("Type", "MSysObjects", _
"Name='" & linktable & "'"), 0) <> 1
 If IsLinked = True Then
Exit Function
 Else
    DoCmd.OpenReport "mapp"
 End If
 End Function

It says the function has to many arguments and error 2439. All I am trying to accomplish is check for the table which is named linktable and if it is active then end the function and the macro will open a start up form. If it cannot locate the link then I want a form called "mapp" to open. That will let the user know that the network drive is unavailable and will close the database. It also gives them an option to run an external program. The external program is a BAT file to map the drives when the user logins.

Any help or suggestions? I am also using Access 2013 with reference to the 15.0 object library. Thanks!!

 

[sneuberg]

The following is from the relinker (basJStreetAccessRelinker) we use. It has the advantage of not using the systems tables.

Private Function TableLinkOkay(strTableName As String) As Boolean
'Function accepts a table name and tests first to determine if linked
'table, then tests link by performing refresh link.
'Error causes TableLinkOkay = False, else TableLinkOkay = True
    Dim CurDB As DAO.Database
    Dim tdf As TableDef
    Dim strFieldName As String
    On Error GoTo TableLinkOkayError
    Set CurDB = DBEngine.Workspaces(0).Databases(0)
    Set tdf = CurDB.TableDefs(strTableName)
    TableLinkOkay = True
    If tdf.Connect <> "" Then
        '#BGC updated to be more thorough in checking the link by opening a recordset
        'ACS 10/31/2013 Added brackets to support spaces in table and field names
        strFieldName = CurDB.OpenRecordset("SELECT TOP 1 [" & tdf.Fields(0).Name & "] FROM [" & tdf.Name & "];", dbOpenSnapshot, dbReadOnly).Fields(0).Name  'Do not test if nonlinked table
    End If
    TableLinkOkay = True
TableLinkOkayExit:
    Exit Function
TableLinkOkayError:
    TableLinkOkay = False
    GoTo TableLinkOkayExit
End Function

 

[cstickman]

Thanks Sneuberg for the code. I have two questions:
1. Where do I define the table name?
2. Do I need to add another reference since I am on 2013 and you are using a DAO recordset?

Thanks!!

 

[sneuberg]

DAO is covered by one of the default references in Access 2013 (See attached)

I don't know what you mean by define the table. If you want to use this function just put it in the same module or make it public and put it in another. To use it in your code change it as follows:

Public Function IsLink(linktable As String) As Boolean

If TableLinkOkay(linktable) = True Then
     Exit Function
Else
     DoCmd.OpenReport "mapp"
End If

End Function

 

[cstickman]

sneuberg,

Ok so I copied over your function and changed my function to what you suggested. In my macro I have it to run code and I run my macro and I am still getting the same error. Too many arguments within the function.

 

[sneuberg]

What function is it complaining about?

if your table is named "linktable" then I would guess the runcode line should have

IsLink("linktable")

Could you upload a screen shot of your macro?

 

[cstickman]

I attached the images for your review. Thanks for your help!!

 

[sneuberg]

The IsLink function requires a table name. So if the linked table is named linktable then the function call in the macro should look like:

IsLink("linktable")

 

[cstickman]

sneuberg

Thanks!! That worked great!! I did have to change my function a little bit, but with your function and mine it works like a charm!! Here is my code for reference for others if they are trying to accomplish the same thing.

 Public Function IsLink(sTableName As String) As Boolean
 If TableLinkOkay("linktable") = True Then
Exit Function
 Else
    DoCmd.OpenForm "mapp"
 End If
 End Function

Thanks again for all of your help!!

 

[sneuberg]

sneuberg

Thanks!! That worked great!! I did have to change my function a little bit, but with your function and mine it works like a charm!! Here is my code for reference for others if they are trying to accomplish the same thing.

 Public Function IsLink(sTableName As String) As Boolean
 If TableLinkOkay("linktable") = True Then
Exit Function
 Else
    DoCmd.OpenForm "mapp"
 End If
 End Function

Thanks again for all of your help!!

If you are going to do it that way, which is fine, you should take the parameter out of the IsLink function as it is doing absolutely nothing.

The code would look like:

Public Function IsLink() 

 If TableLinkOkay("linktable") = True Then
      Exit Function
 Else
     DoCmd.OpenForm "mapp"
 End If

 End Function

or

Public Function IsLink() 

If TableLinkOkay("linktable") = False Then
     DoCmd.OpenForm "mapp"
End If

End Function

And the call in the macro would be back to

IsLink()

 

[sneuberg]

Also I suggest watching the videos at

https://www.youtube.com/watch?v=AefCUyzqElM&index=26&list=PLYMOUCVo86jEeMMdaaq03jQ_t9nFV737s

I think in the long run you'll save time in the future.

 

[cstickman]

Thanks for all of your help. I will make the change and thanks for the link to the videos. I have actually watched numerous of his videos and use some in my past projects.