Adding a Record to a table through the VB code

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Neily - 31 May 2006 11:37 GMT

Hi,

The subject probably expalins what I'm trying to achieve and failing with,
but I'll elaborate a wee bit.

I have a subform on a main form. The subform diplays the contents of a
table. Upon opening the main form, I want the code to add records to the
table and thus be displayed on the subform.

The table (tblImportTextFiles) has 2 fields. ImportfileName (text field)
and Import? (Yes/No field)
When the main form is opened, I want to delete anything that is on the
table, then add records to the table based on filenames in a certain
directory.

I have managed the deleting of old data and can easily collect the names of
the files, it's the adding the records to the table that is causing me
problems.

Can anyone help me out.

Ta.

Neil

Reply to this Message

Klatuu - 31 May 2006 14:19 GMT

You can use the Dir funtion to get the list of files within the directory,
then add them to the table by enstantiating a recordset and looping through
the files returned by Dir and appending records to the table. The example
below includes deleting all the data in the table, then adding a record for
each file in the directory that has a .txt extension

CurrentDb.Execute("DELETE * FROM tblImportTextFiles;"), dbFailOnError

Set rst = CurrentDb.OpenRecordset("tblImportTextFiles")

strNextTable = Dir("C:\SomeDirectory\*.txt")

Do While Len(strNextTable) > 0
With rst
.AddNew
![ImportFileName] = strNextTable
![Import] = True
.Update
End With
strNextTable = Dir()
Loop

rst.close
Set rst = Nothing

> Hi,
>
[quoted text clipped - 20 lines]
>
> Neil

Reply to this Message