access tabel

[ankitpaudel]

I am using this code for import excel file into table
when i use this code its delete all table but i don't want to delete other
tables and when i import excel files message will display filed name not
found how i know which file name fields are wrong how to fix it. i know this red highlighted code delete table i just want to delete record from specification table only please help

Dim dbs As DAO.Database
Dim i As Integer


Set dbs = CurrentDb

For i = dbs.TableDefs.Count - 1 To 0 Step -1
If Left(dbs.TableDefs(i).Name, 4) <> "MSys" _
And dbs.TableDefs(i).Name <> "tblSpecial1" _
And dbs.TableDefs(i).Name <> "tblSpecial2" Then
dbs.TableDefs.Delete dbs.TableDefs(i).Name
End If
Next i

Set dbs = Nothing

Const strPath As String = "c:\temp\" 'Directory Path
Dim strFile As String 'Filename
Dim strFileList() As String 'File Array
Dim intFile As Integer 'File Number


strFile = Dir(strPath & "*.xls")

While strFile <> ""
'add files to the list
intFile = intFile + 1

ReDim Preserve strFileList(1 To intFile)

strFileList(intFile) = strFile
strFile = Dir()
Wend

If intFile = 0 Then
MsgBox "No files found"
Exit Sub
End If

For intFile = 1 To UBound(strFileList)
DoCmd.TransferSpreadsheet acImport, , "tblTest", strPath & strFileList(intFile), _
True, "F!A1:AB8000"
Next

MsgBox UBound(strFileList) & " Files were Imported"


Edited 29-Sep-10 by geekgirlau. Reason: insert vba tags

Thanks in advance
ankit

[geekgirlau]

If you don't want to delete the tables, you should remove this:


Set dbs = CurrentDb

For i = dbs.TableDefs.Count - 1 To 0 Step -1
If Left(dbs.TableDefs(i).Name, 4) <> "MSys" _
And dbs.TableDefs(i).Name <> "tblSpecial1" _
And dbs.TableDefs(i).Name <> "tblSpecial2" Then
dbs.TableDefs.Delete dbs.TableDefs(i).Name
End If
Next i

Set dbs = Nothing


and use something like this:


DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE * FROM Specification"
DoCmd.SetWarnings True

(you will need to put the proper name of the table in here).

Without seeing the structure of your database I don't know why you are receiving the field not found error, but I would suggest that you try manually importing your data files into tblTest so that you can see where the problem is occuring.

Please use the VBA tags when posting code (the little "vba" button).

[Imdabaum]

Or just change that loop to delete the records for the table that don't meet your specifications.

Dim dbs As DAO.Database
Dim i As Integer

Set dbs = CurrentDb

For i = dbs.TableDefs.Count - 1 To 0 Step -1
If Left(dbs.TableDefs(i).Name, 4) <> "MSys" _
And dbs.TableDefs(i).Name <> "tblSpecial1" _
And dbs.TableDefs(i).Name <> "tblSpecial2" Then
DoCmd.RunSQL "DELETE * FROM " & dbs.TableDefs(i).Name
End If
Next i

Set dbs = Nothing
'...continued code...
'.....


Edited 29-Sep-10 by geekgirlau. Reason: insert vba tags

Thanks in advance
ankit[/quote]

[hansup]

You can access each member of the TableDefs collection with For Each ... Next. It's faster, and you don't need a counter variable.

Dim dbs As DAO.Database
Dim tdf As DAO.TableDef

Set dbs = CurrentDb
For Each tdf In dbs.TableDefs
If Not (tdf.Name Like "MSys*" Or _
tdf.Name = "tblSpecial1" Or _
tdf.Name = "tblSpecial2") Then
'Debug.Print tdf.Name
dbs.Execute "DELETE FROM " & tdf.Name & ";", dbFailOnError
End If
Next tdf
Set tdf = Nothing
Set dbs = Nothing

[Imdabaum]

Sorry.. I got lazy. That would be the prefered method. I just ran with his existing code.