can't find table when running rept.

[Marc Miller]

Hi,

I am using MS Access 2000. I programmatically link to a variable SQL Server
table, depending on user input. I call the linked table 'VarTable' and have
queries and reports built off it.

The user hits a button, enters the actual table name and my code then links
to it naming it 'VarTable' and then runs a report.

The problem occurs on the code 'DoCmd.OpenReport stDocName, acPreview' in
the following
sub with the error 'can't find VarTable'. Code for sub is below.

If I hit debug and then continue, it runs the report.

This does not happen every time the sub is called.

Thanks for any help. This is a sticky one for an old FoxPro programmer.

Thanks,
Marc Miller


*********************************************************************

Private Sub cmdOverDue_Click()
'On Error GoTo Err_cmdFinals_Click

Dim rs As New ADODB.Recordset
Dim strRemoteTableName As String

Dim strMsg As String
Dim strInput As String


If cnAccess.State <> 1 Then
cnAccess.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=\\ctehq\sys\groups\tax_forms\TaxExReports.mdb"
cat.ActiveConnection = cnAccess
End If

strMsg = "Enter the table Name:"

strRemoteTableName = Trim(InputBox(Prompt:=strMsg, _
Title:="Mail Merge Table Name", XPos:=2000, YPos:=2000))


Dim i As Integer
For i = 0 To cat.Tables.Count - 1
If UCase(cat.Tables(i).Name) = "VARTABLE" Then
cat.Tables.Delete "Vartable"
Exit For
End If

Next i



tbl.Name = "VarTable"
Set tbl.ParentCatalog = cat

tbl.Properties("Jet OLEDB:Link Provider String") = "ODBC;Driver=SQL
Server;Server=CTENEWS-NT\ACCOUNTING;database=TaxExempt;uid=taxempt_ro;pwd=re
ady4word;"
tbl.Properties("Jet OLEDB:Remote Table Name") = strRemoteTableName
tbl.Properties("Jet OLEDB:Create Link") = True
tbl.Properties("Jet OLEDB:Cache Link Name/Password") = True



If UCase(Mid(strRemoteTableName, 5, 2)) = "ST" Or
UCase(Mid(strRemoteTableName, 5, 2)) = "FD" Then

cat.Tables.Append tbl

rs.CursorLocation = adUseClient
rs.Open "Select * from vartable", cnAccess, adOpenStatic, adLockReadOnly
Else

MsgBox ("You have entered an invalid table name. Please Retry.")
If rs.State = 1 Then
rs.Close
End If
Set rs = Nothing
Set cat = Nothing
Set cnAccess = Nothing
Exit Sub

End If

Dim icnt As Integer
Dim boolIsTable As Boolean
boolIsTable = False

For icnt = 0 To cat.Tables.Count - 1
If UCase(cat.Tables(icnt).Name) = "VARTABLE" Then
boolIsTable = True
Exit For
End If

Next icnt



If rs.State = 1 Then
rs.Close
End If
Set rs = Nothing
Set cat = Nothing
Set cnAccess = Nothing

'---------------run the report
If boolIsTable Then
Dim stDocName As String

If UCase(Mid(strRemoteTableName, 5, 2)) = "ST" Then

stDocName = "State_Overdue"

'**************ERROR OCCURS HERE********************************
DoCmd.OpenReport stDocName, acPreview
'*******************************************************************
ElseIf UCase(Mid(strRemoteTableName, 5, 2)) = "FD" Then
stDocName = "Federal Overdue Exemptions"
DoCmd.OpenReport stDocName, acPreview

End If
Else
MsgBox ("table not found")
End If


Exit_cmdFials_Click:
Exit Sub

Err_cmdFinals_Click:
MsgBox Err.Description
'Resume Exit_cmdFinals_Click
Exit Sub

End Sub