Link to home
Start Free TrialLog in
Avatar of will_scarlet7
will_scarlet7

asked on

Skipping deleting/importing a link when the table does not exist in BE

OK. I think it should be simple...
    In the code below I move through the recordset to get the table names to delete and to link. Works fine except that there are 2 versions of the program that generate the work reports sent in (old and new) the only difference in the data recieved is one added table.
    I realize that the proper solution is just to get the users to use the correct version of the program, but due to timing I need to be able to handle the reports if for example their computer crashed and they installed the wrong one for one month. Basicaly my end needs to be flexible.

So my question is how can I skip the parts of the "With" section for the missing table?

Sub CreateLinkedTableADO(dbBE As String)
   
    Dim cat As Object
    Dim rs As Object
    Dim tbl As Object
    Dim strSQL As String
 
    Set cn = CreateObject("Adodb.Connection")
    Set rs = CreateObject("Adodb.Recordset")
    Set cat = CreateObject("ADOX.Catalog")
    Set tbl = CreateObject("ADOX.Table")
 
    cat.ActiveConnection = CurrentProject.Connection
    strSQL = "SELECT * FROM stpLinkedTables WHERE stpLinkedTables.Category = 'ABM';"
    rs.Open strSQL, CurrentProject.Connection, 2, 1
    rs.MoveFirst
   
    While Not rs.EOF
        DoCmd.DeleteObject acTable, rs("LinkName")
        Set tbl = New ADOX.Table
        Set tbl.ParentCatalog = cat
        With tbl
            .Name = rs("LinkName")
            .Properties("Jet OLEDB:Create Link") = True
            .Properties("Jet OLEDB:Remote Table Name") = rs("TableName")
            .Properties("Jet OLEDB:Link Datasource") = dbBE
        End With
        cat.Tables.Append tbl
        rs.MoveNext
    Wend
   
    rs.Close
    Set rs = Nothing
    Set cat = Nothing
    Set tbl = Nothing

End Sub
Avatar of arcross
arcross
Flag of United Kingdom of Great Britain and Northern Ireland image

could you trap the error and resume?
Avatar of Jim Horn
Can you add the field stpLinkedTables.VersionNumber, put the version numbers where these tables were introduced, then in your rs.Open SQL add a WHERE clause to do the logic for the correct version?

Just a thought, as I don't have the VBA answer to your question.

Hope this helps.
-Jim
ASKER CERTIFIED SOLUTION
Avatar of shanesuebsahakarn
shanesuebsahakarn
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of will_scarlet7
will_scarlet7

ASKER

Shane, you are a genius! Works beautiful and no noticable speed difference.
Thank you & God bless!

Sam

PS. Thank you also Jim & arcross for your suggestions!