trouble with tabledefs in 2007

[supmktg]

I just moved an application from A2000 to A2007 and I am having trouble with the DAO.tabledefs collection. Code that works fine in A2000 is failing in A2007.

I am using transferspreadsheet to create a new table from an xl file. Before I do, I check if the table already exists (using the same tableexists code that I have been using successfully for years) and delete it first with this code:

If TableExists(strTbl) Then
CurrentDb.TableDefs.Delete strTbl
End If

which is giving me an error item is not in the collection. When I check, the table name does exist.

If I comment out the above code transferspreadsheet creates a 2nd table with the same name & "1".

I am also counting the fields in this new table after it is imported using:

CurrentDB.TableDefs(strTbl).Fields.Count

This also gives me the same not in collection error even though the table does exist. Both of these work just fine in A2000. Why are they causing errors in A2007?

Thanks,
Sup

 

[bob fitz]

What is the value of strTbl when the code fails?

 

[gemma-the-husky]

simply this. error handling

onerror resume next
CurrentDb.TableDefs.Delete strTbl

the thing is, with this sort of thing - if the table exists, you can delete it - if it doesn't exist, you can just ignore the error. hence the onerror statement.

often however, you cannot just ignore an error, and you need to test what the error was before you continue. but in this case you are ok.

having re-read this, this is not the error, is it? i agree with bob - you need ot examine what is oging on and test the value of strtbl. maybe it is being declared in two places, and you are not checking the "right" one

 

[supmktg]

Confusing?

The value of strTbl is being extrapolated from the path to the xl file using the following code:

strTbl = TmpTblName(Me.txtFileLocation)  


Function TmpTblName(strPath As String) As String
    Dim strFileFullName As String, strTblName As String
    
        strFileFullName = Left(Right(strPath, Len(strPath) - InStrRev(strPath, "\")), (Len(Right(strPath, Len(strPath) - InStrRev(strPath, "\")))))
        strTblName = Left(strFileFullName, (Len(strFileFullName) - 4)) '(Len(strExt) + 1)))
        TmpTblName = strTblName & "_Del"

End Function

The value of strTbl is "TmpTblProc_del" (including the quotes) as indicated when hovering over strTbl on the code break. The table "TmpTblProc_del" (strTbl) is being created by a transfer spreadsheet as follows:

strFilePath = Me.txtFileLocation

'if it is an xls file
            DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, strTbl, strFilePath, True, strSheetName & "!"

'if it is an xlsx file
            DoCmd.TransferSpreadsheet acImport, 10, strTbl, strFilePath, True, strSheetName & "!" 'spreadsheet type xlsx = 10, "!" so sheet name is recognized

which successfully creates the new table.

The value of strTbl is never altered from its initial value as above.

Confusing is that I don't get the error in A2000, only A2007. More confusing is that if I replace strTbl Tbl with "TmpTblProc_del", the error disappears.

Until I figure out the problem, I will use the static name "TmpTblProc_del" instead of strTbl.

Thanks,
Sup

 

[gemma-the-husky]

is the second block of code the whole of your code.

there is nothing in there to test whether iti s an xls or xlsx file. maybe that is part of the problem.

 

[supmktg]

Sorry, I knew I should have separated the 2 lines of code.

Dim strFilePath as String
Dim strSheetName as String
Dim strTbl as String
Dim strFileType as String


'complete path and file name
strFilePath = Me.txtFileLocation

strSheetName = Me.txtSheetName

'temporary new import table name based on xl file name 
strTbl = TmpTblName(Me.txtFileLocation)  

strFileType = Right(Me.txtFileLocation, Len(Me.txtFileLocation) - (InStr(Me.txtFileLocation, ".")))

Select Case strFileType

	Case "xls"

            DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, strTbl, strFilePath, True, strSheetName & "!"

            
        Case "xlsx"

            DoCmd.TransferSpreadsheet acImport, 10, strTbl, strFilePath, True, strSheetName & "!" 'spreadsheet type xlsx = 10, "!" so sheet name is recognized

End select

This code works fine, and the table is imported properly.

The issue is that the newly (or previously) imported table is not being recognized in the tabledefs if I use the TmpTblName function to set the value of strTbl. If I manually set the value of strTbl I have no problem.

Also, the issue is only occurring in A2007. Setting the value of strTbl using the TmpTblName function works fine in A2000.

I thought there might be something specific to A2007 that was causing my problem. For now, my workaround will be to manually set a generic name for this import.

I appreciate your help,
Sup

 

[gemma-the-husky]

it's hard to iunderstand exactly what is happening. i would set a breakpoint, and step through the code, so you can see exactly what is happening

 

[spikepl]

The value of strTbl is "TmpTblProc_del" (including the quotes)

Where did it get the quotes from?

I think you are getting lost in strings. "MyTable" <>""MyTable"" and that is the root of your problem. You see a "MyTable" but are trying to delete a ""MyTable""