How to use this funtion (1 Viewer)

[sandanet]

I would say the clue is in the error message. Maybe focus on your syntax and see if you're using Table2 in the right argument.

I'm a pretty sure about the syntax because it works fine on access2007

 

[theDBguy]

the code of mine is posted above, the database located here strPath = Application.CurrentProject.Path & "\idcard.mdb"

What does your code look like for IsTable()?

 

[theDBguy]

I'm a pretty sure about the syntax because it works fine on access2007

Function syntax doesn't usually change between versions. So, if the same syntax doesn't work in one version but does on another, then what you want to check is the environment. For example, the error message specifically mentioned "Table2," try and see if there's something different about Table2 between 2007 and 2010.

 

[sandanet]

What does your code look like for IsTable()?

this function is just to check if the table is existing or no

 

[theDBguy]

this function is just to check if the table is existing or no

Can we still see it please?

 

[sandanet]

Function syntax doesn't usually change between versions. So, if the same syntax doesn't work in one version but does on another, then what you want to check is the environment. For example, the error message specifically mentioned "Table2," try and see if there's something different about Table2 between 2007 and 2010.

I don't know what's wrong .. as u c the table2 is existing in the second database

 

[sandanet]

Can we still see it please?

sure

Option Compare Database
Option Explicit

Public Function IsTable(sTblName As String) As Boolean

'does table exists and work ?
'note: finding the name in the TableDefs collection is not enough,
' since the backend might be invalid or missing

On Error GoTo hell
Dim x
x = DCount("*", sTblName)
IsTable = True
Exit Function
hell:
Debug.Print Now, sTblName, Err.Number, Err.Description
IsTable = False
'MsgBox "" & IsTable

End Function

 

[theDBguy]

I don't know what's wrong .. as u c the table2 is existing in the second database View attachment 86758

If you step through the code, which line is getting highlighted when you debug?

 

[theDBguy]

sure

Option Compare Database
Option Explicit

Public Function IsTable(sTblName As String) As Boolean

'does table exists and work ?
'note: finding the name in the TableDefs collection is not enough,
' since the backend might be invalid or missing

On Error GoTo hell
Dim x
x = DCount("*", sTblName)
IsTable = True
Exit Function
hell:
Debug.Print Now, sTblName, Err.Number, Err.Description
IsTable = False
'MsgBox "" & IsTable

End Function

Thanks. Since I am not familiar with your database and cannot see what you're looking at, we need to ask for clarifications and verify places where we would check anyway.

For example, the linking code you posted is supposed to create a linked table for all the tables in the BE, correct? If so, why are you specifically checking for Table2?

And since the error message mentions the same table, I suspect the problem has something to do with it.

 

[arnelgp]

you may try also this modified version of your CreateLinkedTable() function:
to use it:


Call CreateLinkedTable("Table2", "Table2", Application.CurrentProject.Path & "\idcard.mdb")

Public Function CreateLinkedTable(ByVal strLinkedTableName As String, _
ByVal strSourceTable As String, _
ByVal strSourceDatabase As String) As Boolean

Dim rs As DAO.Recordset, ok As Boolean
Dim db As DAO.Database
Dim td As DAO.TableDef
Set db = CurrentDb
'check if source database exists
ok = (Len(Dir$(strSourceDatabase)) > 0)
If ok Then
    'check if table exists
    On Error Resume Next
    Set rs = db.OpenRecordset( _
        "select * from [" & strSourceTable & "] In '" & strSourceDatabase & "'")
    ok = (Err.Number = 0)
    Err.Clear
    Set rs = Nothing
    If ok Then
        'delete old table link, if already exists
        DoCmd.DeleteObject Objecttype:=acTable, ObjectName:=strLinkedTableName
        Application.RefreshDatabaseWindow
        Err.Clear
        On Error GoTo 0
        Set td = db.CreateTableDef(strLinkedTableName)
        With td
            .Connect = ";DATABASE=" & strSourceDatabase
            .SourceTableName = strSourceTable
        End With
        db.TableDefs.Append td
        Set td = Nothing
        db.TableDefs.Refresh
        Application.RefreshDatabaseWindow
    End If
End If
Set db = Nothing
CreateLinkedTable = ok
End Function

 

[sandanet]

If you step through the code, which line is getting highlighted when you debug?

there is no highlighted line at all , only that error msg says Microsoft access unable.. then continue open the other forms normally but no linked table

 

[sandanet]

the linking code you posted is supposed to create a linked table for all the tables in the BE, correct?

no only link with table2 as shown in this line of code

If Left(BackObj.name, 4) <> "MSys" And BackObj.name = "table2" Then

 

[sandanet]

you may try also this modified version of your CreateLinkedTable() function:
to use it:


Call CreateLinkedTable("Table2", "Table2", Application.CurrentProject.Path & "\idcard.mdb")

Public Function CreateLinkedTable(ByVal strLinkedTableName As String, _
ByVal strSourceTable As String, _
ByVal strSourceDatabase As String) As Boolean

Dim rs As DAO.Recordset, ok As Boolean
Dim db As DAO.Database
Dim td As DAO.TableDef
Set db = CurrentDb
'check if source database exists
ok = (Len(Dir$(strSourceDatabase)) > 0)
If ok Then
    'check if table exists
    On Error Resume Next
    Set rs = db.OpenRecordset( _
        "select * from [" & strSourceTable & "] In '" & strSourceDatabase & "'")
    ok = (Err.Number = 0)
    Err.Clear
    Set rs = Nothing
    If ok Then
        'delete old table link, if already exists
        DoCmd.DeleteObject Objecttype:=acTable, ObjectName:=strLinkedTableName
        Application.RefreshDatabaseWindow
        Err.Clear
        On Error GoTo 0
        Set td = db.CreateTableDef(strLinkedTableName)
        With td
            .Connect = ";DATABASE=" & strSourceDatabase
            .SourceTableName = strSourceTable
        End With
        db.TableDefs.Append td
        Set td = Nothing
        db.TableDefs.Refresh
        Application.RefreshDatabaseWindow
    End If
End If
Set db = Nothing
CreateLinkedTable = ok
End Function

Thank u so much ^_^ finally it works yaaaaa

 

[theDBguy]

Thank u so much ^_^ finally it works yaaaaa

Hi. Congratulations! Glad to hear you got it sorted out. Sorry I wasn't much help.

Good luck with your project.

 

[sandanet]

Hi. Congratulations! Glad to hear you got it sorted out. Sorry I wasn't much help.

Good luck with your project.

Thank u too, u also helped me a lot by tracking the problem ^_^