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.Connec tion")
Set rs = CreateObject("Adodb.Record set")
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
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.Connec
Set rs = CreateObject("Adodb.Record
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,
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
could you trap the error and resume?
Can you add the field stpLinkedTables.VersionNum ber, 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
Just a thought, as I don't have the VBA answer to your question.
Hope this helps.
-Jim
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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!
Thank you & God bless!
Sam
PS. Thank you also Jim & arcross for your suggestions!