Please Help me -- How do i store Path name and File name

[Guest_imported]

How do i store the path name and file name into Access database using VBA.

For example c:\Resume\AAA.doc
c:\Resume\Vb\BBB.doc
c:\Resume\VB\Test\CCC.doc

Result will be stored into a table like this..

id Path_name File_name
-- --------- ----------
1) c:\Resume AAA.doc
2) c:\Resume\Vb BBB.doc
3) c:\Resume\VB\Test CCC.doc

Pleas help me out.Thanks

 

[Databaseguy]

Do you already have the full path & Name ? If so, the following routine will extract jsut the name and add it to the field called Namez

Private Sub NameFile()
Dim i As Integer
Dim dbsDocument As Database
Dim rstDocument As Recordset
Dim MyStr
Dim StrLen, Position, Remainder As Integer
Set dbsDocument = CurrentDb
Set rstDocument = dbsDocument.OpenRecordset("tblTablesIndex&quot
With rstDocument
.MoveFirst
Do While Not rstDocument.EOF
Position = 1000
MyStr = .Fields("Connection&quot.Value
Do While Position <> 0
StrLen = Len(MyStr)
Position = InStr(MyStr, &quot;\&quot
Remainder = StrLen - Position
MyStr = Mid(MyStr, Position + 1, Remainder)
Loop
.Edit
.Fields(&quot;Namez&quot = MyStr
.Update
.MoveNext
Loop
End With

You could modify it to put the first part (the Path) in another field.

 

[Guest_imported]

i don't have path name and file name. It has to search all the .doc file from c:\ drive and store the path name and file name into Access database

Thanks

 

[Databaseguy]

OK, then run this first. Change the search paamaters to *.doc or whatever your searching for. You need a table called tblDocument

Public Sub Search()
Dim i As Integer
Dim dbsDocument As Database
Dim rstDocument As Recordset
Dim DirList, RecordTemp
Dim x As Integer
Dim y As Double
Dim DateMod As Date
DirList = Array(&quot;C:\&quot'You can add network drives here too
Set dbs=DocumenCurrentDB()
Set rstDocument = dbsDocument.OpenRecordset(&quot;tblDocument&quot
For x = 0 To 1'Change this to agree with number of drives in DirList
On Error Resume Next
With Application.FileSearch
.NewSearch
.LookIn = DirList(x)
.SearchSubFolders = True
.FileName = &quot;*.mdb&quot; 'Change here
.FileType = msoFileTypeDatabases 'And here
If .Execute() > 0 Then
'MsgBox .FoundFiles.Count
For i = 1 To .FoundFiles.Count
RecordTemp = .FoundFiles(i)
'MsgBox .FoundFiles(i)
y = FileLen(RecordTemp)
DateMod = FileDateTime(RecordTemp)
With rstDocument
.AddNew
!File = RecordTemp
!fileSize = y
!LastMod = DateMod
.Update
End With
'MsgBox .FoundFiles(i)
Next i
Else
'MsgBox &quot;There were no files found.&quot;
End If
End With
Next x
Call NameFile
End Sub

 

[Guest_imported]

Thanks for helping me.

i have created one command button and copied your code in on click event. is it correct way?

i have created table called tbldocument and coloumn names are:
id Autonumber
Path_name Text
File_name Text

i have changed here aslo => .FileName = &quot;*.Doc&quot;

should i change here, if yes what should i change?
.FileType = msoFileTypeDatabases

whatelse i have to change? Please help me.
i am new to VBA

Thanks you so much . Appreciated